Buffer cache hit ratio:
"The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE"
Oracle has the v$db_cache_advice utility and has incorporated a buffer cache advisory into the standard AWR report, ostensibly to provide recommendations about the projected reduction in expensive disk I/O with the addition of more data buffers.
Hence, on the margin, the data buffer cache advisory is inaccurate for database with an undersized db_cache_size (and db_keep_cache_size, etc.).
The following query can be used to perform the cache advice function, once the db_cache_advice has been enabled and the database has run long enough to give representative results.
-- ***********************************************************
-- Display cache advice
-- *********************************************************** column c1 heading 'Cache Size (meg)' format 999,999,999,999
select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3, estd_physical_reads c4from v$db_cache_advicewhere name = 'DEFAULT'and block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')and advice_status = 'ON';The output from the script is shown below. Note that the values range from 10 percent of the current size to double the current size of the db_cache_size.
Estd Phys Estd Phys
Cache Size (meg) Buffers Read Factor Reads
---------------- ------------ ----------- ------------ 30 3,802 18.70 192,317,943 <== 10% size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180 304 38,020 1.00 10,282,475 <== Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122 608 76,040 .66 6,739,731 <== 2x size
From the above listing we see that increasing the db_cache_size from 304 meg to 334 meg would result in approximately 700,000 less physical reads. This can be plotted as a 1/x function and the exact optimal point computed as the second derivative of the function:
The Buffer Cache Hit Ratio Oracle metric monitors the rate at which Oracle finds the data blocks it needs in memory over the lifetime of an instance.
"many DBAs do their best to get a 99% or better hit ratio, but quickly discover that the performance of their database isn't improving as the hit ratio gets better," therefore, sometimes these statistics can be misleading. However, you can try, "the Oracle Wait Interface (OWI)" for better tuning.
we can use this query
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;
to learn more about the buffer pool hit ratios.
you have
the default pool
the keep pool
and the recycle pool
When people refer to the buffer cache they usually refer to the default pool
KEEP pool - typically used for objects you want to keep permanently cached. After a warm-up period you hope that every access to an object in this pool is met from the buffer, so the KEEP hit ratio should be 100% if you're using it as you expect.
RECYCLE pool - typically used for objects that are such a nuisance that you can't hope to get any reasonable caching effect for them, but they still knock something out of memory when you read the blocks. You expect the RECYCLE hit ratio to 0% - if you're using it "properly".
DEFAULT pool - whatever you think your hit ratio should be for this pool, the figure is going to be clouded if you sum in the buffer gets and physical blocks read for the other buffer pools.
Oracle Library Cache Hit Ratio
The Library Cache Hit Ratio Oracle metric monitors the percentage of entries in the library cache that were parsed more than once (reloads) over the lifetime of the instance.
Since you never know in-advance how many SQL statements need to be cached, the Oracle DBA must set shared_pool_size large enough to prevent excessive re-parsing of SQL.
the library cache hit ratio and error code ORA-0403.
It states that adjusting the shared pool size may help avoid this error.
To do this, we evaluate the library cache hit ratio metric as such; "The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:
SELECT
SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM
V$LIBRARYCACHE;
If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size. "
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
