Hit Ratio For ORACLE

1.Share Pool Free
select pool,name,bytes/1024/1024 m from v$sgastat where name='free memory' and pool='shared pool';

2.Share Pool Hit Ratio
SELECT(1- ROUND(BYTES /(4096*1024*1024),2))*100|| '%'
FROM V$SGASTAT
WHERE NAME= 'free memory'
AND POOL = 'shared pool';

3.Buffer Cache Hit Ratio
select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
"Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';

4.Library Cache Hit Ratio
SELECT (ROUND(SUM(pinhits)/sum(pins),2))*100|| '%' FROM V$LIBRARYCACHE;

5.PGA Hit Ratio
SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';

pga設定建議值：
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;

參考網址：
http://www.cnblogs.com/jimeper/archive/2008/07/09/1238763.html
http://blog.csdn.net/luoyanqing119/archive/2008/10/30/3183099.aspx
[@more@]