set showmode off echo off
set pagesize 24 heading on feedback 6 timing off linesize 80 termout on
rem
rem Script: removing_buffers.sql (must be run from user "sys")
rem Purpose: Compute resulting Cache Hit Ratio for a decrease in the
rem    buffer cache size of the given number of buffer blocks.  If the
rem    ratio is still more than 80% after "removing" those buffers, you
rem    may want to decrease db_block_buffers (in your $ORACLE_HOME/dbs/
rem    initSID.ora file) by that number of buffer blocks, but only if
rem    you are running short of memory and really need that additional
rem    freed-up memory for something else.
rem
rem Note: Before running this, you must edit your initSID.ora file to
rem    add "db_block_lru_statistics = true", then shutdown and startup
rem    your database to begin gathering statistics, and let it run for a
rem    few hours at least to gather a representative sample during the
rem    time after the startup.  Remove that line after analyzing the
rem    results and do another shutdown/startup to stop the statistics
rem    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 remove to compute the new Cache Hit
rem Ratio for, and get the number of additional cache misses gathered
rem for that decrease in buffers.
rem
set heading off
select 'Max Buffers Tested To Remove = ' || nvl(max(indx)+1,0) from x$kcbcbh;
accept less_buffers number prompt "Enter Trial Value For Buffers To Remove From db_block_buffers: "
set verify off termout off
spool removing_buffers1.sql
select 'define acm = ' || nvl(sum(count),0)
  from x$kcbcbh where indx >=
    (select nvl(max(indx)+1,0) - &less_buffers from x$kcbcbh);
spool off
@removing_buffers1.sql
!rm removing_buffers1.sql
set heading on termout on
rem
rem Compute the resulting Cache Hit Ratio for that decreased buffer
rem cache size for the sample time.
rem
select pr.value "1 - (Physical Reads", &acm "+ Addl Cache Misses)",
    dbg.value "/ (DB Block Gets", cg.value "+ Consistent Gets) =",
    (1-(pr.value+(&acm))/(dbg.value+cg.value))*100 "New Cache Hit Ratio",
    decode(sign((1-pr.value/(dbg.value+cg.value))*100-80),-1,'Original Low',
      decode(sign((1-(pr.value+(&acm))/(dbg.value+cg.value))*100-80),
        -1,'Too Low Now ','Remove 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 Saved By Removal = ' ||
  trunc(&less_buffers * value / 1024)
  from v$parameter where name = 'db_block_size';
set heading on
undefine acm
undefine less_buffers
set timing on verify on echo on showmode both