Buffer cache advisor

-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/07/27
-- # Name: db_cache_advisor.sql
--  ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # Display database buffer cache advisor: useful 
-- # to know the good size of the buffer cache
-- #
-- ################################################

col size_est   format 999,999,999,999 heading 'Cache Size (m)'
col buf_est    format 999,999,999     heading 'Buffers'
col estd_rf    format 999.90          heading 'Estd Phys|Read Factor'
column estd_pr format 999,999,999     heading 'Estd Phys| Reads'

SET LINES 80 PAGES 100
SELECT
   size_for_estimate size_est,
   buffers_for_estimate buf_est,
   estd_physical_read_factor est_rf,
   estd_physical_reads est_pr
 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';

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.