set showmode off echo off
set pagesize 24 heading on feedback 6 timing off linesize 80 termout on
rem
rem Script: adding_buffers.sql (must be run from user "sys")
rem Purpose: Compute resulting Cache Hit Ratio for an increase in the
rem    buffer cache size of the given number of buffer blocks.  If the
rem    ratio was previously less than 80%, but is now above that amount
rem    after "adding" those buffers, you should increase db_block_buffers
rem    (in your $ORACLE_HOME/dbs/initSID.ora file) by that number of
rem    buffer blocks.
rem
rem Note: Before running this, you must edit your initSID.ora file to
rem    add "db_block_lru_extended_statistics = 1000" (or, whatever other
rem    maximum buffer increase you want to gather statistics for), then
rem    shutdown and startup your database to begin gathering statistics,
rem    and let it run for a few hours at least to gather a representative
rem    sample during the time after the startup.  Remove that line after
rem    analyzing the results and do another shutdown/startup to stop the
rem    statistics gathering, which tends to slow down transactions.
rem
rem Author: Stephen Rea
rem Released: 9/30/98
rem
rem Ask for the number of buffers to add to compute the new Cache Hit
rem Ratio for, and get the number of additional cache hits gathered
rem for that increase in buffers.
rem
set heading off
select 'Maximum Buffers Tested To Add = ' || nvl(max(indx)+1,0) from x$kcbrbh;
accept more_buffers number prompt "Enter Trial Value For Buffers To Add To db_block_buffers: "
set verify off termout off
spool adding_buffers1.sql
select 'define ach = ' || nvl(sum(count),0)
  from x$kcbrbh where indx < &more_buffers;
spool off
@adding_buffers1.sql
!rm adding_buffers1.sql
set heading on termout on
rem
rem Compute the resulting Cache Hit Ratio for that increased buffer
rem cache size for the sample time.
rem
select pr.value "1 - (Physical Reads", &ach "- Addl Cache Hits)",
    dbg.value "/ (DB Block Gets", cg.value "+ Consistent Gets) =",
    (1-(pr.value-(&ach))/(dbg.value+cg.value))*100 "New Cache Hit Ratio",
    decode(sign((1-pr.value/(dbg.value+cg.value))*100-80),1,'Original OK',
      decode(sign((1-(pr.value-(&ach))/(dbg.value+cg.value))*100-80),
        -1,' Still Low ',' Add Them? ')) "  Results  "
  from v$sysstat dbg, v$sysstat cg, v$sysstat pr
  where dbg.name = 'db block gets' and cg.name = 'consistent gets'
    and pr.name = 'physical reads';
set heading off
select 'KBytes That Would Be Used By Addition = ' ||
  trunc(&more_buffers * value / 1024)
  from v$parameter where name = 'db_block_size';
set heading on
undefine ach
undefine more_buffers
set timing on verify on echo on showmode both