Doc  

        Name:   bufcache.sql

        Author: Mark gurry

        This script gathers various statistics to assist you with your buffer cache tuning.


#


break on name skip 1;
column name format a32;
set pagesize 9999
spool bufcache.lis

ttitle 'System Global Area Breakdown'

select *      
  from v$sgastat
/


ttitle ' The Hit Ratio      '

select 
       sum(decode(name, 'consistent gets',value, 0))  "Consis Gets",
        sum(decode(name, 'db block gets',value, 0))  "DB Blk Gets",
        sum(decode(name, 'physical reads',value, 0))  "Phys Reads",
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  -
        sum(decode(name, 'physical reads',value, 0)))
		       /
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  )  * 100 "Hit Ratio" 
from v$sysstat st
/

ttitle ' User Hit Ratios with Hit Ratio < 80%' 
column nl newline;
column "Hit Ratio" format 999.99
column  "User Session" format a15;

select  se.username||'('|| se.sid||')' "User Session",
       sum(decode(name, 'consistent gets',value, 0))  "Consis Gets",
        sum(decode(name, 'db block gets',value, 0))  "DB Blk Gets",
        sum(decode(name, 'physical reads',value, 0))  "Phys Reads",
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  -
        sum(decode(name, 'physical reads',value, 0)))
		       /
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  )  * 100 "Hit Ratio" 
  from  v$sesstat ss, v$statname sn, v$session se
where   ss.sid    = se.sid
  and   sn.statistic# = ss.statistic#
  and   value != 0
  and   sn.name in ('db block gets', 'consistent gets', 'physical reads')
group by se.username, se.sid
having 
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  -
        sum(decode(name, 'physical reads',value, 0)))
		       /
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  )  * 100 
  <   80
;

ttitle 'The Average Length of the Write Request Queue'

column  "Write Request Length" format 999,999.99

select sum( decode (name, 'summed dirty queue length', value))
               /
       sum( decode (name, 'write requests', value)) "Write Request Length"
  from v$sysstat
 where  name in ( 'summed dirty queue length'
                 ,'write requests')
   and  value > 0
/

ttitle ' Breakdown of Buffer Cache By File '

select name, decode(b.status,'CR', 'Modified', 'XCUR', 'Not Modified', b.status) , count(*) 
  from v$datafile a,  v$bh b 
 where a.file#  = b.file#
 group by name, b.status
/
column "Status" format a12;
  
ttitle 'Tables/Indexes/Rollbacks Currently in Buffer Cache' 

select name, decode(status,'CR', 'Modified', 'XCUR', 'Not Modified',    status) "Status", count(*)
  from v$cache
 group by name, decode(status,'CR', 'Modified', 'XCUR', 'Not Modified', status)
/
spool off