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