DB CACHE HIT RATIO
The DB Cache Hit Ratio is important as it reflects the percentage of time the data was found in memory without the need to go to disk. The ratio should be greater than 90% for OLTP type applications. A low buffer hit ratio implies that the excessive CPU is being spent on filling database buffers. If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.
Large values of the initialization parameter ‘db_file_multiblock_read_count’ influence the optimizer to increasingly favor full table scans. Oracle is very sensitive to the setting of this parameter, and with an OLTP application, clients are usually best served by the default setting of 8.
To identify the DB Cache Hit Ratio execute the following:
SELECT 1 – (phy.value – lob.value – dir.value)/ses.value “Cache Hit Ratio”
FROM v$sysstat ses,
v$sysstat lob,
v$sysstat dir, v$sysstat phy
WHERE ses.name =’session logical reads’ AND
dir.name =’physical reads direct’ AND
lob.name =’physical reads direct (lob)’ AND
phy.name =’physical reads’
Once again, there is no simple answer on how to fix the DB Cache Hit Ratio if it is below 90%. Throwing more memory at the problem can have adverse affects if the real problem is not understood. For example, if the system is performing full table scans throughout the day, the hit ratio will be lower. In this case, it is not likely that more memory will help.
Comments are currently closed.