Hit/Miss Ratios

Here are some scripts related to Hit/Miss Ratios .
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