Oracle7 Server Tuning

Contents Index Home Previous Next

Reducing Contention for Rollback Segments

In this section, you will learn how to reduce contention for rollback segments. The following issues are discussed:

Identifying Rollback Segment Contention

Contention for rollback segments is reflected by contention for buffers that contain rollback segment blocks. You can determine whether contention for rollback segments is reducing performance by using the dynamic performance table V$WAITSTAT.

The V$WAITSTAT contains statistics that reflect block contention. By default, this table is only available to the user SYS and to other users who have SELECT ANY TABLE system privilege, such as SYSTEM. These statistics reflect contention for different classes of block:

system undo header The value of this statistic is the number of waits for buffers containing header blocks of the SYSTEM rollback segment.
system undo block The value of this statistic is the number of waits for buffers containing blocks other than header blocks of the SYSTEM rollback segment.
undo header The value of this statistic is the number of waits for buffers containing header blocks of rollback segments other than the SYSTEM rollback segment.
undo block The value of this statistic is the number of waits for buffers containing blocks other than header blocks of rollback segments other than the SYSTEM rollback segment.
Monitor these statistics over a period of time while your application is running with this query:

SELECT class, count
   FROM v$waitstat
   WHERE class IN ('system undo header', 'system undo block',
      'undo header', 'undo block');

The result of this query might look like this:

CLASS              COUNT
------------------ ----------
system undo header       2089
system undo block         633
undo header              1235
undo block                942

Compare the number of waits for each class of block with the total number of requests for data over the same period of time. You can monitor the total number of requests for data over a period of time with this query:

SELECT SUM(value)
   FROM v$sysstat
   WHERE name IN ('db block gets', 'consistent gets');

The output of this query might look like this:

SUM(VALUE)
----------
    929530

The information in V$SYSSTAT can also be obtained through SNMP.

If the number of waits for any class is greater than 1% of the total number of requests, you should consider creating more rollback segments to reduce contention.

Creating Rollback Segments

To reduce contention for buffers containing rollback segment blocks, create more rollback segments. Table 10 - 1 shows some general guidelines for choosing how many rollback segments to allocate based on the number of concurrent transactions on your database. These guidelines are appropriate for most application mixes.

Number of Current Transactions(n) Recommended Number of Rollback Segments
n<16 4
16<=n<32 8
32<=n n/4,but no more than 50
Table 10 - 1. Choosing a Number of Rollback Segments

Contents Index Home Previous Next