15.8 How do I…Identify rollback segment contention?

Problem

I administer a database with significant amounts of update, delete, and insert activity. I know that rollback segment contention can degrade performance in a database environment like mine. How can I determine if my database is suffering from rollback segment contention?

Technique

Oracle8 uses rollback segments for all transactions that change the database and assigns every such transaction to one of the available rollback segments. Every rollback segment has a transaction table in its header and every write transaction, moreover, must periodically acquire update access to the transaction table of its rollback segment.

Contention becomes an issue when Oracle8 has assigned more than one transaction to any given rollback segment, and those transactions attempt to access the rollback segment’s transaction table at the same time.

The UTLESTAT report contains a section on rollback segment statistics, which appears in Listing 15.10.

Listing 15.10 An excerpt from the rollback segment performance section of the UTLESTAT report

UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS

------------------- ------------------- -------------------

0 7 0

1 412 42

2 652 57

3 1121 127

4 65 7

5 72 9

The column TRANS_TBL_GETS contains the number of number of times that Oracle accessed the header of the rollback segment specified (by rollback segment number, unfortunately) in the UNDO_SEGMENT column. The column TRANS_TBL_WAITS contains the number of these gets, which results in a temporary wait state because another transaction already accessed the rollback segment’s header.

A school of thought maintains that any non-zero value in the TRANS_TBL_WAITS column should prompt the DBA to add rollback segments. This is too conservative for most production installations and a better guideline to follow is that whenever the ratio of TRANS_TBL_WAITS to TRANS_TBL_GETS is greater than 2 percent for any rollback segment, there is rollback segment contention.

Another way to detect rollback segment contention is to use the V$ROLLSTAT table. It appears in Table 15.7.

Table 15.7 The structure of the V$ROLLSTAT table

Column Column Description
USN Rollback segment number
EXTENTS Number of rollback extents
RSSIZE Rollback segment size in bytes
WRITES Number of bytes written to rollback segment
XACTS Number of currently active transactions
GETS Number of header gets
WAITS Number of header waits
OPTSIZE Optimal size of rollback segment
HWMSIZE High water mark of rollback segment size
SHRINKS Number of times the size of a rollback segment decreases
WRAPS Number of times rollback segment is wrapped
EXTENDS Number of times rollback segment size is extended
AVESHRINK Average shrink size
AVEACTIVE Current size of active extents, averaged over time
STATUS Rollback segment status
CUREXT Current extent
CURBLK Current block

Like the rollback performance section of the UTLESTAT report, the V$ROLLSTAT view uses the rollback segment number instead of the name to identify each rollback segment. The V$ROLLNAME table, appearing in Table 15.8, maps the rollback segment number to the rollback segment name.

Table 15.8 The structure of the V$ROLLNAME table
Column Column Description
USN Rollback segment number
NAME Rollback segment name