spool x$kcbrbh.rslt 

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;
 
/* ************************************************************************* */
/*                                                                           */
/*               Measure the impact of adding MORE "DB_CLOCK_BUFFERS"        */
/*                                                                           */
/* Script will show the cache hit rate of your database.  To make the buffer */
/*  cache larger increase the iniatialization parameter DB_BLOCK_BUFFERS to  */
/*  a maximium value of 65535.  In order to determine the efficiency of the  */
/*  buffer cache the parameter DB_BLOCK_LRU_EXTENDED_STATISTICS must by set  */
/*  to TRUE; this value will represent the number of statistic rows you wish */
/*  to capture (i.e.: 100 will save 100 rows of data) - THERE IS AN OVERHEAD */
/*  TO THE SYSTEM PERFORMANCE FOR CAPTURING THE STATISTICS.                  */
/*                                                                           */
/*  NAME:   @c:\oracle\sql\x$kcbrbh.sql                                      */ 
/*  OWNER:  R.Kupcunas                                                       */
/*  DATE:   May 2, 1995                                                      */
/*                                                                           */
/* ************************************************************************* */

/* ********************************************* */
/* predict the cache hits by adding more buffers */
/*  - from the values in the database use the    */
/*    following to determine the hit ratio:      */
/*   Hit Ratio = 1 - (physical reads - ACH /     */
/*           (db blocks gets + consistent gets)) */
/* ********************************************* */

select sum(count) ACH
  from sys.x$kcbrbh
  where indx < 100;

/* *********************************************** */
/* Group the additional buffers in large intervals */
/* *********************************************** */

select 200*TRUNC(indx/200)+1||' to '||250*(TRUNC(indx/200)+1) "Interval",
       sum(count) "Buffer Cache Hits"
  from sys.x$kcbrbh
  group by TRUNC(indx/200);
         
set echo off;
spool off;
set long 80;
commit;










