Buffer Cache Hit Ratio

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

•  Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

•  DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

•  Physical Reads - The cumulative number of blocks read from disk.

•  Logical reads are the sum of consistent gets and db block gets.

•  The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

•  Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

 To Check with Query

select   sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",

            sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",

            sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",

            round((sum(decode(name, 'consistent gets',value, 0)) +

                   sum(decode(name, 'db block gets',value, 0)) -

                   sum(decode(name, 'physical reads',value, 0))) /

                  (sum(decode(name, 'consistent gets',value, 0)) +

                   sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"

from   v$sysstat;

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

•  Gets - Total number of requests for information on the data object.

•  Cache Misses - Number of data requests resulting in cache misses

•  Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

To Check with Query

select   sum(GETS),

            sum(GETMISSES),

            round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)

from    v$rowcache;

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

•  Pins - The number of times a pin was requested for objects of this namespace.

•  Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

•  Hit Ratio should be > 85%

To Check with Query

select   sum(PINS) Pins,

            sum(RELOADS) Reloads,

            round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio

from    v$librarycache;

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

•  Executions - The number of times a pin was requested for objects of this namespace.

•  Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

•  Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

To Check with Query

select   sum(PINS) Executions,

            sum(RELOADS) cache_misses,

            sum(RELOADS) / sum(PINS) miss_ratio

from    v$librarycache;