Previous | Table of Contents | Next

Page 503

using the block control information (the SCN, or system change number), that it needs to look to the rollback segment to find consistent information.

Note that the proper processing of this situation is entirely reliant on the transaction having sufficient rollback space, so that information written to the rollback segment is not overwritten by subsequent transactions. If the information the transaction needs does get overwritten, you will get the all too common and hateful snapshot too old error.

The previous example is one aspect to be considered while sizing for rollback segments. From the discussion, it's clear enough that there should be enough space in the rollback segments to store these inactive or committed data transactions that may be accessed by other long-
running queries.

Another extreme case to be appreciated is that when getting a read consistent view of the database, it might not be the amount of rollback data generated that matters. For example, consider a small transaction that modifies some row of data hours after some long-running query started. In this case, it's not the amount or rollback the update creates, but rather the existence of enough rollback space to keep the tiny rollback segment record in the segment.

You can avoid problems with long-running queries by:

The last point above can be accomplished by using the following SQL command:

set transaction use rollback segment RB1;

When you issue this set transaction statement, all DML issued, up to the commit, is stored in the specified rollback segment. This is useful for directing transactions to specific rollback segments. Long-running queries work best with large rollback segments, because the overhead involved in dynamically allocating rollback segment space is avoided.

Avoiding Rollback Segment Contention

The number of transactions that can be allocated to a rollback segment is determined by the init.ora parameter TRANSACTIONS_PER_ROLLBACK. The default is usually sufficient, but some tuning is advised if you create unusually large or small rollback segments. By looking at this parameter value and the number of concurrent transactions you have, you should get a good idea of how many rollback segments you need to create.

Contention for rollback segments occur because every time writes are made to a rollback segment, the rollback header segment is locked. This contention can be avoided by having a

Page 504

sufficient number of rollback segments for the transactions in your database. The following are some scripts to help you detect contention problems:

Script 1: If the following select output reports a very high value in the count column, you may be experiencing waits on rollback segment allocation, and you may have to increase the number of rollback segments.
SELECT class,value
FROM v$waitstat
WHERE class in (`system undo header',
                `system undo block',
                `undo header',
                `undo block'  ) ;
SELECT usn,writes,xacts,shrinks,extends,gets,waits
FROM  v$rollstat;
USN    WRITES   XACTS  SHRINKS  EXTENDS  GETS   WAITS
------ --------- -----   -------  -------  -----  -----
    0       2110    0       0        0        124    0
    1    6435854    2       0        0      24733    2
    2    4579731    1       0        0      16911    0
    3   10962780    1       0        0      20496    1
    4    6826471    1       0        0      29601    5
    5    3140954    1       0        0      18703    2
    6   14251656    2       0        0      27807    4

Using the OPTIMAL Parameter

When creating the rollback segment, the target size of the rollback segment can be defined using the OPTIMAL storage parameter. The optimal parameter specifies the size to which rollback segments should shrink to, when extents are not in use, and other active transactions require space in the rollback segment.

Let's look at a sample create rollback segment statement:

create rollback segment r05
tablespace rbs
storage ( initial 10M
          next     10M
          optimal  20M
          minextents 2
          maxextents 100);

The RDBMS tries to maintain the size of this rollback segment at the specified optimal value. The size is rounded up to the extent boundary, which means that the RDBMS tries to keep the

Page 505

fewest number of extents, such that the total size is greater than or equal to the size specified in OPTIMAL.

Whenever a rollback segment has to extend, it checks if, as a result of this extension, the total size of the rollback segment is greater than the OPTIMAL size. If the size is greater, it deallocates all inactive extents. When attempting to deallocate the inactive extents, the oldest ones are first deallocated.

The OPTIMAL command is seen by some as a brother to the PCTINCREASE storage parameter—the lazy DBA's way to administer. Although it does make rollback segment administration slightly easier, it can also prove to be more harm then help.

CAUTION
If you find your rollback segments are continuously shrinking because of the OPTIMAL clause, it is likely that your rollback segments are improperly sized. If this extent allocation and deallocation is common, it can become a performance concern.

Performing Load Tests to Obtain Rollback Estimates

To correctly size the rollback segments you need to have an idea of the amount of undo information that is going to be generated. In essence, every rollback segment needs to be large enough to accommodate the largest rollback transaction, while not being so large that they waste inordinate amounts of space. In order to get a reasonable estimate of the size of the undo generated, we will perform a load test on a sample transaction that generates a large amount of undo using Listing 21.1.

For the test we are performing, ensure that no other active transactions are generating undo information. The steps involved in generating a load test are described below:

  1. Create two tables to hold our statistical information: stat$undo_begin and stat$undo_end.
  2. Assume that the load test is performed on rollback segment, ro1. After connecting to the database, issue a set transaction command to ensure that all the undo data belonging to this transaction is written into rollback segment ro1.
  3. Capture the initial amount of data written to the rollback segment ro1 to the table stat$undo_begin by using the v$rollstat table.
  4. Issue the test transaction statement.
  5. After the transaction completes, again obtain the amount of data written to the rollback segment ro1 to the snapshot end table stat$undo_end.
  6. Now that we have the snapshot start and snapshot end values in the tables, it's just a matter of subtracting the two values to obtain the amount of data written to rollback segment ro1. These value can be used to determine sizing needs of your rollback segments.

Listing 21.1 is the script for performing the previous steps.

Previous | Table of Contents | Next