buffer_pool_keep

[steeve123]

How can i know the % hit ratio of the buffer_pool_keep?

[hinermak]

-- Name: Sort Area Efficiency
-- Purpose: The percentage of sorts performed in memory
-- as opposed to sorts performed in temporary
-- segments on disk.
-- Results:
-- Data Warehouse systems should be greater than 98%
-- OLTP systems should be greater than 95%
-- Resolution:
-- Helpful indicator when DB needs more block buffers
-- or a system is not tuned very well.
-- Ref: Oracle Performance Tuning Tips & Techniques, p572
-------------------------------------------------------
-- select round((sum(decode(name, 'sorts (memory)', value, 0))
-- / (sum(decode(name, 'sorts (memory)', value, 0))
-- + sum(decode(name, 'sorts (disk)', value, 0))))
-- * 100,2) "% Sorts Performed In Memory"
-- from v$sysstat;
-------------------------------------------------------

-- Hit Ratio for Data (V$SYSSTAT View)
set serverout on
BEGIN

FOR I IN ( select
round(1 - (sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
(sum(decode(name, 'consistent gets', value, 0))))),2) * 100
ReadHitRatio
from v$sysstat) LOOP
DBMS_OUTPUT.PUT_LINE('DB Read Hit Ratio Is: ' || i.ReadHitRatio || '%');
IF (i.ReadHitRatio < 95) THEN
DBMS_OUTPUT.PUT_LINE(' Hit Ratio is very low, you must increase DB Block Buffers');
DBMS_OUTPUT.PUT_LINE(' or tune quries that are causing disk reads');

ELSE
DBMS_OUTPUT.PUT_LINE(' Hit Ratio Is Acceptable');
END IF;

END LOOP;

END;
/

[hinermak]

-- Purpose:
-- Used to find how often data dictionary calls are
-- effectively hitting the memory cache allocated by
-- the shared_pool_size.
-- Every time a statement is processed by Oracle, it
-- uses the dictionary cahce to find required info in
-- the data dictionary.
-- Results:
-- Desired results b/w 90 - 95%, less than is bad
-- Resolution:
-- A low indication means that the shared_pool_size
-- should be increased.
-- Ref: Oracle Performance Tuning Tips & Techniques, p572
-------------------------------------------------------
-------------------------------------------------------

-- Hit Ratio For The Data Cache
BEGIN

FOR I IN (select
sum(gets) gets,
sum(getmisses) getmisses ,
round((1-sum(getmisses) / (sum(gets) + sum(getmisses))) * 100, 2) HitRate
from v$rowcache) LOOP
DBMS_OUTPUT.PUT_LINE('The Dictionary Cache Hit Ratio Is: ' || i.HitRate || '%');
IF (i.HitRate < 95) THEN
DBMS_OUTPUT.PUT_LINE(' Dictionary Cache Hit Ratio Is To Low, Increase Shared Pool Size');
ELSE
DBMS_OUTPUT.PUT_LINE(' Dictionary Cache Hit Ratio is Fine, Leave Shared Pool Size As Is');
END IF;
END LOOP;

END;
/

[hinermak]

-- Purpose:
-- v$librarycache view will show how well actual
-- statementsare accessing memory. If shared_pool_size
-- is too small, enough room may not be available to
-- store all the statements into memory.
--
-- If shared pool becomes fragmented, large PL/SQL
-- may not fit. The following script will indicate how
-- often statements are being accessed from memory.
-- Results:
-- Pin Hits: greater than 95%
-- Reload Hit Ratio: Greater than 99%
-- Resolution:
-- If either of these falls below recommended value
-- increase shared pool.
-- Ref: Oracle Performance Tuning Tips & Techniques, p573
-------------------------------------------------------
-------------------------------------------------------

-- Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE View)
set serverout on
BEGIN

FOR I IN (select
sum(pins) Executions,
sum(pinhits) Hits,
round(((sum(pinhits) / sum(pins)) * 100), 2) PinHitRatio,
sum(reloads) Misses,
round(((sum(pins) / (sum(pins) + sum(reloads))) * 100), 2) RelHitRatio
from V$librarycache) LOOP
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Shared Pool');
DBMS_OUTPUT.PUT_LINE(' PINNED Hit Ratio for SQL & PL/SQL:' || i.pinHitRatio || '%');
DBMS_OUTPUT.PUT_LINE(' RELOADED Hit Ratio for SQL & PL/SQL:' || i.relHitRatio || '%');
DBMS_OUTPUT.PUT_LINE('Recommendations:');

IF (i.pinHitRatio < 95) THEN
DBMS_OUTPUT.PUT_LINE(' Pinned Hit Ratio Is Low (Below 95%), Increase shared_pool');
ELSE
DBMS_OUTPUT.PUT_LINE(' Pinned Hit Ratio Is O.K.');
END IF;

IF (i.relHitRatio < 99) THEN
DBMS_OUTPUT.PUT_LINE(' Reload hit Ratio Is Low (Below 99%), Increase shared_pool');
ELSE
DBMS_OUTPUT.PUT_LINE(' Reload Hit Ration Is O.K.');
END IF;

END LOOP;

END;
/

[padmam]

Hi, 24th April 2001 20:16 hrs chennai

Please query with this formula to work out on your problem.

1 SELECT NAME, 1-(physical_reads/(db_block_gets +consistent_gets))
2 "HIT RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE
3* db_block_gets+consistent_gets >0
SQL> /

NAME HIT RATIO
-------------------- ----------
DEFAULT .97665668

In my case there is no "keep" buffer .

SQL> select ID,NAME,BUFFERS from v$buffer_pool;

ID NAME BUFFERS
---------- -------------------- ----------
1 KEEP 0
2 RECYCLE 0
3 DEFAULT 2102

Cheers

Padmam

[Edited by padmam on 04-24-2001 at 10:59 AM]

[steeve123]

This question is for padmam.

what happends if db_block_gets+consistent_gets >0
cuse mine is = 0 .

so the query does not return a result.

[chrisrlong]

Okay, you'll have to forgive my ignorance of the dba-side of the house, but...

By definition, wouldn't the hit ratio for the buffer_pool_keep be 100%? Actually, since the tables have to be loaded from disk the first time, this would reduce it a little. But after that, it would keep creeping closer to 100% because, by definition, the blocks would never get flushed, no? This also assumes that the KEEP area is large enough to hold everything you have pointed at it. Or am I missing something?

- Chris

[steeve123]

Question for chrisrlong.

I think you have a good point with the hit ratio of the buffer
pool keep.

The only time the hit ratio would not be 100%,
is if the entire table is not in the buffer_pool_keep.

Is this a good observation?


And what can i do to check latch in the V$BUFFER_POOL_STATISTICS?