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