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 * FROM v$buffer_pool_statistics ; 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(*) ;
55809