Db cache advice
From Oramosswiki
-- -- Change History -- -- Date Author Description -- =========== ================= ================================================ -- 22-JAN-2010 Jeff Moss Created -- COLUMN name HEADING "Pool|Name" FORMAT A10 COLUMN block_size HEADING "Block|Size" FORMAT 99,999 COLUMN advice_status HEADING "Advice|Status" FORMAT A3 COLUMN size_for_estimate HEADING "Size For|Estimate" FORMAT 999,999 COLUMN size_factor HEADING "Size|Factor" FORMAT 9.999 --COLUMN buffers_for_estimate HEADING "Pool|Name" FORMAT A10 --COLUMN estd_physical_read_factor HEADING "Estimated Physical|Read Factor" FORMAT A10 COLUMN estd_physical_reads HEADING "Estimated|Physical Reads" FORMAT 999,999,999,990 COLUMN estd_physical_read_time HEADING "Estimated Physical|Read Time" FORMAT 999,999,990 COLUMN estd_pct_of_db_time_for_reads HEADING "Estimated % DB| Time For Reads" FORMAT 999.99 --COLUMN estd_cluster_reads HEADING "Pool|Name" FORMAT A10 --COLUMN estd_cluster_read_time HEADING "Pool|Name" FORMAT A10 SELECT name , block_size , advice_status , size_for_estimate , size_factor --, buffers_for_estimate --, estd_physical_read_factor , estd_physical_reads , estd_physical_read_time , estd_pct_of_db_time_for_reads --, estd_cluster_reads --, estd_cluster_read_time FROM v$db_cache_advice ORDER BY name , block_size , size_factor /
Which gives results like this:
Pool Block Adv Size For Size Estimated Estimated Physical Estimated % DB Name Size Sta Estimate Factor Physical Reads Read Time Time For Reads ---------- ------- --- -------- ------ ---------------- ------------------ --------------- DEFAULT 32,768 ON 1,840 .100 1,009,739,483 4,605,814 8.90 DEFAULT 32,768 ON 3,680 .200 839,807,738 3,334,691 6.40 DEFAULT 32,768 ON 5,520 .300 737,000,099 2,565,669 4.90 DEFAULT 32,768 ON 7,360 .400 675,558,500 2,106,074 4.10 DEFAULT 32,768 ON 9,200 .500 637,350,626 1,820,271 3.50 DEFAULT 32,768 ON 11,040 .600 611,891,250 1,629,830 3.10 DEFAULT 32,768 ON 12,880 .700 595,356,107 1,506,143 2.90 DEFAULT 32,768 ON 14,720 .800 584,277,814 1,423,276 2.70 DEFAULT 32,768 ON 16,560 .900 576,292,722 1,363,546 2.60 DEFAULT 32,768 ON 18,400 1.000 570,147,932 1,317,581 2.50 DEFAULT 32,768 ON 20,240 1.100 565,177,614 1,280,403 2.50 DEFAULT 32,768 ON 22,080 1.200 560,932,869 1,248,651 2.40 DEFAULT 32,768 ON 23,920 1.300 557,123,059 1,220,153 2.30 DEFAULT 32,768 ON 25,760 1.400 553,483,436 1,192,928 2.30 DEFAULT 32,768 ON 27,600 1.500 549,873,766 1,165,927 2.20 DEFAULT 32,768 ON 29,440 1.600 546,160,855 1,138,153 2.20 DEFAULT 32,768 ON 31,280 1.700 542,173,385 1,108,326 2.10 DEFAULT 32,768 ON 33,120 1.800 537,808,130 1,075,673 2.10 DEFAULT 32,768 ON 34,960 1.900 532,989,049 1,039,626 2.00 DEFAULT 32,768 ON 36,800 2.000 527,374,932 997,631 1.90