/* ************************************************ */
/*                                                  */
/* format the report:                               */
/*        DCSTATS - Dictionary Cache Stats          */
/*                                                  */
/* report dictionary cache parameter effectiveness  */
/*                                                  */
/* name:    @c:\oracle\sql\dcstats.sql              */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

set feedback off
set linesize  78
set newpage    0
set pagesize  60
set trimout   on
set verify    on
set underline =
set echo      off
set termout   off

whenever sqlerror exit failure 
spool dcstats.lst

ttitle center 'DCSTATS - Dictionary Cache Stats  ' right CWS_DATE skip 2

col parameter heading 'Parameter name'    format a20       justify c trunc
col count     heading 'Entries|Allocated' format 9999990   justify c
col usage     heading 'Entries|Used'      format 9999990   justify c
col gets      heading 'Gets'              format 9999990   justify c
col getmisses heading 'Get|Misses'        format 9999990   justify c
col pctused   heading 'Pct|Used'          format     990.0 justify c
col pctmisses heading 'Pct|Misses'        format     990.0 justify c
col action    heading 'Rec''d|Action'     format a6        justify c
 
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(usage,0)/decode(count,null,1,0,1,count),80),
    80, 'Lower',
    decode(least(100*nvl(getmisses,0)/decode(gets,null,1,0,1,gets),10),
    10, 'Raise', 'OK')
  ) action
from
  v$rowcache
order by
  1;
 
prompt
prompt
prompt Rec'd Action is based on maintaining >80% Entries Used and <10% Gets Miss
 
clear col
clear bre
set feedback on
spool off;
