Oracle常用巡检脚本－金锄头文库

资源描述

《Oracle常用巡检脚本》由会员分享，可在线阅读，更多相关《Oracle常用巡检脚本（36页珍藏版）》请在金锄头文库上搜索。

1、Oracle常用巡检脚本-SGA-SGA 各部分大小show sgaselect * from v$sga;SELECT * FROM V$SGAINFO;-SGA设置大小 show parameter sga_target-SGA各个池大小COL name FORMAT a32;SELECT pool, name, bytes/1024/1024 MFROM v$sgastatWHERE pool IS NULLOR pool != shared poolOR (pool = shared pool AND(name IN(dictionary cache, enqueue, library

2、cache, parameters, processes, sessions, free memory)ORDER BY pool DESC NULLS FIRST, name;-BUFFER CACHE-查看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 Ratiofrom v$sysstat;sel

3、ect name,physical_reads,(consistent_gets + db_block_gets) logic_reads,1 - (physical_reads) / (consistent_gets + db_block_gets) hit_radiofrom v$buffer_pool_statistics;-查看buffer cache建议 select size_for_estimate,estd_physical_read_factor,to_char(estd_physical_reads, 99999999999999999999999) asestd_phys

4、ical_readsfrom v$db_cache_advicewhere name = DEFAULT;-查看buffer cache建议 -适用于指定SGA的目的COL pool FORMAT a10;SELECT (SELECT ROUND(value / 1024 / 1024, 0)FROM v$parameterWHERE name = db_cache_size) Current Cache(Mb),name Pool,size_for_estimate Projected Cache(Mb),ROUND(100 - estd_physical_read_factor, 0) C

5、ache Hit Ratio%FROM v$db_cache_adviceWHERE block_size = (SELECT value FROM v$parameter WHERE name = db_block_size)ORDER BY 3;-查看cacheshow parameter cache-各种读取的统计-Database read buffer cache hit ratio =-1 (physical reads / (db block gets + consistent gets)SELECT to_char(value,9999999999999), name FROM

6、 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 = physical reads)/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = db block gets)+ (SELECT SUM(value) FROM V$SYSSTAT WHERE name =

7、 consistent gets) * 100)|% PercentageFROM DUAL;-SHARED_POOL-show parameter shared-检查整体命中率(library cache)select sum(pinhits) get ,sum(pins)-sum(pinhits) miss,sum(pinhits) / sum(pins)from v$librarycache;- 查看library cache 命中率select t.NAMESPACE,t.GETHITRATIO*100from v$librarycache t;select sum(pins) hit

8、s,sum(reloads) misses,sum(pins) / (sum(pins) + sum(reloads) Hits Ratiofrom v$librarycache;e-检查shered pool free spaceSELECT * FROM V$SGASTATWHERE NAME = free memoryAND POOL = shared pool;-检查row cache(数据字典缓冲区)命中率-当执行一个dml或ddl都会造成对数据字典的递归修改column updates format 999,999,999SELECT parameter, sum(gets), s

9、um(getmisses), 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updatesFROM V$ROWCACHEWHERE gets > 0GROUP BY parameter;SELECT (SUM(GETS - GETMISSES - FIXED) / SUM(GETS) ROW CACHEFROM V$ROWCACHE;-查看Shared pool latch(多池技术)/*col parameter for a20col session for a20*/select a.ks

10、ppinm Parameter,b.ksppstvl Session Value,c.ksppstvl Instance Valuefrom sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cwhere a.indx = b.indx and a.indx = c.indxand a.ksppinm = _kghdsidx_count;-查看shared pool建议column c1 heading Pool |Size(M)column c2 heading Size|Factorcolumn c3 heading Est|LC(M) column

12、_saved_factor c6,to_char(estd_lc_memory_object_hits, 99999999999) c7FROM V$SHARED_POOL_ADVICE; -查看shared pool中各种类型的chunk的大小数量 SELECT KSMCHCLS CLASS,COUNT(KSMCHCLS) NUM,SUM(KSMCHSIZ) SIZ,To_char(SUM(KSMCHSIZ) / COUNT(KSMCHCLS) / 1024), 999,999.00) | k AVG SIzEFROM X$KSMSPGROUP BY KSMCHCLS;-查看是否有库缓冲有

13、关的等待事件select sid, seq#, event, p1, p1raw, p2, p2raw, p3, p3raw, statefrom v$session_waitwhere event like library%; -row cache命中率SELECT Dictionary Cache Hit Ratio Ratio,ROUND(1 - (SUM(GETMISSES) / SUM(GETS) * 100, 2) | % PercentageFROM V$ROWCACHE;-library cache中详细比率信息SELECT Library Lock Requests Rati

14、o,ROUND(AVG(gethitratio) * 100, 2) | % PercentageFROM V$LIBRARYCACHEUNION allSELECT Library Pin Requests Ratio,ROUND(AVG(pinhitratio) * 100, 2) | % PercentageFROM V$LIBRARYCACHEUNION allSELECT Library I/O Reloads Ratio,ROUND(SUM(reloads) / SUM(pins) * 100, 2) | % PercentageFROM V$LIBRARYCACHE ;-查看library cache 内存分配情况(对哪类对象

展开阅读全文