rem *  **************************************************** *
rem *  name: c:\oracle\sql\cache_01.sql ........            *
rem *                              gather cache statistics  *
rem *  owner: R.Kupcunas                                    *
rem *  date:    30-APR-95                                   *
rem *  **************************************************** *

spool cache_01.rslt

set wrap on

set feedback off
set pagesize 60
set heading on
set echo off
set verify off
set pause off
set newpage 0
set term on
set recsep off
set linesize 100;

/* ****************************** */
/* "Cache Statistics"             */
/* ****************************** */

REM define desired_usage_percent=$DESIRED_USAGE_PERCENT
REM define desired_gets_percent=$DESIRED_GETS_PERCENT

ttitle center 'Action is based on maintaining > ' high '% entries used' -
    skip 1 -
    center 'and < ' low '% gets missed'

col parameter heading 'Parameter Name'    format a20   just cent
col count     heading 'Entries|Allocated' format 9,999,990
col usage     heading 'Entries|Used'      format 9,999,990
col gets      heading 'Gets'              format 99,999,990
col getmisses heading 'Get|Misses'        format 9,999,990
col pctused   heading 'Pct|Used'          format 990.0
col pctmisses heading 'Pct|Misses'        format 990.0
col action    heading 'Action'            format a6

select parameter,
       count,
       usage,
       100*nvl(usage, 0)/decode(count,null,1,0,1,count) pctused,
       gets,
       getmisses,
       100*nvl(getmisses, 0)/decode(gets,null,1,0,1,gets) pctmisses,
       decode(greatest(100*nvl(gets, 0)/decode(nvl(gets,0) +
             nvl(getmisses,0), null,1,1), 
             &&desired_gets_percent), &&desired_gets_percent, 'Raise',
       decode(greatest(100*nvl(usage, 0)/decode(count,null,1,0,1,count),
             &&desired_usage_percent), &&desired_usage_percent, 'Lower',
             'OK')) action
  from v$rowcache
  order by 1;

spool off;

undefine desired_gets_percent;
undefine desired_usage_percent;
