modified on 22 January 2010 at 11:21 ••• 357 views

Db cache advice

From Oramosswiki

Jump to: navigation, search
--
--  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