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

1、*os层检查#检查 oracle 进程ps -ef|grepora|wc-lcrs_stat-t -vtopglancecrsctl check cluster (cssdvmstat 36006*查看基本信息#database概况信息检查#检查 database 基本信息select *from v$version;select name ,open_mode,log_modefrom v$database;select instance_number,instance_name,status from gv$instance;show parameter cpu_cshow paramet

2、er block_sizeselect group#,thread#,members,bytes/1024/1024from gv$log;show sgaselect count(*from v$controlfileselect count(*from v$tempfile;select count(*from v$datafile;*查看数据文件信息#检查表空间数据文件信息col tablespace_namefor a30select tablespace_name, sum(bytes/1024/1024from dba_temp_filesgroup by tablespace_n

3、ame; #检查表空间SELECTTABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE _MANAGEMENTFROM DBA_TABLESPACES;#检查数据文件状态select count(*,statusfrom v$datafilegroup by status;#检查表空间使用情况selectf.tablespace_name,a.total,f.free,(a.total-f.free/1024"used SIZE(G",round(f.free/a.total*100"%Fr

4、ee"from(selecttablespace_name,sum(bytes/(1024*1024total from dba_data_filesgroup by tablespace_namea,(selecttablespace_name,round(sum(bytes/(1024*1024free from dba_free_space group by tablespace_namefWHERE a.tablespace_name=f.tablespace_name(+order by "%Free"/#查询临时 segment 使用情况COL use

5、rname FORMAT a10;COL segtype FORMAT a10;SELECT username, segtype, extents "Extents Allocated",blocks "Blocks Allocated"FROM v$tempseg_usage;#查看数据文件信息 , 若文件较多可以根据需要字段进行排序 输出 top 10SELECT fs.phyrds "Reads", fs.phywrts "Writes",fs.avgiotim "Average I/OTime&q

6、uot;, "Datafile"FROM v$datafiledf, v$filestatfs WHERE df.file#=fs.file#;#查看所有数据文件 i/o情况 , 若文件太多 , 可以改写为 top 10select *(order by xx desc where rownum<=10COL ts FORMAT a10HEADING "Tablespace"COL reads FORMAT 999990;COL writes FORMAT 999990;COL br FORMAT 999990HEADING &qu

7、ot;BlksRead"COL bw FORMAT 999990HEADING "BlksWrite"COL rtime FORMAT 999990;COL wtime FORMAT 999990;SELECT AS ts, fs.phyrds "Reads", fs.phywrts "Writes",fs.phyblkrd AS br, fs.phyblkwrt AS bw,fs.readtim "RTime", fs.writetim "WTime"FROM v$t

8、ablespacets, v$datafiledf, v$filestatfsWHERE ts.ts#=df.ts#AND df.file#=fs.file#UNIONSELECT AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw,ts.readtim "RTime", ts.writetim "WTime"FROM v$tablespacets, v$tempfiletf, v$t

9、empstattsWHERE ts.ts#=tf.ts#AND tf.file#=ts.file#ORDER BY 1;#查看所有数据文件 i/o情况 (比率 , 若文件太多 , 可以改写为 top 10select *(order by xx desc where rownum<=10COL ts FORMAT a10HEADING "Tablespace"COL reads FORMAT 999990;COL writes FORMAT 999990;COL br FORMAT 999990HEADING "BlksRead"COL bw FO

10、RMAT 999990HEADING "BlksWrite"COL rtime FORMAT 999990;COL wtime FORMAT 999990;SELECT AS ts, fs.phyrds "Reads", fs.phywrts "Writes",fs.phyblkrd AS br, fs.phyblkwrt AS bw,fs.readtim "RTime", fs.writetim "WTime"FROM v$tablespacets, v$datafiledf,

11、 v$filestatfsWHERE ts.ts#=df.ts#AND df.file#=fs.file#UNIONSELECT AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw,ts.readtim "RTime", ts.writetim "WTime"FROM v$tablespacets, v$tempfiletf, v$tempstattsWHERE ts.ts#=tf.t

12、s#AND tf.file#=ts.file#ORDER BY 1;#获取 top 10热 segmentcol objct_namefor a30select *from(selectob.owner, ob.object_name,sum(b.tchTouchsfrom x$bhb , dba_objectsobwhere b.obj =ob.data_object_idand b.ts#>0group by ob.owner, ob.object_nameorder by sum(tchdescwhere rownum <=10#判断物理读最多的 objectselect *

13、from (selectowner,object_name,valuefrom v$segment_statisticswhere statistic_name='physicalreads' order by value desc where rownum<=10#查看热点数据文件 (从单块读取时间判断 SELECT t.file_name,t.tablespace_name,round(s.singleblkrdtim/s.singleblkrds, 2 AS CS,s.READTIM,s.WRITETIMFROM v$filestats, dba_data_file

14、stWHERE s.file#=t.file_idand rownum<=10order by cs desc*查看 redo#检查日志切换频率select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss' firsttime,round(first_time-lag(first_timeover(orderby first_time*24*60,2minutes from v$log_historywhere first_time>sysdate -1order by first_time,minutes;#检查

15、 lgwr i/o性能 (time_waited/total_waits:表示平均 lgwr 写入完成时间 若 >1表示写入过 慢 select total_waits,time_waited,average_wait,time_waited/total_waitsas avg from v$system_eventwhere event ='log file parallel write'#检查与 redo 相关性能指标select name,value from v$sysstatwhere name like '%redo%'#查询 redo blo

16、ck sizeselect max(lebszfrom x$kccle;#查看 redo allocation latchcol name for a30select name,gets,misses,misses/getsfrom v$latch_childrenwhere name='redoallocation'#查看与 redo 相关等待事件col event format a40select event,total_waits,total_timeouts,average_waitfrom v$system_eventwhere upper(event like

17、9;%REDO%'#查看 session redo eventselect event,total_waits,total_timeouts,average_waitfrom v$session_eventwhere upper(eventlike'%REDO%'#查看 user commit 次数select to_number(value,99999999999from v$sysstatwhere name='usercommits'#查看系统运行时间select (sysdate-startup_time*24*60*60as seconds f

18、rom v$instance#计算出每秒用户提交次数select user_commit次数 /系统运行时间 from dual;#计算出每个事务平均处理多少个 redo blockselect value from v$sysstatwhere name ='redo blocks written'select a.redoblocks/b.trancountfrom (selectvalue redoblocks from v$sysstatwhere name='redo blocks written' a ,(selectvalue trancount

19、from v$sysstatwhere name='usercommits' b'*sga,pga,命中率#sga,pga, 命中率#检查 sgashow sgaselect *from v$sga;#查看 buffer cache 命中率select 1-(sum(decode(name,'physical reads', value, 0/(sum(decode(name,'db block gets', value, 0+(sum(decode(name,'consistent gets', value, 0 &qu

20、ot;Buffer Hit Ratio"from v$sysstat;#查看 buffer cache 建议select size_for_estimate,estd_physical_read_factor, to_char(estd_physical_reads,99999999999999999999999as"estd_physical_reads"from v$db_cache_advicewhere name ='DEFAULT'COL pool FORMAT a10;SELECT (SELECTROUND(value/1024/102

21、4,0FROM v$parameterWHERE name ='db_cache_size'"Current Cache(Mb",name "Pool", size_for_estimate"Projected Cache(Mb",ROUND(100-estd_physical_read_factor,0"Cache Hit Ratio%"FROM v$db_cache_adviceWHERE block_size=(SELECTvalue FROM v$parameterWHERE name =&

22、#39;db_block_size'ORDER BY 3;#查看 pgashow parameter pga#查看 cache 池show parameter cache#查看 buffer cache 中 defalut pool 命中率select name,1-(physical_reads/(consistent_gets+db_block_getsfrom v$buffer_pool_statistics;#检查 shared poolshow parameter shared#检查 shared pool 中 library cacheselect namespace,pi

23、nhitratio from v$librarycache;#检查整体命中率 (librarycacheselect sum(pinhits/sum(pinsfrom v$librarycache;select sum(pins"hits",sum(reloads"misses",sum(pins/(sum(pins+sum(reloads"Hits Ratio"from v$librarycache;#检查 shered pool free spaceSELECT *FROM V$SGASTATWHERE NAME ='fr

24、ee memory'AND POOL ='shared pool'#检查 row cache(数据字典缓冲区 命中率#当执行一个 dml 或 ddl 都会造成对数据字典的递归修改column updates format 999,999,999SELECT parameter, sum(gets, sum(getmisses, 100*sum(gets-getmisses /sum(getspct_succ_gets, sum(modificationsupdatesFROM V$ROWCACHEWHERE gets >0GROUP BY parameter;SE

25、LECT (SUM(GETS-GETMISSES -FIXED /SUM(GETS"ROW CACHE" FROM V$ROWCACHE;#查看 Shared pool latch(多池技术 col parameter for a20col session for a20select a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"from sys.x$ksppia, sys.x$ksppcvb, sys.x$k

26、sppsvcwhere a.indx =b.indx and a.indx =c.indxand a.ksppinm ='_kghdsidx_count'#每个子 shared pool 由 单独的 shared pool latch 保护 查看 他们的命中率shared pool latch ,用于 shared pool 空间 回收分配使用的 latchcol name format a15select addr,name,gets,misses,1-misses/getsfrom v$latch_childrenwhere name='sharedpool'

27、; #查看 shared pool 建议column c1heading 'Pool |Size(M'column c2heading 'Size|Factor'column c3heading 'Est|LC(M'column c4heading 'Est LC|Mem.Obj.'column c5heading 'Est|Time|Saved|(sec'column c6heading 'Est|Parse|Saved|Factor'column c7heading 'Est|Objec

28、tHits' format 999,999,999SELECT shared_pool_size_for_estimatec1,shared_pool_size_factorc2,estd_lc_sizec3,estd_lc_memory_objectsc4,estd_lc_time_savedc5,estd_lc_time_saved_factorc6,to_char(estd_lc_memory_object_hits,99999999999 c7FROM V$SHARED_POOL_ADVICE;#使用 v$shared_pool_advice算不同 shared pool 大小

29、情况下,响应时间, S 单位SELECT 'Shared Pool' component,shared_pool_size_for_estimateestd_sp_size,estd_lc_time_saved_factorparse_time_factor,CASEWHEN current_parse_time_elapsed_s+adjustment_s<0THENELSEcurrent_parse_time_elapsed_s+adjustment_sEND response_timeFROM (SELECTshared_pool_size_for_estimate

30、,shared_pool_size_factor,estd_lc_time_saved_factor,a.estd_lc_time_saved,e.VALUE /100current_parse_time_elapsed_s,c.estd_lc_time_saved-a.estd_lc_time_savedadjustment_sFROM v$shared_pool_advicea,(SELECT*FROM v$sysstatWHERE NAME ='parse time elapsed' e, (SELECTestd_lc_time_savedFROM v$shared_po

31、ol_adviceWHERE shared_pool_size_factor=1 c/#查看 shared pool 中 各种类型的 chunk 的大小数量SELECT KSMCHCLS CLASS, COUNT(KSMCHCLSNUM, SUM(KSMCHSIZSIZ, To_char(SUM(KSMCHSIZ/COUNT(KSMCHCLS/1024,'999,999.00'|'k'"AVG SIzE"FROM X$KSMSPGROUP BY KSMCHCLS;#查看是否有库缓冲有关的等待事件select sid,seq#,event,p1

32、,p1raw,p2,p2raw,p3,p3raw,statefrom v$session_waitwhere event like 'library%'#rowcache 命中率SELECT 'Dictionary Cache Hit Ratio ' "Ratio",ROUND(1-(SUM(GETMISSES/SUM(GETS*100,2|'%'"Percentage"FROM V$ROWCACHE;#librarycache 中详细比率信息SELECT 'Library Lock Request

33、s' "Ratio", ROUND(AVG(gethitratio*100, 2|'%'"Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library Pin Requests' "Ratio", ROUND(AVG(pinhitratio*100, 2|'%'"Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library I/OReloads' "R

34、atio", ROUND(SUM(reloads/SUM(pins*100, 2|'%'"Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library Reparses' "Ratio", ROUND(SUM(reloads/SUM(pins*100, 2|'%'"Percentage" FROM V$LIBRARYCACHE;#查询 sga 中各个 pool 情况COL name FORMAT a32;SELECT pool, nam

35、e, bytes FROM v$sgastatWHERE pool IS NULLOR pool !='shared pool' OR (pool='shared pool'AND (nameIN('dictionarycache','enqueue','librarycache','parameters','processes','sessions','free memory'ORDER BY pool DESC NULLS FIRST, name;

36、SELECT *FROM V$SGAINFO;#查看使用 shard_pool保留池情况SELECT request_misses,request_failures,free_spaceFROM v$shared_pool_reserved;#Oracle专门从共享池内置出一块区域来来分配内存保持这些大块。这个保留共享池的默认 大小是共享池的 5%(_shared_pool_reserved_pct5控制 oracle 建设置为 10%。大小通 过参数 SHARED_POOL_RESERVED_SIZE改。它是从共享池中分配,不是直接从 SGA 中 分配的,它是共享池的保留部分,专门用于存储大

37、块段#sharedpool 中内存大于 _SHARED_POOL_RESERVED_MIN_ALLOC将放入 shared pool 保 留池 , 保留池维护一个单独的 freelist,lru ,并且不会在 lru 列表存 recreatable 类型 chunks ,普 通 shared pool 的释放与 shared pool 保留池无关。#关于设置 SHARED_POOL_RESERVED_SIZE#1.如 果 系 统 出 现 ora-04031, 发 现 请 求 内 存 都 是 大 于 _SHARED_POOL_RESERVED_MIN_ALLOC(default10GR24400

38、 , 且 v$shared_pool_reserved中有大量 REQUEST_MISSES(并且可以 看下 LAST_MISS_SIZE 表示 SHARED_POOL_RESERVED_SIZE太小了 需要大的内存的请求失败 , 那么需要加大 SHARED_POOL_RESERVED_SIZE#2.如果 ora-04031请求内存出现在 4100-4400并造成 shared pool lru 合并 , 老化换出内存 , 可 以 调 小 _SHARED_POOL_RESERVED_MIN_ALLOC让 此 部 分 内 存 进 入 shared reserved pool, 相应的加大 SHA

39、RED_POOL_RESERVED_SIZE#3.从 v$shared_pool_reserved来判断 , 如果 REQUEST_FAILURES>0(出现过 ora-04031 且 LAST_FAILURE_SIZE(最 后 请求 内 存 大 小 >_SHARED_POOL_RESERVED_MIN_ALLOC表示 shared reserved pool 缺少连续内存 , 可以加大 SHARED_POOL_RESERVED_SIZE,减少 _SHARED_POOL_RESERVED_MIN_ALLOC少放对象 , 并相对加大 shared_pool_size#要是反过来 RE

40、QUEST_FAILURES>0(出现过 ora-04031 且 LAST_FAILURE_SIZE(最后请 求内存大小 <_SHARED_POOL_RESERVED_MIN_ALLOC,表示 在 shared pool 中缺少连续 内存 , 可以加减少 _SHARED_POOL_RESERVED_MIN_ALLOC多放入一些对象 , 减少 sharedpool 压力 , 适当加大 shared_pool_size,SHARED_POOL_RESERVED_SIZE#从 library 中获取 执行次数最多的 top 10sql 查看他们的具体信息 (未含解析次数 COL exec

41、s FORMAT 9999990COL parses FORMAT 99990COL fetches FORMAT 999990COL loads FORMAT 9990COL invalids FORMAT 9999990COL i/oFORMAT 99990COL hits FORMAT 9999990COL rows FORMAT 999990COL sorts FORMAT 9990COL sql FORMAT a32SELECT *FROM(SELECT executions "Execs"-Executions,parse_calls"Parses&q

42、uot;-Parses,fetches "Fetches"-Fetches,loads"Loads"-Loads and reloads,invalidations "Invalids"-Invalidations,disk_reads"I/O"-I/O,buffer_gets"Hits"-Buffer hits,rows_processed"Rows"-Rows,sorts"Sorts"-Sorts,sql_text"SQL"FROM

43、 v$sqlareaORDER BY executions DESCWHERE ROWNUM <=10;#查询还保留在 library cache 中,解析次数和执行次数最多的 sql(解析 *执行 COL sql_textFORMAT A38;SELECT *FROM(SELECT parse_calls*executions"Product", parse_calls"Parses",executions "Execs", sql_textFROM v$sqlareaORDER BY 1DESCWHERE ROWNUM &l

44、t;=10;#查看 shared pool 中 ,row cache 详细信息SELECT COUNT "Entries", GETS "Requests", GETMISSES "Misses",MODIFICATIONS "DML Activity", parameter "Area"FROM v$rowcache;#DBMS_SHARED_POOL.UNKEEP(object,type;#查看 shared pool 建议SELECT (SELECTROUND(value/1024/102

45、4,0FROM v$parameter WHERE name ='shared_pool_size'"Current Mb", shared_pool_size_for_estimate"Projected Mb", ROUND(shared_pool_size_factor*100"%", ESTD_LC_SIZE"Library Mb", ESTD_LC_TIME_SAVED"Parse Savings",to_char(ESTD_LC_MEMORY_OBJECT_HITS,

46、'999999999999'"Hits" FROM v$shared_pool_adviceORDER BY 1;#查看 library cache 内存分配情况 (对哪类对象 SELECT lc_namespace"Library",LC_INUSE_MEMORY_OBJECTS"Objects",LC_INUSE_MEMORY_SIZE"Objects Mb",LC_FREEABLE_MEMORY_OBJECTS"Freeable Objects",LC_FREEABLE_M

47、EMORY_SIZE"Freeable Mb"FROM v$library_cache_memory;#查看 pgashow parameters area_size#查看 pgaSELECT *FROM v$pgastat;查看 pga 建议SELECT (SELECTROUND(value/1024/1024,0FROM v$parameter WHERE name ='pga_aggregate_target'"Current Mb", ROUND(pga_target_for_estimate/1024/1024,0"P

48、rojected Mb" , ROUND(estd_pga_cache_hit_percentage"%"FROM v$pga_target_adviceORDER BY 2;#Databaseread buffer cache hit ratio =#1 (physicalreads /(dbblock gets +consistent getsSELECT to_char(value,'9999999999999',name FROM V$SYSSTAT WHERE name IN ('physicalreads', '

49、db block gets', 'consistent gets'#查询数据库命中率SELECT 'Database Buffer Cache Hit Ratio ' "Ratio", ROUND(1-(SELECTSUM(valueFROM V$SYSSTAT WHERE name ='physical reads'/(SELECTSUM(valueFROM V$SYSSTATWHERE name ='db block gets'+(SELECTSUM(valueFROM V$SYSSTAT WHER

50、E name ='consistent gets' *100|'%'"Percentage"FROM DUAL;#alter table xx cache#查看 cache 中所有 pool, 命中情况COL pool FORMAT a10;SELECT "Pool", a.physical_reads,a.db_block_gets, a.consistent_gets,(SELECTROUND(1-(physical_reads/(db_block_gets+consistent_gets*100 FRO

51、M v$buffer_pool_statisticsWHERE db_block_gets+consistent_gets!=0AND name = "Ratio"FROM v$buffer_pool_statisticsa;#ALTERTABLE(INDEXxx STORAGE(BUFFER_POOLKEEP;#查看数据库 cache 或 keep 了哪些 objectCOL table_nameFORMAT A16COL index_nameFORMAT A16SELECT table_nameAS "Table", NULL, buff

52、er_pool,cache FROMuser_tablesWHERE buffer_pool!='DEFAULT' OR TRIM(cache='Y'UNIONSELECT table_name,index_name,NULL, buffer_poolFROMuser_indexesWHERE buffer_pool!='DEFAULT'ORDER BY 1, 2NULLS FIRST;#取消 cache 或 keep(keeppool#ALTERTABLE XX NOCACHE;#SELECT'ALTER INDEX '|ind

53、ex_name|'STORAGE(BUFFER_POOLDEFAULT;'#FROMUSER_INDEXESWHERE BUFFER_POOL!='DEFAULT'*#检查 undoshow parameter undo_#检查 undo rollback segment 使用情况select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstata,v$rollnameb where a.usn=b.usnorder by waits desc;select a.redoblocks

54、/b.trancountfrom (selectvalue redoblocks from v$sysstatwhere name='redo blocks written' a ,(selectvalue trancount from v$sysstatwhere name='usercommits' b;#计算每秒钟产生的 undoblk 数量select sum(undoblks/sum(end_time-begin_time*24*60*60from v$undostat;#Undospace=UR*UPS*blocksize+overload(10%,

55、计算 undo tablespace 大小show parameter block_sizeshow parameter undo_retention#selectundo_retention*每 秒 产 生 undoblk 数 量 *block_size/1024/1024/1024+ (1+1undo_retention*每秒产生 undoblk 数量 *block_size/1024/1024/1024*0.1from dual;#查询 undo 具体信息COL undob FORMAT 99990;COL trans FORMAT 99990;COL snapshot2old FORM

56、AT 9999999990;SELECT undoblks "UndoB", txncount "Trans",maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency",ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"FROM v$undostat;#查询 rollback 段详细信息 (收缩次数 , 扩展次数 , 平均活动事务等 COL RBS F

57、ORMAT a4;SELECT "RBS", s.extends "Extends", s.shrinks"Shrinks",s.wraps "Wraps", s.aveshrink "AveShrink",s.aveactive "AveActive"FROM v$rollnamen JOIN v$rollstats USING(usnWHERE !='SYSTEM'#查询当前 rollback segment 使用情况COL R

58、BS FORMAT a4;SELECT "RBS", s.status, s.waits, s.gets, to_char(s.writes,'9999999999999' ,s.xacts "Active Trans"FROM v$rollnamen JOIN v$rollstats USING(usnWHERE !='SYSTEM'#查询使用 rollback segment 时等待比率SELECT ROUND(SUM(waits/gets*100,2|'%'"Co

59、ntention" FROMv$rollstat;#查询使用 rollback segment 时等待比率及其平局活动事务数COL contention FORMAT 9999999990;SELECT AVG(xacts"Trans per RBS",ROUND(SUM(waits/gets*100,2|'%'"Contention"FROM v$rollstat;*查看其它 性能指标信息 (等待事件 ,latch 等 , 排序 , 解 析 ,index 使用率 #查看数据库中行 chainSELECT 'Chaine

60、d Rows ' "Ratio", ROUND(SELECTSUM(valueFROM V$SYSSTATWHERE name ='table fetch continued row'/(SELECTSUM(valueFROM V$SYSSTATWHERE name IN ('tablescan rows gotten', 'table fetch byrowid'*100, 3|'%'"Percentage"FROM DUAL;#在内存中排序比率 (最优排序 SELECT '

61、;Sorts in Memory ' "Ratio", ROUND(SELECTSUM(valueFROM V$SYSSTATWHERE name ='sorts (memory'/(SELECTSUM(valueFROM V$SYSSTATWHERE name IN ('sorts(memory','sorts (disk'*100, 5|'%'"Percentage"FROM DUAL;#查询解析比率SELECT 'Soft Parses ' "Rati

62、o", ROUND(SELECTSUM(valueFROM V$SYSSTAT WHERE name ='parse count (total'-(SELECTSUM(valueFROM V$SYSSTATWHERE name ='parse count (hard'/(SELECTSUM(valueFROM V$SYSSTAT WHERE name ='execute count'*100, 2|'%'"Percentage"FROM DUALUNIONSELECT 'Hard Parses

63、 ' "Ratio", ROUND(SELECTSUM(valueFROM V$SYSSTAT WHERE name ='parse count (hard' /(SELECTSUM(valueFROM V$SYSSTATWHERE name ='execute count' *100, 2|'%' "Percentage"FROM DUALUNIONSELECT 'Parse Failures ' "Ratio", ROUND(SELECTSUM(valueFROM V$SYSSTATWHERE name ='parse count (failures'/(SELECTSUM(valueFROM V$SYS