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';

Library cache hit ratio

 
-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/10
-- # Name: Library cache hit ratio
-- ############################################################
-- #
-- # Compatible: Oracle 7i 8i 9i 10g 11g
-- #
-- ############################################################
-- #
-- # How to calculate Library cache hit ratio
-- # Other ratios see: "Quick tune"
-- # 
-- ############################################################

Calculate the cache hit ratio for the library cache with the following query:

    Select sum(pinhits) / sum(pins) "Hit Ratio",
        sum(reloads) / sum(pins) "Reload percent"
    From v$librarycache
    Where namespace in
    ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

-- The hit ratio should be above 85 percent. 
-- The reload percent should be very low, 2% or less. 
-- If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. 
-- OPEN_CURSORS may also need to increased.

PGA Target advice histogram

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2010/01/01
-- # Name: pga_target_advice_histogram.sql
--  ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # First statement: Display pga target advice histogram
-- # Second statement: Display pga target advice (%cache hit)
-- #
-- ################################################
 
SELECT
   low_optimal_size/1024 "Low(K)",
   (high_optimal_size+1)/1024 "High(K)",
   estd_optimal_executions "Optimal",
   estd_onepass_executions "One Pass",
   estd_multipasses_executions "Multi-Pass"
FROM
   v$pga_target_advice_histogram
WHERE
   pga_target_factor = 2
AND
   estd_total_executions != 0
ORDER BY
   1;

column c1     heading 'Target(M)'
column c2     heading 'Estimated|Cache Hit %'
column c3     heading 'Estimated|Over-Alloc.'
 
SELECT
   ROUND(pga_target_for_estimate /(1024*1024)) c1,
   estd_pga_cache_hit_percentage         c2,
   estd_overalloc_count                  c3
FROM
   v$pga_target_advice;

Quick tune

This script shows the different memory pools of the SGA and some indicative ratios for the Oracle DBA

-- ################################################
-- # Creator: Ph de Saint Aignan
-- # Created: 2002/06/02
-- # Name: quicktune.sql
--  ################################################
-- #
-- # Compatible: Oracle 7 8i 9i 10g 11g
-- #
-- ################################################

set echo off linesize 200 pagesize 100 wrap off timing off doc off

COL "MEMORY TYPE" 		FOR A16 
--HEAD "SGA + RATIOS"
--COL "Total_Mem(Ko)" 	FOR A12 
--COL "Free(Ko)"	        FOR A8
COL "|||"		FOR A3
COL "MEMORY RATIOS"	FOR A28
COL "RATIO %"		FOR A7
COL IDEAL 		FOR A8 ;
compute sum of "Total_Mem K" on report
compute sum of "Free K" on report
break on report ;

select pool "MEMORY TYPE", Total_Mem "ALLOCATION K",  Free_Mem "FREE K", '|||' "|||", 
RUBRIQUE "MEMORY RATIOS", to_char(round(RATIO*100,1),'999.9') "RATIO %", IDEAL
from 
------------------First  subset Construction :
(select rownum0,A.pool, A.Total_Mem  , B.Free_Mem  
from
(select (rownum ) rownum0, A.* from (
   select pool, round(sum(bytes)/1024,0) Total_Mem from v$sgastat
   where pool is not null group by pool 
   UNION 
   select name, round(bytes/1024)  from v$sgastat where pool is null and name !='fixed_sga') A
UNION ALL
select 6,'Sort Area Size' ,round(value/1024,0)  from v$parameter 
where  name in ('sort_area_size') 
UNION ALL
select 7,'Hash Area Size' ,round(value/1024,0)  from v$parameter 
where  name in ('hash_area_size') ) 
 A ,
(select pool, round(bytes/1024,0) Free_Mem from v$sgastat 
        where name = 'free memory'
        UNION ALL
 select 'db_block_buffers',
           (select count(*)   from v$bh  where status='free')* 
           (select (round(value/1024,0))  from v$parameter where name = 'db_block_size')
           from dual )
          B
where A.pool=B.pool(+)  ) SGA ,
------------------2nd  subset Construction :
(select 6 rownum0, 'DATA DICTIONARY CACHE' "RUBRIQUE", 
	sum(getmisses)/sum(gets) "RATIO", ' < 15 %' "IDEAL"
from v$rowcache
UNION ALL
select 3,'SHARED POOL HIT RATIO',sum(pinhits-reloads)/sum(pins),' > 85 %'
from v$librarycache
UNION ALL
select 4 ,'SHARED POOL RELOAD %',sum (reloads)/sum(pins), ' <  2 %'
from v$librarycache
UNION ALL
select 2,'BUFFER CACHE Hit Ratio',
     (1-(sum (decode (name, 'physical reads',value,0))/
    (sum(decode(name,'db block gets',value,0)) +
    (sum(decode(name,'consistent gets',value,0)))))
    ),  ' > 95 %'
from v$sysstat 
UNION ALL
select 1,'BUFFER CACHE MISS RATIO',
((G-F)/(G-F+C+E)),' < 15 %'
from
(select sum(value) C  from v$sysstat where name like '%- consistent read gets') c,
--http://www.ixora.com.au/tips/tuning/cache_miss.htm : Steve Adams
(select value E from v$sysstat where name = 'db block gets') e,
(select value F from v$sysstat where name = 'physical reads direct') f,
(select value G from v$sysstat where name = 'physical reads') g
UNION ALL
select 5, 'LOG BUFFER REQUESTS Ratio', -- '#Redo Space requests/#redo entries'
((req.value * 50)/entries.value), 
' < 0.02%' 
from v$sysstat req, v$sysstat entries
where req.name='redo log space requests' and entries.name ='redo entries'
UNION ALL
select 7,'MEM SORTS/TOTAL SORTS',
mem.value/(mem.value+disk.value),' > 95 %'
from v$sysstat mem, v$sysstat disk
 where mem.name = 'sorts (memory)'
 and disk.name = 'sorts (disk)' 
)  RATIOS
where SGA.rownum0(+) = RATIOS.ROWNUM0
order by SGA.rownum0 asc;

--@defaultenv