oracle数据库性能健康检查脚本

1、*os 层检查#检查 oracle 进程 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;# *查看数据文件信息# 检查表空间数据文件信息coltablespace_namefora30selecttablespace_name,sum(bytes)/1024/1024fromdba_temp_filesgroupbytablespace_name;# 检查表空间SELECTTABLESPACE_NA

3、ME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENTFROMDBA_TABLESPACES;# 检查数据文件状态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/# 查询临时 segment 使用情况COLusernameFORMATa10;COLsegtypeFORMATa10;SELECTusername,segtype,extentsE

5、xtentsAllocated,blocksBlocksAllocatedFROMv$tempseg_usage;# 查看数据文件信息，若文件较多可以根据需要字段进行排序输出 top10SELECTfs.phyrdsReads,fs.phywrtsWrites,fs.avgiotimAverageI/OTime,DatafileFROMv$datafiledf,v$filestatfsWHEREdf.file#=fs.file#;# 查看所有数据文件 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;# 查看所有数据文件 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# 查看热点数据文件（从单块读取时间判断）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 写入完成时间若1 表示写入过慢)selecttotal_waits,time_waited,average_wait,tim

10、e_waited/total_waitsasavgfromv$system_eventwhereevent=logfileparallelwrite;# 检查与 redo 相关性能指标selectname,valuefromv$sysstatwherenamelike%redo%;# 查询 redoblocksizeselectmax(lebsz)fromx$kccle;# 查看 redoallocationlatchcolnamefora30selectname,gets,misses,misses/getsfromv$latch_childrenwherename=redoallocati

11、on;查看 redo# 查看与 redo 相关等待事件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 次数selectto_number(value,

12、99999999999)fromv$sysstatwherename=usercommits;# 查看系统运行时间select(sysdate-startup_time)*24*60*60assecondsfromv$instance# 计算出每秒用户提交次数selectuser_commit 次数/系统运行时间 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（数据字典缓冲区）命中率# 当执行一个 dml 或 ddl 都会造成对数据字典的递归修改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(多池技术)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;# 每个子 sharedpool 由单独的 sharedpoollatch 保护查看他们的命中率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 大小情况下，响应时间，S 单位SELECTSharedPoolcomponent,shared_pool_size_for_estimateestd_sp_size,estd_lc_time_saved_factorparse_time_factor

22、,CASEWHENcurrent_parse_time_elapsed_s+adjustment_s0（出现过 ora-04031）且LAST_FAILURE_SIZE （最后请求内存大小）_SHARED_POOL_RESERVED_MIN_ALLOC表示sharedreservedpool缺少连续内存，可以力口大 SHARED_POOL_RESERVED_SIZE,减少_SHARED_POOL_RESERVED_MIN_ALLOC少放对象，并相对力口大 shared_pool_size# 要是反过来 REQUEST_FAILURES0（出现过 ora-04031）且 LAST_FAILURE_SIZE（最后请求内存大小）_SHARED_POOL_RESERVED_MIN_ALLOC,表示在 sharedpool 中缺少连续内存，可以力口减少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些对象，减少 sharedpool 压力，适当加大shared_pool_size,SHARED_POOL_RESERVED_SIZE# 从