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

1、*os#检查oracle进程 ps _ef|grep ora|wc -lcrs_stat -t -vtopglancecrsctl check cluster (cssd)vmstat 3600 6*#database 概况信息检查#检查database基本信息select * from v$version;select name ,open_mode,log_mode from v$database;select instance_number,instance_name ,status from gv$instance;show parameter cpu_cshow parameter

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

3、name;#检查表空间SELECTTABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;#检查数据文件状态select count(*),status from v$datafile group by status;#检查表空间使用情况selectf.tablespace_name,a.total,f.free,(a.total-f.free)/1024 "used SIZE(G)",round(f.free/a.total)*100)

4、"% Free"from(select tablespace_name, sum(bytes/(1024*1024) total from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024) free from dba_free_space group by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+)order by "% Free"

5、;/# 查询临时 segment 使用情况COL username 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.avgiot

6、im "Average I/O Time", "Datafile"FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;# 查看所有数据文件 i/o 情况 ,若文件太多 , 可以改写为 top 10 select *( order by xx desc) where rownum<=10COL ts FORMAT a10 HEADING "Tablespace"COL reads FORMAT 999990;COL writes FORMAT 99

7、9990;COL br FORMAT 999990 HEADING "BlksRead"COL bw FORMAT 999990 HEADING "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",

8、 fs.writetim "WTime"FROM v$tablespace ts, v$datafile df, v$filestat fsWHERE 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 "WTi

9、me"FROM v$tablespace ts, v$tempfile tf, v$tempstat tsWHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;# 查看所有数据文件 i/o 情况 ( 比率 ), 若文件太多 , 可以改写为 top 10 select *( order by xx desc) where rownum<=10COL ts FORMAT a10 HEADING "Tablespace"COL reads FORMAT 999990;COL writes FORMAT 9

10、99990;COL br FORMAT 999990 HEADING "BlksRead"COL bw FORMAT 999990 HEADING "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"

11、;, fs.writetim "WTime"FROM v$tablespace ts, v$datafile df, v$filestat fsWHERE 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 "W

12、Time"FROM v$tablespace ts, v$tempfile tf, v$tempstat tsWHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;# 获取 top 10 热 segmentcol objct_name for a30select * from(selectob.owner, ob.object_name, sum(b.tch) Touchs from x$bh b , dba_objects ob where b.obj = ob.data_object_id and b.ts# >

13、0group by ob.owner, ob.object_nameorder by sum(tch) desc)where rownum <=10# 判断物理读最多的 object select * from (select owner,object_name,value from v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10# 查看热点数据文件 （从单块读取时间判断 ）SELECT t.file_name,t.tab

14、lespace_name,round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,s.READTIM,s.WRITETIMFROM v$filestat s, dba_data_files tWHERE s.file# = t.file_id and rownum<=10 order by cs desc查看 redo*#检查日志切换频率 select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round(first_time-lag(first_

15、time) over(order by first_time)*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes;# 检查 lgwr i/o 性能 (time_waited/total_waits ：表示平均 lgwr 写入完成时间 若 >1 表示写入过慢 ) select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_eve

16、nt where event = 'log file parallel write'# 检查与 redo 相关性能指标select name,value from v$sysstat where name like '%redo%'# 查询 redo block sizeselect max(lebsz) from x$kccle;# 查看 redo allocation latch col name for a30select name,gets,misses,misses/gets from v$latch_children where name='

17、redo allocation'# 查看与 redo 相关等待事件col event format a40select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%'# 查看 session redo eventselect event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%REDO%

18、'# 查看 user commit 次数select to_number(value,99999999999) from v$sysstat where name='user commits'#查看系统运行时间select (sysdate - startup_time)*24*60*60 as seconds from v$instance#计算出每秒用户提交次数select user_commit 次数 /系统运行时间 from dual;# 计算出每个事务平均处理多少个 redo block select value from v$sysstat where na

19、me = 'redo blocks written'select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') bit*. m o*sga,pga,命中率# sga,pga, 命中率# 检查 sgashow sga select * from v$sg

20、a;# 查看 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) "Buffer Hit Ratio" from v$sysstat;# 查看 buffer cache 建议select size_for_estimate, estd_physical_r

21、ead_factor, to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from v$db_cache_advice where name = 'DEFAULT'COL pool FORMAT a10;SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameterWHERE name = 'db_cache_size') "Current Cache(Mb)",name

22、 "Pool", size_for_estimate "Projected Cache(Mb)" ,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"FROM v$db_cache_adviceWHERE block_size = (SELECT value FROM v$parameterWHERE name = 'db_block_size')ORDER BY 3;# 查看 pgashow parameter pga# 查看 cache 池show p

23、arameter cache# 查看 buffer cache 中 defalut pool 命中率select name,1-(physical_reads)/(consistent_gets+db_block_gets) from v$buffer_pool_statistics;# 检查 shared poolshow parameter shared# 检查 shared pool 中 library cacheselect namespace,pinhitratio from v$librarycache;# 检查整体命中率 (library cache)select sum(pin

24、hits)/sum(pins) from 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$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool'#

25、 检查 row cache（ 数据字典缓冲区 ） 命中率#当执行一个dml或ddl都会造成对数据字典的递归修改 column updates format 999,999,999SELECT parameter,sum(gets),sum(getmisses),100*sum(gets - getmisses) / sum(gets) pct_succ_gets,sum(modifications) updatesFROM V$ROWCACHEWHERE gets > 0GROUP BY parameter;SELECT (SUM(GETS - GETMISSES - FIXED) /

26、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$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.in

27、dx and a.indx = c.indx and a.ksppinm = '_kghdsidx_count：#每个子shared pool由 单独的shared pool latch 保护 查看 他们的命中率shared pool latch ，用于shared pool 空间 回收分配使用的 latch col name format a15shared pool'select addr,name,gets,misses,1-misses/gets from v$latch_children where name='# 查看 shared pool 建议colum

28、n c1 heading 'Pool |Size(M)'column c2 heading 'Size|Factor'column c3 heading 'Est|LC(M) 'column c4 heading 'Est LC|Mem. Obj.'column c5 heading 'Est|Time|Saved|(sec)'column c6 heading 'Est|Parse|Saved|Factor' column c7 heading 'Est|Object Hits'

29、format 999,999,999SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2, estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5, estd_lc_time_saved_factor c6,to_char(estd_lc_memory_object_hits,99999999999) c7 FROMV$SHARED_POOL_ADVICE;# 使用 v$shared_pool_advice 算不同 shared pool 大小

30、情况下，响应时间， S 单位SELECT 'Shared Pool' component, shared_pool_size_for_estimate estd_sp_size, estd_lc_time_saved_factor parse_time_factor, CASEWHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN 0ELSE current_parse_time_elapsed_s + adjustment_s END response_timeFROM (SELECT shared_pool_

31、size_for_estimate, shared_pool_size_factor, estd_lc_time_saved_factor, a.estd_lc_time_saved,e.VALUE / 100 current_parse_time_elapsed_s, c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s FROM v$shared_pool_advice a,(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e, (SELECT

32、estd_lc_time_saved FROM v$shared_pool_adviceWHERE shared_pool_size_factor = 1) c)/# 查看 shared pool 中 各种类型的 chunk 的大小数量SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, To_char( (SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024),'999,999.00')|'k' "AVG SIzE" FROM X$KSMSP GROUP

33、 BY KSMCHCLS;#查看是否有库缓冲有关的等待事件select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%'#row cache 命中率SELECT 'Dictionary Cache Hit Ratio ' "Ratio",ROUND(1 - (SUM(GETMISSES) / SUM(GETS) * 100,2)|'%'"Percentage"FROM

34、 V$ROWCACHE;#library cache 中详细比率信息SELECT 'Library Lock Requests' "Ratio", ROUND(AVG(gethitratio) * 100, 2)|'%' "Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library Pin Requests' "Ratio", ROUND(AVG(pinhitratio)* 100, 2)|'%' "Percentag

35、e" FROM V$LIBRARYCACHEUNIONSELECT 'Library I/O Reloads' "Ratio", ROUND(SUM(reloads) / SUM(pins) * 100, 2)|'%' "Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library Reparses' "Ratio", ROUND(SUM(reloads) / SUM(pins) * 100, 2) |'%' "

36、;Percentage" FROM V$LIBRARYCACHE;# 查询 sga 中各个 pool 情况COL name FORMAT a32;SELECT pool, name, bytes FROM v$sgastatWHERE pool IS NULLOR pool != 'shared pool' OR (pool = 'shared pool' AND (name IN('dictionary cache','enqueue','library cache','parameters&#

37、39;,'processes','sessions','free memory')ORDER BY pool DESC NULLS FIRST, name;SELECT * FROM V$SGAINFO;# 查看使用 shard_pool 保留池情况SELECT request_misses, request_failures, free_space FROM v$shared_pool_reserved;#Oracle 专门从共享池内置出一块区域来来分配内存保持这些大块。这个保留共享池的默认大小是共享池的 5%(_shared_pool_res

38、erved_pct 5控制 ) oracle 建设置为 10% 。大小通过参数SHARED_POOL_RESERVED_SIZE 改。它是从共享池中分配，不是直接从 SGA 中分配的，它是共享池 的保留部分，专门用于存储大块段#shared pool 中内存大于 _SHARED_POOL_RESERVED_MIN_ALLOC将放入 shared pool 保留池 ,保留池维护一个单独的 freelist,lru ，并且不会在 lru 列表存 recreatable 类型 chunks ，普通 shared pool 的释放 与 shared pool 保留池无关。# 关于设置 SHARED_P

39、OOL_RESERVED_SIZE#1. 如果 系统出现 ora-04031, 发现 请求内存都是大于 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且 v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以 看下 LAST_MISS_SIZE ) 表示 SHARED_POOL_RESERVED_SIZE 太小了 需要大的内存的请求失败 ,那么需 要加大 SHARED_POOL_RESERVED_SIZE#2.如果ora-04031 请求内存出现在 4100-4400 并造成shared

40、pool Iru 合并,老化换出内存 ，可以 调小 _SHARED_POOL_RESERVED_MIN_ALLOC让此部分内存 进入 shared reserved pool, 相应的加大SHARED_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_PO

41、OL_RESERVED_SIZE, 减少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放对象，并相对加大 shared_pool_size#要是反过来 REQUEST_FAILURES>0（ 出现过ora-04031）且LAST_FAILURE_SIZE（ 最后请求内存大小）v_SHARED_POOL_RESERVED_MIN_ALLOC, 表示在shared pool中缺少连续内存，可以加减少_SHARED_POOL_RESERVED_MIN_ALLOC多放入一些对象，减少 shared pool 压力，适当加大shared_pool_size,SHARED_PO

42、OL_RESERVED_SIZE#从library中获取执行次数最多的top 10 sql查看他们的具体信息（未含解析次数）COL execs FORMAT 9999990COL parses FORMAT 99990COL fetches FORMAT 999990COL loads FORMAT 9990COL invalids FORMAT 9999990COL i/o FORMAT 99990COL hits FORMAT 9999990COL rows FORMAT 999990COL sorts FORMAT 9990COL sql FORMAT a32SELECT * FROM（

43、SELECT executions "Execs"-Executions,parse_calls "Parses"-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 &

44、quot;Rows"-Rows,sorts"Sorts"-Sorts,sql_text "SQL"FROM v$sqlareaORDER BY executions DESC) WHERE ROWNUM <= 10;#查询还保留在library cache中，解析次数和执行次数最多的sql(解析*执行)COL sql_text FORMAT A38;SELECT * FROM(SELECT parse_calls*executions "Product", parse_calls"Parses",e

45、xecutions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)WHERE ROWNUM <= 10;#查看 shared pool 中 ,row cache 详细信息SELECT COUNT "Entries", GETS "Requests", GETMISSES "Misses",MODIFICATIONS "DML Activity", parameter "Area"FROM v$rowcache;#DBM

46、S_SHARED_POOL.UNKEEP (object, type);#查看 shared pool 建议SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameterWHERE name = 'shared_pool_size') "Current Mb", shared_pool_size_for_estimate "Projected Mb", ROUND(shared_pool_size_factor*100) "%", ESTD_LC_SIZE "

47、Library Mb", ESTD_LC_TIME_SAVED "Parse Savings",to_char(ESTD_LC_MEMORY_OBJECT_HITS,'999999999999') "Hits"FROM v$shared_pool_adviceORDER BY 1;# 查看 library cache 内存分配情况 （ 对哪类对象 ）SELECT lc_namespace "Library"丄CNUSE_MEMORY_OBJECTS "Objects"丄 C_INUSE_M

48、EMORY_SIZE "Objects Mb"丄C_FREEABLE_MEMORY_OBJECTS "Freeable Objects"丄C_FREEABLE_MEMORY_SIZE "Freeable Mb"FROM v$library_cache_memory;#查看pgashow parameters area_size#查看pgaSELECT * FROM v$pgastat;查看pga建议SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameterWHERE name =

49、'pga_aggregate_target') "Current Mb",ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb",ROUND(estd_pga_cache_hit_percentage) "%"FROM v$pga_target_adviceORDER BY 2;#Database read buffer cache hit ratio = #1 -(physical reads / (db block gets + consistent ge

50、ts)SELECT to_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN ('physical reads', 'db block gets', 'consistent gets');#查询数据库命中率SELECT 'Database Buffer Cache Hit Ratio ' "Ratio",ROUND(1-(SELECT SUM(value) FROM V$SYSSTAT WHERE name = '

51、physical reads')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets') + (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets') ) * 100)|'%' "Percentage"FROM DUAL;# alter table xx cache# 查看 cache 中所有 pool, 命中情况COL pool FORMAT a10;SELECT a.n

52、ame "Pool", a.physical_reads, a.db_block_gets, a.consistent_gets,(SELECT ROUND(1-(physical_reads / (db_block_gets + consistent_gets)*100) FROM v$buffer_pool_statisticsWHERE db_block_gets+consistent_gets != 0AND name = ) "Ratio"FROM v$buffer_pool_statistics a;#ALTER TABLE(IN

53、DEX) xx STORAGE(BUFFER_POOL KEEP);# 查看数据库 cache 或 keep 了哪些 objectCOL table_name FORMAT A16COL index_name FORMAT A16SELECT table_name AS "Table", NULL, buffer_pool, cache FROM user_tablesWHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y'UNIONSELECT table_name, index_name, NUL

54、L, buffer_pool FROM user_indexesWHERE buffer_pool != 'DEFAULT'ORDER BY 1, 2 NULLS FIRST;# 取消 cache 或 keep(keep pool)#ALTER TABLE XX NOCACHE;#SELECT 'ALTER INDEX '|index_name|' STORAGE(BUFFER_POOL DEFAULT);' #FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT'*# 检查 undoshow

55、 parameter undo_# 检查 undo rollback segment 使用情况select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where a.usn=b.usn order by waits desc;select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(sel

56、ect value trancount from v$sysstat where name='user commits') b;# 计算每秒钟产生的 undoblk 数量select sum(undoblks)/sum(end_time-begin_time)*24*60*60) from v$undostat;#Undospace=UR*UPS*blocksize + overload(10%),计算 undo tablespace 大小show parameter block_sizeshow parameter undo_retention每秒产#select undo_

57、retention* 每秒产生 undoblk 数量 *block_size/1024/1024/1024+ (1+1undo_retention* 生 undoblk 数量 *block_size/1024/1024/1024*0.1) from dual;# 查询 undo 具体信息COL undob FORMAT 99990;COL trans FORMAT 99990;COL snapshot2old FORMAT 9999999990;SELECT undoblks "UndoB", txncount "Trans",maxquerylen &

58、quot;LongestQuery", maxconcurrency "MaxConcurrency",ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"FROM v$undostat;# 查询 rollback 段详细信息 (收缩次数 ,扩展次数 ,平均活动事务等 )COL RBS FORMAT a4;SELECT "RBS", s.extends "Extends", s.shrinks"Shr

59、inks",s.wraps "Wraps", s.aveshrink "AveShrink",s.aveactive "AveActive"FROM v$rollname n JOIN v$rollstat s USING(usn)WHERE != 'SYSTEM'# 查询当前 rollback segment 使用情况COL RBS FORMAT a4;SELECT "RBS", s.status, s.waits, s.gets, to_char(s.wri

60、tes,'9999999999999'),s.xacts "Active Trans"FROM v$rollname n JOIN v$rollstat s USING(usn)WHERE != 'SYSTEM'# 查询使用 rollback segment 时等待比率SELECT ROUND(SUM(waits/gets)*100,2)|'%' "Contention" FROMv$rollstat;# 查询使用 rollback segment 时等待比率及其平局活动事务数COL cont

61、ention FORMAT 9999999990;SELECT AVG(xacts) "Trans per RBS",ROUND(SUM(waits/gets)*100,2)|'%' "Contention"FROM v$rollstat;*查看其它性能指标信息(等待事件，latch等排序，解析,index使用率)# 查看数据库中行 chainSELECT 'Chained Rows ' "Ratio"， ROUND(SELECT SUM(value) FROM V$SYSSTATWHERE name

62、= 'table fetch continued row')/ (SELECT SUM(value) FROM V$SYSSTATWHERE name IN ('table scan rows gotten', 'table fetch byrowid')* 100, 3)|'%' "Percentage"FROM DUAL;# 在内存中排序比率 (最优排序 )SELECT 'Sorts in Memory ' "Ratio", ROUND(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)')/ (SELECT SUM(value) FROM V$SYSSTATWHERE name IN ('sorts (memory)', 'sorts (disk)') * 100, 5)|'%' "Percentage"FROM DUAL;#查询解析比率SELECT 'Soft Parses ' "R