命中率
1)Library Cache的命中率:
.计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。
2)计算共享池内存使用率:SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'
FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';其中: &TSP_IN_M是你的总的共享池的SIZE(M)
共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。
查询空闲的共享池内存:
SELECT pool,name,BYTES/1024/1024 "MB" FROM V$SGASTAT
WHERE NAME = 'free memory' AND POOL = 'shared pool'; 3)db buffer cache命中率:计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE
---第二种办法：oracle官方文档方法命中率的计算公式为:
Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets + consistent gets - physical reads direct - physical reads direct (lob))分别代入上一查询中的结果值,就得出了Buffer cache的命中率
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN('session logical reads', 'physical reads', 'physical reads direct', 'physical reads direct (lob)', 'db block gets', 'consistent gets'); 4)数据缓冲区命中率：select value from v$sysstat where name ='physical reads';
select value from v$sysstat where name ='physical reads direct'; select value from v$sysstat where name ='physical reads direct (lob)'; select value from v$sysstat where name ='consistent gets'; select value from v$sysstat where name = 'db block gets'; 这里命中率的计算应该是 令 x = physical reads direct + physical reads direct (lob) 命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100 通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区 5)共享池的命中率：select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;
假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存
6)计算在内存中排序的比率:
SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';—查询内存排序数
SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';—查询磁盘排序数--caculate sort in memory ratio
SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM dual;此比率越大越好,太小整要考虑调整,加大PGA
7)PGA的命中率:计算公式:BP x 100 / (BP + EBP)
BP: bytes processed
EBP: extra bytes read/written
SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';
或者从OEM的图形界面中查看
我们可以查看一个视图以获取Oracle的建议值:
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE;TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63 23 367
125 24 30
250 30 3
375 39 0
500 58 0
600 59 0
700 59 0
800 60 0
900 60 0
在此例中:PGA至少要分配375M
我个人认为PGA命中率不应该低于50%
以下的SQL统计sql语句执行在三种模式的次数: optimal memory size, one-pass memory size, multi-pass memory size:
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total,4)) percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%'); 8)共享区字典缓存区命中率计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)
命中率应大于0.85
select sum(gets-getmisses-usage-fixed)/sum(gets)
from v$rowcache; 9)数据高速缓存区命中率计算公式：1-(physical reads / (db block gets + consistent gets))
命中率应大于0.90最好
select name,value
from v$sysstat where name in ('physical reads','db block gets','consistent gets'); 10)共享区库缓存区命中率计算公式：SUM(pins - reloads) / SUM(pins)
命中率应大于0.99
select sum(pins-reloads)/sum(pins)
from v$librarycache; 11)检测回滚段的争用SUM(waits)值应小于SUM(gets)值的1%
select sum(gets),sum(waits),sum(waits)/sum(gets)
from v$rollstat;
12)检测回滚段收缩次数
select name,shrinks
from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn;