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