REM Script created by Tim Onions, June 1998
REM You are free to use this script (although no guarentees are made or liability accepted from the author)
REM on one condition - use it to improve the performance of your database!
set newpage 0
set feedback off
ttitle "Rollback segment statistics"
btitle off;
column name heading "Rollback |Segment" format a12;
column gets format 999999990 heading Gets
column writes format 99999999990 heading 'Writes'
column waits format 99990 heading Waits
column hwmsize format 9999990 heading 'High|Water|(KB)'
column optimal format 9999990 heading 'Optimal|(KB)'
column shrinks format 9990 heading 'No. of|Shrinks'
column aveshrink format 99990 heading 'Avg Shrink|(KB)'
column aveactive format 9999990 heading 'Avg Size|Active|(KB)'
column rssize format 9999990 heading 'Current|Size|(KB)'
column extents format 999 heading 'Extents'
column xacts format 99999 heading 'Trans|actions'
column wraps format 9,999 heading "Wraps"
select n.name name ,
r.hwmsize/1024 hwmsize,
r.optsize/1024 optimal,
r.aveactive/1024 aveactive ,
r.shrinks Shrinks,
r.aveshrink/1024 aveshrink ,
r.rssize/1024 rssize ,
r.extents,
r.gets,
r.writes writes,
r.waits,
r.xacts,
r.wraps
from v$rollstat r,
v$rollname n
where n.usn=r.usn
/
prompt .
prompt For equally sized rollback segments, OPTIMAL should be slightly larger
prompt than the average size.
prompt
prompt Shrinks Average Analysis and recommendations
prompt . Sizes
prompt . Shrunk
prompt ------- ------- -----------------------------------------------------;
prompt Low Low If average sizes active is close to Sizes, Optimal
prompt . then the OPTIMAL setting is correct. Otherwise,
prompt . Optimal is too large.
prompt
prompt Low High Excellent - A good setting for OPTIMAL
prompt
prompt High Low OPTIMAL is too small - too many shrinks are being
prompt . performed
prompt
prompt High High Periodic long transcations are probably causing these
prompt . statistics. Set the OPTIMAL parameter higher until
prompt . Shrinks is lower.
prompt .
column wait_pct heading "waits/writes %" format 999.9999
column wait_get_pct heading "waits/gets %" format 999.9999
select n.name,r.gets,r.writes,r.waits,
(r.waits/decode(r.writes,0,1,r.writes))*100 wait_pct,
(r.waits/decode(r.gets,0,1,r.gets))*100 wait_get_pct
from v$rollstat r,
v$rollname n
where n.usn=r.usn
/
prompt
prompt If the ratio waits/gets > 5%, add more rollback.
prompt
col name format a12 heading "Roll|Segm|Name"
col waits format 9999 heading "Wait"
col pct_wait format 999 heading "%|Wait"
col gets format a5 heading "Gets"
col writes format a5 heading "Write"
col v1 format 999 heading 'Mb'
col v2 format 999 heading 'Opt|Mb'
col v3 format 999 heading 'Hi|Wtr|Mb'
col shrinks format 999 heading '#|Shr|ink'
col extends format 999 heading '#|Ext|end'
col aveactive format a5 heading 'Avgsz|Activ'
col extents format 999 heading '#|Ext'
col xacts format 999 heading '#|Trn'
col wraps format 99,990 heading "Wraps"
col aveshrink format 999,999,999 heading "Average|Shrink"
set heading on
select name,
waits,
floor(100 * waits / gets) pct_wait,
decode(sign(9999999-gets), -1, lpad(trunc(gets / 1000000), 3)||' M',
decode(sign(9999-gets), -1, lpad(trunc(gets / 1000), 3)||' k',
lpad(gets, 5))) gets,
decode(sign(9999999-writes), -1, lpad(trunc(writes / 1000000), 3)||' M',
decode(sign(9999-writes),-1,lpad(trunc(writes / 1000), 3)||' k',
lpad(writes, 5))) writes,
rssize / 1048576 v1,
optsize /1048576 v2,
hwmsize / 1048576 v3,
shrinks,
extends,
decode(sign(9999999-aveactive), -1,
lpad(trunc(aveactive / 1000000), 3)||' M',
decode(sign(9999-aveactive), -1,
lpad(trunc(aveactive / 1000), 3)||' k',
lpad(aveactive, 5))) aveactive,
extents,
xacts,
wraps,
aveshrink
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn;
col name format a6 heading "Roll|Segm|Name"
col tablespace_name format a6 heading "Tablsp"
col status format a16 heading "Status"
set heading on
select name,
tablespace_name,
v$rollstat.status status
from v$rollstat, v$rollname, dba_rollback_segs
where v$rollstat.usn = v$rollname.usn and name = segment_name and
v$rollstat.status <> 'ONLINE';
set heading off
select 'If # Shrink is low:'||chr(10)||
' If AvShr is low:'||chr(10)||
' If Avgsz Activ is much smaller than Opt Mb:'||chr(10)||
' Reduce OPTIMAL (since not many shrinks occur).'||chr(10)||
' If AvShr is high:'||chr(10)||
' Good value for OPTIMAL.'||chr(10)||
'If # Shrink is high:'||chr(10)||
' If AvShr is low:'||chr(10)||
' Too many shrinks being performed, since OPTIMAL is'||chr(10)||
' somewhat (but not hugely) too small.'||chr(10)||
' If AvShr is high:'||chr(10)||
' Increase OPTIMAL until # of Shrnk decreases. Periodic'||chr(10)||
' long transactions are probably causing this.'||chr(10)||chr(10)||
'A high value in the #Ext column indicates dynamic extension, in'||chr(10)||
'which case you should consider increasing your rollback segment'||chr(10)||
'size. (Also, increase it if you get a "Shapshot too old" error).'||
chr(10)||chr(10)||
'A high value in the # Extend and # Shrink columns indicate'||chr(10)||
'allocation and deallocation of extents, due to rollback segments'||
chr(10)||'with a smaller optimal size. It also may be due to a batch'||chr(10)
||'processing transaction assigned to a smaller rollback segment.'||chr(10)
||'Consider increasing OPTIMAL.'
from dual;
column so noprint
select 1 so, 'Rollback contention for system undo header = '||
(round(max(decode(class, 'system undo header', count, 0)) /
(sum(count)+0.00000000001),4))*100||'%'||
' (Total requests = '||sum(count)||')'
from v$waitstat
union
select 2 so, 'Rollback contention for system undo block = '||
(round(max(decode(class, 'system undo block', count, 0)) /
(sum(count)+0.00000000001),4))*100||'%'||
' (Total requests = '||sum(count)||')'
from v$waitstat
union
select 3 so, 'Rollback contention for undo header = '||
(round(max(decode(class, 'undo header', count, 0)) /
(sum(count)+0.00000000001),4))*100||'%'||
' (Total requests = '||sum(count)||')'
from v$waitstat
union
select 4 so, 'Rollback contention for undo block = '||
(round(max(decode(class, 'undo block', count, 0)) /
(sum(count)+0.00000000001),4))*100||'%'||
' (Total requests = '||sum(count)||')'
from v$waitstat
union
select 5 so, chr(10)||'NB If percentage is more than 1%, create more rollback segments'
from dual
order by 1;
Prompt
clear columns
set head on pages 40
