Oracle常见监控(一)

1.Librarycache命中率

SELECT round(100 * SUM(e.PINHITS) / SUM(e.PINS),2)
  FROM v$librarycache e
ROUND(100*SUM(E.PINHITS)/SUM(E.PINS),2)
---------------------------------------
                                  77.54

2.buffer命中率

SELECT
to_char(100*(1 - phy.value/(c.value+db.value)),'fm999999999.90')  Buffer_cache_hit
 FROM v$sysstat c, v$sysstat db,v$sysstat phy
 where c.name = 'consistent gets'
 AND db.name = 'db block gets'
 AND phy.name = 'physical reads';
BUFFER_CACHE_HIT
---------------------------------------
92.41

3.数据字典命中率

select to_char(100*sum(GETS)/sum(GETS+GETMISSES),9999990.90)   rowcache_hit from v$rowcache;
SQL> select to_char(100*sum(GETS)/sum(GETS+GETMISSES),9999990.90)   rowcache_hit from v$rowcache;
ROWCACHE_HIT
------------------------------
      92.4

4.latch命中率

select  to_char(100*(1-sum(l.MISSES+l.IMMEDIATE_MISSES)/sum(l.GETS+l.IMMEDIATE_GETS)),'999999999.90') "latch_hitratio"
 from v$latch l;
latch_hitratio
---------------------------------------
        99.80

5.log buffer命中率

select min(Hit_ratio) from (
SELECT name, gets, misses, immediate_gets, immediate_misses,
to_char(Decode(gets+immediate_gets,0,0,1-(misses+immediate_misses)/(gets+immediate_gets))*100,'999.90') Hit_ratio
FROM v$latch WHERE name IN ('redo allocation', 'redo copy'))
MIN(HIT_RATIO)
---------------------
  98.79

6.sort parse命中率

select to_char((1-a.VALUE/b.VALUE)*100,'999.90') as soft_pars_hit from 
v$sysstat a,v$sysstat b
where a.NAME='parse count (hard)'
and b.NAME='parse count (total)'
SOFT_PARS_HIT
---------------------
  36.83

7.in-mem sort率

select to_char(a.value/(b.value+c.value)*100,'999.90') as in_mem_sort_hit
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='sorts (memory)' and b.name='sorts (memory)' and c.name='sorts (disk)';
IN_MEM_SORT_HIT
---------------------
 100.00