← Performance
Oracle · Performance
How to check DB buffer cache advice in Oracle
Predict how changes to the Oracle buffer cache size will affect physical reads, from V$DB_CACHE_ADVICE.
Tested on11g12c18c19c21c23ai26ai
V$DB_CACHE_ADVICE predicts the physical read impact of different DEFAULT buffer cache sizes for the current block size. Read factor 1.0 = current; anything below is potential improvement.
COLUMN size_for_estimate FORMAT 999,999,999,999 HEADING 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 HEADING 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 HEADING 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999,999 HEADING 'Estd Phys| Reads'
SELECT size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
AND advice_status = 'ON';performancememorybuffer cachev$db_cache_advice