Oracle DB Cache Advice Report
This script displays buffer cache advisory information to predict how changes in database buffer cache size may affect physical reads. It queries the V$DB_CACHE_ADVICE view to help DBAs evaluate cache sizing decisions and improve overall database performance.
oraclesqlperformance-tuningv1.0.0
0 stars0 downloads17 views0 comments
By OracleDba • Created
Code
(24 lines)123456789101112131415161718192021222324
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/db_cache_advice.sql
-- Author : Tim Hall
-- Description : Predicts how changes to the buffer cache will affect physical reads.
-- Requirements : Access to the V$ views.
-- Call Syntax : @db_cache_advice
-- Last Modified: 12/02/2004
-- -----------------------------------------------------------------------------------
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';