SGA Hit Ratio’s
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
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
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%
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
select sum(PINS) Executions, sum(RELOADS) cache_misses, sum(RELOADS) / sum(PINS) miss_ratio from v$librarycache