Buffer Cache Analysis
SELECT
  size_for_estimate, buffers_for_estimate,
  estd_physical_read_factor,
  estd_physical_reads
FROM v$db_cache_advice
WHERE block_size = (SELECT value 
                    FROM  v$parameter  
                    WHERE name = 'db_block_size')
AND name = 'DEFAULT'
AND advice_status = 'ON'
;

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, 
       estd_physical_reads
  FROM v$db_cache_advice
 WHERE block_size = (SELECT value 
                    FROM  v$parameter  
                    WHERE name = 'db_block_size')
AND name = 'KEEP'
AND advice_status = 'ON'
;

SELECT name, value
FROM v$sysstat 
WHERE name IN (
 'db block gets from cache',
 'consistent gets from cache', 
 'physical reads cache'
);

-- Buffer Hit Ratio

SELECT
 1-(pr.value/(cg.value+dg.value)) buffer_hit_ratio 
FROM v$sysstat pr,
     v$sysstat cg,
     v$sysstat dg
WHERE pr.name = 'physical reads cache'
AND   cg.name = 'consistent gets from cache'
AND   dg.name = 'db block gets from cache'
;


SELECT name, physical_reads, db_block_gets, consistent_gets,
       1 - (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio"
FROM v$buffer_pool_statistics
;

SELECT o.object_name, COUNT(*) number_of_blocks
FROM dba_objects o, v$bh bh
WHERE o.data_object_id = bh.objd
AND o.owner != 'SYS'
GROUP BY o.object_Name
ORDER BY COUNT(*)
;

CPU Analysis Queries

Library Cache Queries

Oracle 12c Documentation: Tune Buffer Cache

Oracle registered trademark of Oracle Corporation.

Last Revised On: May 31st, 2014

  103