Question: I have a KEEP pool defined, plus a
db_2k_cache_size, a db_16k_cache_size and a db_32k_cache_size.
I do not see the data buffer hit ratios for multiple data buffer
caches in my AWR report. How do I get the data buffer hit
ratio for multiple data buffers
Answer: The script will display
the data buffer hit ratio for all of the data buffer caches
when using multiple blocksizes and multiple data caches.
select
name,
block_size,
(1-(physical_reads/
decode(db_block_gets+consistent_gets, 0, .001,
db_block_gets+consistent_gets)))*100 buffer_cache_hit_ratio
from
v$buffer_pool_statistics;
The following is a
sample output from this script to display the buffer hit
ratio (BHR) for all 7 data buffer caches. The names of the sized block
buffers remain DEFAULT, and the
db_block_size
column must be selected to differentiate between the
buffers.
The sample output shows all 7 data buffers.
NAME
BLOCK_SIZE CACHE_HIT_RATIO
----------- ---------- ---------------
DEFAULT
32,767 .97
RECYCLE
16,384 .61
KEEP
16,384 1.00
DEFAULT 16,384
.92
DEFAULT
4,096 .99
DEFAULT
8,192 .98
DEFAULT
2,048 .86
