Oracle Database Discussions

I need help here.

I am running few scripts every 30 Mins to check the Cache Hit ratio. My db is sometime suddenly going slow i wanted to monitor the hit ratio.

Here are the scripts running for every 30 Min.

Buffer Cache Hit ratio


Code: Select all
SELECT (1 - (v3.VALUE / (v1.VALUE + v2.VALUE))) * 100
INTO v_cache_hit_ratio
FROM v$sysstat v1,
v$sysstat v2,
v$sysstat v3
WHERE v1.NAME = 'db block gets'
AND v2.NAME = 'consistent gets'
AND v3.NAME = 'physical reads';


Question here is, Let us say, consistent gets is 1000. It means, oracle access 1000 blocks in consistent mode or oracle access 1000 times in consistent mode??

Library Cache Hit ratio


Code: Select all
SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100
FROM V$LIBRARYCACHE;


Question here is, Is this the correct query to check the library cache hit ratio?


Dictionary Cache Hit ratio

Code: Select all
SELECT (SUM(GETS)/(SUM(GETS)+SUM(GETMISSES))) * 100
FROM
V$ROWCACHE;


Currently i am using this query. But i do see different query when i was googling...

here are the other queries when i was googling.


Code: Select all
SElect (sum(getmisses)/sum(gets)) * 100 from v$rowcache.



Code: Select all
select round((1-(sum(getmisses)/sum(gets))),5) * 100 "RowCache Hit Ratio" from V$ROWCACHE;



Code: Select all
SELECT SUM(gets), SUM(getmisses),
(1 - (SUM(getmisses) / (SUM(gets) + SUM(getmisses)))) * 100 hitrat FROM v$rowcache;


I am not sure which one i should use...

Memory Sort


Code: Select all
SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
0,1,(a.VALUE + b.VALUE)),2)
INTO v_precent_memory_sort
FROM v$sysstat a,
v$sysstat b
WHERE a.name = 'sorts (disk)'
AND b.name = 'sorts (memory)';


The query output is always 99%, 100%. If it reduces, let us say, 60% or 70%, then what is the action i should do?
I am using oracle 9.2.0.8. Should i crease the PGA_AGGREGATE_TARGET?

Thanks