52 lines
1.8 KiB
SQL
52 lines
1.8 KiB
SQL
SET ECHO off
|
|
REM NAME: TFSDCEFF.SQL
|
|
REM USAGE:"@path/tfsdceff"
|
|
REM ------------------------------------------------------------------------
|
|
REM REQUIREMENTS:
|
|
REM Should be run as SYS
|
|
REM ------------------------------------------------------------------------
|
|
REM PURPOSE:
|
|
REM Reports dictionary cache parameter effectiveness. It recommends
|
|
REM an action based on maintaining more than 80% usage ofthe available
|
|
REM cache entries while encountering cache misses no more than 10% of
|
|
REM the time.
|
|
REM
|
|
REM ------------------------------------------------------------------------
|
|
REM Main text of script follows:
|
|
|
|
ttitle -
|
|
center 'Dictionary Cache Statistics Report' 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 Missed.
|
|
|