ROLLBACK CONTENTION STATISTICS

To properly configure a system's rollback segments, you must create enough rollback segments, and they must be of a sufficient size. That seems fairly simple, but it is not. You can observe how the rollback segments are being used, and from that determine what needs to be done.

Determining the Number of Rollback Segments

The number of rollback segments should be determined by the number of concurrent transactions in the database. Remember--the fewer transactions per rollback segment, the less contention. A good rule of thumb is to create about one rollback segment for every four concurrent transactions.

Rollback contention occurs when too many transactions try to use the same rollback segment at the same time, and some of them have to wait. You can tell whether you are seeing contention on rollback segments by looking at the dynamic performance table, V$WAITSTAT. Following is the data contained by V$WAITSTAT that is related to rollback segments:

* UNDO HEADER--The number of waits for buffers containing rollback header blocks.
* UNDO BLOCK--The number of waits for buffers containing rollback blocks other than header blocks.
* SYSTEM UNDO HEADER--Same as UNDO HEADER for the SYSTEM rollback segment.
* SYSTEM UNDO BLOCK--Same as UNDO BLOCK for the SYSTEM rollback segment.

The system rollback segment is the original rollback segment that was created when the database was created. This rollback segment is used primarily for special system functions but is sometimes used when no other rollback segment is available. Typically the SYSTEM rollback segment is not used, and you do not need to be concerned about it.

SQL> col name format a15
SQL> col wraps format 99999
SQL> col shrinks format 99999
SQL> col extends format 99999
SQL> set pages 40

SQL> select name, gets, round(waits/gets*100,3) "Pct Waits",writes, wraps, shrinks, extends
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
and v$rollname.name != 'SYSTEM';

NAME GETS Pct Waits WRITES WRAPS SHRINKS EXTENDS
--------------- ---------- ---------- ---------- ------ ------- -------
_SYSSMU1$ 2119454 .002 349971066 524 61 177
_SYSSMU2$ 2159979 .003 366184380 560 70 175
_SYSSMU3$ 2166968 .002 372017462 523 58 173
_SYSSMU4$ 2625707 .003 425201464 627 66 211
_SYSSMU5$ 2511741 .002 437697190 635 69 228
_SYSSMU6$ 2064043 .002 344161682 494 53 159
_SYSSMU7$ 2115427 .003 426560784 621 70 245
_SYSSMU8$ 2539862 .003 381893486 548 57 174
_SYSSMU9$ 2559839 .002 419910174 627 71 222
_SYSSMU10$ 2679432 .002 466469086 643 74 240
_SYSSMU11$ 1285735 .002 201921514 3 0 1
_SYSSMU12$ 1367639 .003 255898414 2 0 0

12 rows selected.

SQL> set head off
SQL> select 'The average of waits/gets is '||
round((sum(waits) / sum(gets)) * 100,2)||'%'
from v$rollstat;

The average of waits/gets is 0%

If the ratio of waits to gets is more than 1% or 2%, consider creating more rollback segments.


Another way to gauge rollback contention is:


SQL> set head on
SQL> select class, count
from v$waitstat
where class in ('system undo header', 'system undo block', 'undo header', 'undo block' );

CLASS COUNT
------------------ ----------
system undo header 2
system undo block 0
undo header 1333
undo block 24

SQL> set head off
SQL> select 'Total requests = '||sum(count) xn1, sum(count) xv1
from v$waitstat 2 ;

Total requests = 117316

column xn1 format 9999999
column xv1 new_value xxv1 noprint

SQL> select 'Contention for system undo header = '||
(round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from v$waitstat
where class = 'system undo header' 2 3 4
5 /
old 2: (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
new 2: (round(count/( 117318+0.00000000001),4)) * 100||'%'

'CONTENTION FOR SYSTEM UNDO HEADER = '
--------------------------------------------------------------------------------
Contention for system undo header = 0%

SQL> select 'Contention for system undo block = '||
(round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from v$waitstat
where class = 'system undo block' 2 3 4
5 /
old 2: (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
new 2: (round(count/( 117318+0.00000000001),4)) * 100||'%'

'CONTENTION FOR SYSTEM UNDO BLOCK
--------------------------------------------------------------------------------
Contention for system undo block = 0%

SQL> select 'Contention for undo header = '||
(round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from v$waitstat
where class = 'undo header' 2 3 4 ;
old 2: (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
new 2: (round(count/( 117318+0.00000000001),4)) * 100||'%'

'CONTENTION FOR UNDO HEADER =
--------------------------------------------------------------------------------
Contention for undo header = 1.14%

SQL> select 'Contention for undo block = '||
(round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from v$waitstat
where class = 'undo block' 2 3 4 ;
old 2: (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
new 2: (round(count/( 117318+0.00000000001),4)) * 100||'%'

'CONTENTION FOR UNDO BLOCK='
--------------------------------------------------------------------------------
Contention for undo block = 1.97%

SQL>

If the percentage for an area is more than 1% or 2%, consider creating more rollback segments.

We do have some "undo block" contention since the percent wait is 1.97%. We cannot manually add more rollback segments since ours is System Managed Undo.


Another way to gauge rollback contention is:


Hit Ratio should be >= 99% - if not, consider adding additional rollback segments.

SQL> select b.NAME,
a.USN seg#,
GETS,
WAITS,
round(((GETS-WAITS)*100)/GETS,2) hit_ratio,
XACTS active_transactions,
WRITES
from v$rollstat a,
v$rollname b
where a.USN = b.USN;



Note: Scripts published in this blog are tested, but under any circumstance of failures or bad effect/low performance on database point of view, members of this blog are no were responsible.