SQL> @rollback_stat
Tue Mar 01 page 1
Rollback segment statistics
High Avg Size Current
Rollback Water Optimal Active No. of Avg Shrink Size Trans
Segment (KB) (KB) (KB) Shrinks (KB) (KB) Extents Gets Writes Waits actions Wraps
------------ -------- -------- -------- -------- ---------- -------- ------- ---------- ------------ ------ ------- ------
SYSTEM 10296 2048 0 0 0 10296 2 2069 4140 0 0 0
RBS01 40956 20480 10800 3 13653 30716 3 722576 192347558 12 0 24
RBS02 30716 20480 9183 1 10240 20476 2 807317 179716658 9 0 20
RBS03 276476 20480 56130 4 66560 30716 3 935309 325989570 43 0 47
RBS04 40956 20480 10498 3 10240 20476 2 783926 192091996 12 0 24
RBS05 40956 20480 10449 3 13653 20476 2 1476253 323218588 10 1 39
RBS06 40956 20480 11460 2 15360 30716 3 726903 186978590 10 0 22
RBS07 40956 20480 13091 2 10240 40956 4 825424 183840416 14 0 22
RBS08 286716 20480 66667 4 71680 20476 2 910731 295174048 37 0 42
RBS09 40956 20480 12558 4 15360 20476 2 749028 201418940 6 1 25
.
For equally sized rollback segments, OPTIMAL should be slightly larger
than the average size.
Shrinks Average Analysis and recommendations
. Sizes
. Shrunk
------- ------- ------------------------------------------------------
Low Low If average sizes active is close to Sizes, Optimal
. then the OPTIMAL setting is correct. Otherwise,
. Optimal is too large.
Low High Excellent - A good setting for OPTIMAL
High Low OPTIMAL is too small - too many shrinks are being
. performed
High High Periodic long transcations are probably causing these
. statistics. Set the OPTIMAL parameter higher until
. Shrinks is lower.
.
Tue Mar 01 page 1
Rollback segment statistics
Rollback
Segment Gets Writes Waits waits/writes % waits/gets %
------------ ---------- ------------ ------ -------------- ------------
SYSTEM 2071 4140 0 .0000 .0000
RBS01 722578 192347558 12 .0000 .0017
RBS02 807319 179716658 9 .0000 .0011
RBS03 935311 325989570 43 .0000 .0046
RBS04 783928 192091996 12 .0000 .0015
RBS05 1476255 323218588 10 .0000 .0007
RBS06 726905 186978590 10 .0000 .0014
RBS07 825426 183840416 14 .0000 .0017
RBS08 910733 295174048 37 .0000 .0041
RBS09 749030 201418940 6 .0000 .0008
If the ratio waits/gets > 5%, add more rollback.
Tue Mar 01 page 1
Rollback segment statistics
Roll Hi # #
Segm % Opt Wtr Shr Ext Avgsz # # Average
Name Wait Wait Gets Write Mb Mb Mb ink end Activ Ext Trn Wraps Shrink
------------ ----- ---- ----- ----- ---- ----- ---- ---- ---- ----- ---- ---- ------- ------------
SYSTEM 0 0 2073 4140 10 2 10 0 0 0 2 0 0 0
RBS01 12 0 722 k 192 M 30 20 40 3 5 11 M 3 0 24 13,981,013
RBS02 9 0 807 k 179 M 20 20 30 1 1 940 k 2 0 20 10,485,760
RBS03 43 0 935 k 325 M 30 20 270 4 27 57 M 3 0 47 68,157,440
RBS04 12 0 783 k 192 M 20 20 40 3 3 10 M 2 0 24 10,485,760
RBS05 10 0 147 k 323 M 20 20 40 3 4 10 M 2 1 39 13,981,013
RBS06 10 0 726 k 186 M 30 20 40 2 4 11 M 3 0 22 15,728,640
RBS07 14 0 825 k 183 M 40 20 40 2 4 13 M 4 0 22 10,485,760
RBS08 37 0 910 k 295 M 20 20 280 4 28 68 M 2 0 42 73,400,320
RBS09 6 0 749 k 201 M 20 20 40 4 6 12 M 2 1 25 15,728,640
Tue Mar 01 page 1
Rollback segment statistics
If # Shrink is low:
If AvShr is low:
If Avgsz Activ is much smaller than Opt Mb:
Reduce OPTIMAL (since not many shrinks occur).
If AvShr is high:
Good value for OPTIMAL.
If # Shrink is high:
If AvShr is low:
Too many shrinks being performed, since OPTIMAL is
somewhat (but not hugely) too small.
If AvShr is high:
Increase OPTIMAL until # of Shrnk decreases. Periodic
long transactions are probably causing this.
A high value in the #Ext column indicates dynamic extension, in
which case you should consider increasing your rollback segment
size. (Also, increase it if you get a "Shapshot too old" error).
A high value in the # Extend and # Shrink columns indicate
allocation and deallocation of extents, due to rollback segments
with a smaller optimal size. It also may be due to a batch
processing transaction assigned to a smaller rollback segment.
Consider increasing OPTIMAL.
Tue Mar 01 page 1
Rollback segment statistics
Rollback contention for system undo header = 0% (Total requests = 14477)
Rollback contention for system undo block = 0% (Total requests = 14477)
Rollback contention for undo header = 1.58% (Total requests = 14477)
Rollback contention for undo block = .66% (Total requests = 14477)
NB If percentage is more than 1%, create more rollback segments
