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

Steps

1. Start SQL*Plus and connect as the WAITE user. Use the START command to load and execute the query in CHP15_14.SQL. The query and its result appear in Figure 15.10.

Line 4 of the script calculates the waits to gets ratio for each online rollback segment in the database.

How It Works

The query in Step 1 joins the V$ROLLSTAT and V$ROLLNAME dynamic performance tables to help detect rollback segment contention. If the value of WG_RATIO consistently exceeds 2%, then adding more rollback segments may help.

Comments

Oracle database literature often focuses on rollback segment contention and sizing. Most discussions of rollback segment sizing begin with the expected size of an “average” transaction. This number is difficult to come by in most organizations. A better and much simpler strategy is to use the general rule of thumb that there should be one rollback segment for every ten concurrent users or one rollback segment for every four concurrent transactions.

Oracle indicates some common rollback segment errors with the messages failure to extend and snapshot too old. The former error is easy to understand: a large transaction has consumed all the rollback segment’s extents or all the available space in the rollback segment tablespace. The snapshot too old message means that some transaction was using a rollback segment to obtain a read consistent view of the database and the rollback segment was overwritten with new rollback (or “undo”) data. Either of these messages indicates a need for larger rollback segments.

Most tuning literature currently suggests that rollback segments should be composed of 20 equally sized extents. The OPTIMAL parameter of the STORAGE clause for a rollback segment, as in script CHP1_4.SQL in Chapter 1, will request that Oracle8 attempt to maintain a particular rollback segment size. If the SHRINKS or EXTENDS columns in the V$ROLLSTAT table are significantly different than zero, then the DBA should reconsider rollback segment sizes.

It is often difficult to size rollback segments optimally for an Oracle instance if that instance is both a decision support system and an OLTP target. In the former case, users and applications probably will not be making significant use of the rollback segments because their database access is often of the read-only variety.

In the latter situation, even if the size of an average transaction is fairly clear and quite small, there will undoubtedly be a need for a large database batch job. Oracle developers realize this and Oracle features the SET TRANSACTION statement to enable users to specify a particular (and usually large) rollback segment.