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.
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
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 parameterthe 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. |
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:
Listing 21.1 is the script for performing the previous steps.