Oracle日常监控（一）

1.Librarycache命中率

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

2.buffer命中率

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.数据字典命中率

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

**4.latch命中率
**

 from v$latch l;
latch_hitratio
---------------------------------------
        99.80

5.log buffer命中率

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命中率

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率

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