Browse DevX
Sign up for e-mail newsletters from DevX


The ORA-01555 Error: A DBA Solution-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Determining the Proper Rollback Segment Amount and Size
There are two issues that you need to consider when deciding whether your segment is large enough to fit the transaction:
  • Make sure that transactions will not cause the head to wrap around too fast and catch the tail. This causes the segment to extend in size, as discussed above.

  • If you have long-running queries that access frequently changing data, make sure that the rollback segment doesn't wrap around and prevent the construction of a read-consistent view. (I'll discuss read-consistency issues in more detail in my follow-up 10-Minute Solution, "The ORA-01555 Error: A PL/SQL Developer Solution.")
The size needed for a rollback segment depends directly on the transaction activity of the database. Your primary concern should be the activity during normal processing of the database, not rare or semi-frequent large transactions. Deal with such special cases separately.

The number of rollback segments needed to prevent contention between processes can be determined with the use of the V$WAITSTAT view. Waits are a definite indication of contention. The following V$WAITSTAT query displays the number of waits since the instance startup:

SELECT Class, Count FROM V$WAITSTAT WHERE Class LIKE '%undo%';

Any nonzero value in the Count column indicates rollback segment header contention.

To find out the size and number of rollback segments needed to handle normal processing on the database, you need to do some testing. A good test is to start with small rollback segments and allow your application to force them to extend. The maximum size that any rollback segment reaches during the test is the size you want to use when configuring segment. If you see any contention, adjust the number of segments and rerun the test. Also, if the largest size requires fewer than 10 extents, or more than 30, it is a good idea to lower or raise the extent size, respectively, and rerun the test. Otherwise, space may be getting wasted during the test, which would throw the number off. For large transactions you can create separate rollback segments.

For sizing rollback segment extents, I strongly recommend that you size each extent equally and make the rollback tablespace a multiple of that extent size. The minimum number of extents for an individual segment should be around 20 for best performance.

Rollback segments dynamically allocate space when required and deallocate space when they are no longer needed (if the OPTIMAL parameter is used). The fewer extents that a rollback segment consists of, the larger and the less granular these space allocations and deallocations are. For example, consider a 200MB rollback segment that consists of only two 100MB extents. If this segment were to require additional space, it would allocate another 100MB extent. This immediately increases the size of the rollback segment by 50 percent and potentially acquires more space than is really needed. By contrast, if the rollback segment consisted of 20 10MB extents, any additional space required would be allocated in 10MB pieces. When a rollback segment consists of 20 or more extents, any single change in the number of extents will not move the total size of the rollback segment by more than 5 percent, resulting in a much smoother allocation and deallocation of space.

Given this, increasing the number of extents beyond the suggested 20 will make space allocation and deallocation even smoother. However, in-house testing shows rapidly diminishing returns when increasing the number of extents past 20. In addition, allocating and deallocating extents is not a cost-free operation. The database will experience performance degradation when performing extent operations. The cost for individual extents is minor but a rollback segment, which is constantly allocating and deallocating tiny extents, can cause even a minor cost to add up.

Ensuring Read Consistency
Oracle always enforces statement-level read consistency. It guarantees that the data returned by a single query is consistent with respect to the time when the query began. Therefore, a query never sees the changes to the data made by transactions that commit during the course of execution of the query.

Oracle uniquely identifies any given point in time by a set of numbers called System Change Numbers. Think of the SCN as the state of the database at any one point in time. As a query enters its execution phase, Oracle assigns a current SCN to it. The query can only see the snapshot of the records as they were at the time they were marked by, or assigned to, the SCN. Oracle uses rollback segments to reconstruct the read-consistent snapshot of the data. Whenever a transaction makes any changes, a snapshot of the record before the changes were made is copied to a rollback segment and the data block header is marked appropriately with the address of the rollback segment block where the changes are recorded. The data block also maintains the SCN of the last committed change to the block.

As the data blocks are read on behalf of the query, only blocks with a lower SCN than the query SCN will be read. If a block has uncommitted changes of other transactions, or has already changed data with a more-recent SCN, then the data will be reconstructed using the saved snapshot from the rollback segments. A rollback segment maintains the snapshot of the changed data as long as the transaction is still active (that is, a commit or rollback has not been issued). Once a transaction is committed, the database marks it with the current SCN and the space used by the snapshot becomes available for reuse. Therefore, an ORA-01555 error will occur if the query is looking for a snapshot that's so old that rollback segment information could not be found because of a wraparound or overwrite.

Next I show you two cases that describe how an ORA-01555 error could be triggered, along with the solution a DBA could follow to avoid the error.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date