← 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