oracle數(shù)據(jù)庫(kù)性能健康檢查腳本

1、*os 層檢查#檢查 oracle 進(jìn)程 ps-ef|grepora|wc-lcrsstat-t-vtopglancecrsctlcheckcluster(cssd)vmstat36006#database 概況信息檢查#檢查 database 基本信息 select*fromv$version;selectname,open_mode,log_modefromv$database;selectinstance_number,instance_name,statusfromgv$instance;showparametercpu_cshowparameterblock_sizeselectgr

2、oup#,thread#,members,bytes/1024/1024fromgv$log;showsgaselectcount(*)fromv$controlfileselectcount(*)fromv$tempfile;selectcount(*)fromv$datafile;# *查看數(shù)據(jù)文件信息# 檢查表空間數(shù)據(jù)文件信息coltablespace_namefora30selecttablespace_name,sum(bytes)/1024/1024fromdba_temp_filesgroupbytablespace_name;# 檢查表空間SELECTTABLESPACE_NA

3、ME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENTFROMDBA_TABLESPACES;# 檢查數(shù)據(jù)文件狀態(tài)selectcount(*),statusfromv$datafilegroupbystatus;*查看基本信息# 檢查表空間使用情況selectf.tablespace_name,a.total,f.free,(a.total-f.free)/1024usedSIZE(G),round(f.free/a.total)*100)%Freefrom(selecttablespace_name,sum(bytes/(1

4、024*1024)totalfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,round(sum(bytes/(1024*1024)freefromdba_free_spacegroupbytablespace_name)fWHEREa.tablespace_name=f.tablespace_name(+)orderby%Free/# 查詢臨時(shí) segment 使用情況COLusernameFORMATa10;COLsegtypeFORMATa10;SELECTusername,segtype,extentsE

5、xtentsAllocated,blocksBlocksAllocatedFROMv$tempseg_usage;# 查看數(shù)據(jù)文件信息，若文件較多可以根據(jù)需要字段進(jìn)行排序輸出 top10SELECTfs.phyrdsReads,fs.phywrtsWrites,fs.avgiotimAverageI/OTime,DatafileFROMv$datafiledf,v$filestatfsWHEREdf.file#=fs.file#;# 查看所有數(shù)據(jù)文件 i/o 情況若文件太多，可以改寫為 top10select*(orderbyxxdesc)whererownum=10COLtsF

6、ORMATa10HEADINGTablespace;COLreadsFORMAT999990;COLwritesFORMAT999990;COLbrFORMAT999990HEADINGBlksRead;COLbwFORMAT999990HEADINGBlksWrite;COLrtimeFORMAT999990;COLwtimeFORMAT999990;SELECTASts,fs.phyrdsReads,fs.phywrtsWrites,fs.phyblkrdASbr,fs.phyblkwrtASbw,fs.readtimRTime,fs.writetimWTimeFROMv$t

7、ablespacets,v$datafiledf,v$filestatfsWHEREts.ts#=df.ts#ANDdf.file#=fs.file#UNIONSELECTASts,ts.phyrdsReads,ts.phywrtsWrites,ts.phyblkrdASbr,ts.phyblkwrtASbw,ts.readtimRTime,ts.writetimWTimeFROMv$tablespacets,v$tempfiletf,v$tempstattsWHEREts.ts#=tf.ts#ANDtf.file#=ts.file#ORDERBY1;# 查看所有數(shù)據(jù)文件 i/o

8、 情況(比率)，若文件太多，可以改寫為 top10select*(orderbyxxdesc)whererownum0groupbyob.owner,ob.object_nameorderbysum(tch)desc)whererownum=10# 判斷物理讀最多的 objectselect*from(selectowner,object_name,valuefromv$segment_statisticswherestatistic_name=physicalreadsorderbyvaluedesc)whererownum=10# 查看熱點(diǎn)數(shù)據(jù)文件（從單塊讀取時(shí)間判斷）SELECTt.fi

9、le_name,t.tablespace_name,round(s.singleblkrdtim/s.singleblkrds,2)ASCS,s.READTIM,s.WRITETIMFROMv$filestats,dba_data_filestWHEREs.file#=t.file_idandrownumsysdate-1orderbyfirst_time,minutes;# 檢查 lgwri/o 性能(time_waited/total_waits:表示平均 lgwr 寫入完成時(shí)間若1 表示寫入過(guò)慢)selecttotal_waits,time_waited,average_wait,tim

10、e_waited/total_waitsasavgfromv$system_eventwhereevent=logfileparallelwrite;# 檢查與 redo 相關(guān)性能指標(biāo)selectname,valuefromv$sysstatwherenamelike%redo%;# 查詢 redoblocksizeselectmax(lebsz)fromx$kccle;# 查看 redoallocationlatchcolnamefora30selectname,gets,misses,misses/getsfromv$latch_childrenwherename=redoallocati

11、on;查看 redo# 查看與 redo 相關(guān)等待事件coleventformata40selectevent,total_waits,total_timeouts,average_waitfromv$system_eventwhereupper(event)like%REDO%;# 查看 sessionredoeventselectevent,total_waits,total_timeouts,average_waitfromv$session_eventwhereupper(event)like%REDO%;# 查看 usercommit 次數(shù)selectto_number(value,

12、99999999999)fromv$sysstatwherename=usercommits;# 查看系統(tǒng)運(yùn)行時(shí)間select(sysdate-startup_time)*24*60*60assecondsfromv$instance# 計(jì)算出每秒用戶提交次數(shù)selectuser_commit 次數(shù)/系統(tǒng)運(yùn)行時(shí)間 fromdual;selectvaluefromv$sysstatwherename=redoblockswritten;selecta.redoblocks/b.trancountfrom(selectvalueredoblocksfromv$sysstatwherename=re

13、doblockswritten)a,(selectvaluetrancountfromv$sysstatwherename=usercommits)b#sga,pga,命中率# 檢查 sgashowsgaselect*fromv$sga;# 查看 buffercache 命中率select1-(sum(decode(name,physicalreads,value,0)/(sum(decode(name,dbblockgets,value,0)+(sum(decode(name,consistentgets,value,0)BufferHitRatiofromv$sysstat;# 查看 bu

14、ffercache 建議selectsize_for_estimate,estd_physical_read_factor,to_char(estd_physical_reads,99999999999999999999999)asestd_physical_readsfromv$db_cache_advicewherename=DEFAULT;COLpoolFORMATa10;SELECT(SELECTROUND(value/1024/1024,0)FROMv$parameterWHEREname=db_cache_size)CurrentCache(Mb),namePool,size_fo

15、r_estimateProjectedCache(Mb),ROUND(100-estd_physical_read_factor,0)CacheHitRatio%FROMv$db_cache_adviceWHEREblock_size=(SELECTvalueFROMv$parameterWHEREname=db_block_size)ORDERBY3;*sga,pga,命中率# 查看 pgashowparameterpga# 查看 cache 池showparametercache# 查看 buffercache 中 defalutpool 命中率selectname,1-(physical

16、_reads)/(consistent_gets+db_block_gets)fromv$buffer_pool_statistics;# 檢查 sharedpoolshowparametershared# 檢查 sharedpool 中 librarycacheselectnamespace,pinhitratiofromv$librarycache;# 檢查整體命中率(librarycache)selectsum(pinhits)/sum(pins)fromv$librarycache;selectsum(pins)hits,sum(reloads)misses,sum(pins)/(su

17、m(pins)+sum(reloads)HitsRatiofromv$librarycache;# 檢查 sheredpoolfreespaceSELECT*FROMV$SGASTATWHERENAME=freememoryANDPOOL=sharedpool;# 檢查 rowcache（數(shù)據(jù)字典緩沖區(qū)）命中率# 當(dāng)執(zhí)行一個(gè) dml 或 ddl 都會(huì)造成對(duì)數(shù)據(jù)字典的遞歸修改columnupdatesformat999,999,999SELECTparameter,sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets)pct_succ

18、_gets,sum(modifications)updatesFROMV$ROWCACHEWHEREgets0GROUPBYparameter;SELECT(SUM(GETS-GETMISSES-FIXED)/SUM(GETS)ROWCACHEFROMV$ROWCACHE;# 查看 Sharedpoollatch(多池技術(shù))colparameterfora20colsessionfora20selecta.ksppinmParameter,b.ksppstvlSessionValue,c.ksppstvlInstanceValuefromsys.x$ksppia,sys.x$ksppcvb,s

19、ys.x$ksppsvcwherea.indx=b.indxanda.indx=c.indxanda.ksppinm=_kghdsidx_count;# 每個(gè)子 sharedpool 由單獨(dú)的 sharedpoollatch 保護(hù)查看他們的命中率sharedpoollatch,用于 sharedpool 空間回收分配使用的 latchcolnameformata15selectaddr,name,gets,misses,1-misses/getsfromv$latch_childrenwherename=sharedpool;# 查看 sharedpool 建議columnclheadingP

20、ool|Size(M)columnc2headingSize|Factorcolumnc3headingEst|LC(M)columnc4headingEstLC|Mem.Obj.columnc5headingEst|Time|Saved|(sec)columnc6headingEst|Parse|Saved|Factorcolumnc7headingEst|ObjectHitsformat999,999,999SELECTshared_pool_size_for_estimatec1,shared_pool_size_factorc2,estd_lc_sizec3,estd_lc_memor

21、y_objectsc4,estd_lc_time_savedc5,estd_lc_time_saved_factorc6,to_char(estd_lc_memory_object_hits,99999999999)c7FROMV$SHARED_POOL_ADVICE;#使用 v$shared_pool_advice 算不同 sharedpool 大小情況下，響應(yīng)時(shí)間，S 單位SELECTSharedPoolcomponent,shared_pool_size_for_estimateestd_sp_size,estd_lc_time_saved_factorparse_time_factor

22、,CASEWHENcurrent_parse_time_elapsed_s+adjustment_s0（出現(xiàn)過(guò) ora-04031）且LAST_FAILURE_SIZE （最后請(qǐng)求內(nèi)存大?。SHARED_POOL_RESERVED_MIN_ALLOC表示sharedreservedpool缺少連續(xù)內(nèi)存，可以力口大 SHARED_POOL_RESERVED_SIZE,減少_SHARED_POOL_RESERVED_MIN_ALLOC少放對(duì)象，并相對(duì)力口大 shared_pool_size# 要是反過(guò)來(lái) REQUEST_FAILURES0（出現(xiàn)過(guò) ora-04031）且 LAST_FAILURE_SIZE（最后請(qǐng)求內(nèi)存大小）_SHARED_POOL_RESERVED_MIN_ALLOC,表示在 sharedpool 中缺少連續(xù)內(nèi)存，可以力口減少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些對(duì)象，減少 sharedpool 壓力，適當(dāng)加大shared_pool_size,SHARED_POOL_RESERVED_SIZE# 從