-- #############################################################################################
--
-- Autor:    Martin Zahn Akadia AG
--
-- %Purpose: Tuning von Rollback-Segmenten
--
-- #############################################################################################

1). Size and Number of Waits per Rollback-Segment
-------------------------------------------------
SELECT SUBSTR(rsn.name,1,10) "Name",
       rss.rssize "Tot-Size [Bytes]",
       rss.extents "Extents",
       ROUND(rss.rssize/rss.extents) "RS-Size [Bytes]",
       waits "Number Waits" 
  FROM v$rollstat rss, v$rollname rsn
 WHERE rss.usn = rsn.usn
 ORDER BY rsn.name;

Old Tuning Session: (Rollback Segments with 400 KB Size)

Name       Tot-Size [Bytes]    Extents RS-Size [Bytes] Number Waits
---------- ---------------- ---------- --------------- ------------
RBS01               8597504         21          409405         1616
RBS02               8597504         21          409405         4125
RBS03               8597504         21          409405         3992
RBS04               8597504         21          409405         4174
RBS05               8597504         21          409405         3617
RBS06               8597504         21          409405         3843
RBS07               8597504         21          409405         3715
RBS08               8597504         21          409405         3730
RBS09               8597504         21          409405        25699
RBS10               8597504         21          409405         3635

New Tuning Session: (Rollback Segments with 5 MB Size)

Name       Tot-Size [Bytes]    Extents RS-Size [Bytes] Number Waits
---------- ---------------- ---------- --------------- ------------
RBS01             104853504         20         5242675         1715
RBS02             104853504         20         5242675         1835
RBS03             104853504         20         5242675         1338
RBS04             104853504         20         5242675         1499
RBS05             104853504         20         5242675         1572
RBS06             104853504         20         5242675         1628
RBS07             104853504         20         5242675         1533
RBS08             104853504         20         5242675         1689
RBS09             104853504         20         5242675         1461
RBS10             104853504         20         5242675         1663

2). Rollback Contention
-----------------------
SELECT name,gets,waits,
       to_char(((gets-waits)*100)/gets,'999.9999') hit_ratio
  FROM v$rollstat S, v$rollname R
 WHERE S.usn = R.usn
ORDER BY R.name;

Old Tuning Session: (Rollback Segments with 400 KB Size)

NAME                                 GETS      WAITS HIT_RATIO
------------------------------ ---------- ---------- ---------
RBS01                             5314092       1643   99.9691
RBS02                            10363748       4157   99.9599
RBS03                            10459920       4017   99.9616
RBS04                            10962299       4184   99.9618
RBS05                             9469712       3649   99.9615
RBS06                            10218019       3889   99.9619
RBS07                             9796463       3736   99.9619
RBS08                             9900727       3739   99.9622
RBS09                            13130819      25721   99.8041
RBS10                             9456272       3673   99.9612

New Tuning Session: (Rollback Segments with 5 MB Size)

NAME                                 GETS      WAITS HIT_RATIO
------------------------------ ---------- ---------- ---------
RBS01                             5837671       1719   99.9706
RBS02                             6151758       1835   99.9702
RBS03                             5451355       1338   99.9755
RBS04                             5105157       1499   99.9706
RBS05                             5333881       1574   99.9705
RBS06                             6070279       1631   99.9731
RBS07                             5611779       1533   99.9727
RBS08                             6097782       1692   99.9723
RBS09                             5558601       1462   99.9737
RBS10                             6418860       1663   99.9741

3). Compare rollback segment waits with total number of gets
------------------------------------------------------------
Rollback segment waits

SELECT v.class, v.count
  FROM v$waitstat v
 WHERE class IN ('system undo header','system undo block',
                      'undo header','undo block');
                      
Total number of gets

SELECT TO_CHAR(sum(value),'999,999,999,999') "Total Gets"
  FROM v$sysstat 
 WHERE name in ('db block gets','consistent gets');

Old Tuning Session: (Much waits compared with total Gets)

CLASS                   COUNT
------------------ ----------
system undo header          0
system undo block           0
undo header             74880
undo block              21618

Total Gets
----------------
   8,896,140,872

New Tuning Session: (Much waits compared with total Gets, not better)

CLASS                   COUNT
------------------ ----------
system undo header          0
system undo block           0
undo header             24138
undo block              28146

Total Gets
----------------
   4,152,462,130


4). Overall System-Statistics for ALL Rollback-Segments
-------------------------------------------------------
SELECT statistic#,SUBSTR(name,1,50) "Name",
       class,value
  FROM v$sysstat  
  WHERE name in ('user rollbacks',
                 'rollback changes - undo records applied',
                 'transaction rollbacks');

Old Tuning Session

STATISTIC# Name                                                    CLASS        VALUE
---------- -------------------------------------------------- ---------- ------------
         5 user rollbacks                                              1           30
       113 rollback changes - undo records applied                   128    2'524'864
       114 transaction rollbacks                                     128       29'131

A low number of rollbacks initiate a high nunber of waits !

New Tuning Session

STATISTIC# Name                                                    CLASS      VALUE
---------- -------------------------------------------------- ---------- ----------
         5 user rollbacks                                              1         51
       113 rollback changes - undo records applied                   128     111886
       114 transaction rollbacks                                     128      13649

5). Who uses the Rollback Segments
----------------------------------
SELECT r.usn,SUBSTR(r.name,1,10) "Name",s.osuser, 
       SUBSTR(s.username,1,15) "User",s.sid,x.extents, 
       x.extends,x.waits,x.shrinks,
       x.wraps
FROM   sys.v_$rollstat X,
       sys.v_$rollname R,  
       sys.v_$session S,
       sys.v_$transaction T
WHERE  t.addr       = s.taddr (+)
AND    x.usn (+)    = r.usn
AND    t.xidusn (+) = r.usn
ORDER BY r.usn;

Old Tuning Session

       USN Name       OSUSER          User                   SID    EXTENTS    EXTENDS      WAITS    SHRINKS      WRAPS
---------- ---------- --------------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
         0 SYSTEM                                                         4          0          0          0          0
         2 RBS01      poseidon        VP_LINK                 55         21        321       1703         34       3448
         3 RBS02      ota             DISPATCHER              35         21        370       4175         42       6589
         4 RBS03      poseidon        VP_LINK                 59         21        445       4074         53       6981
         5 RBS04      poseidon        VP_LINK                 56         21        262       4252         33       7073
         6 RBS05      bscs            SMH_LINK                76         21        195       3772         22       5847
         7 RBS06                                                         21        291       4044         34       6665
         8 RBS07                                                         21        259       3803         31       6336
         9 RBS08      poseidon        VP_LINK                 57         21        529       3820         64       6789
        10 RBS09      poseidon        VP_LINK                 54         21        599      25839         69       8153
        11 RBS10                                                         21        398       3772         45       5878

New Tuning Session

       USN Name       OSUSER          User                   SID    EXTENTS    EXTENDS      WAITS    SHRINKS      WRAPS
---------- ---------- --------------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
         0 SYSTEM                                                         4          0          0          0          1
         1 RBS10                                                         20          0       1681          0        299
         2 RBS01                                                         20          0       1731          0        277
         3 RBS02      poseidon        VP_LINK                 93         20          0       1850          0        288
         4 RBS03      poseidon        VP_LINK                 92         20          0       1338          0        248
         5 RBS04      poseidon        VP_LINK                 86         20          0       1499          0        232
         6 RBS05      bscs            SMH_LINK                94         20          0       1583          0        258
         7 RBS06      bscs            SMH_LINK               113         20          0       1645          0        282
         8 RBS07      poseidon        VP_LINK                 70         20          0       1533          0        260
         9 RBS08                                                         20          0       1694          0        274
        10 RBS09                                                         20          0       1483          0        264

No more extends and shrinks.