"ORA-01555: Snapshot too old: rollback...too small". Why do I get it? How can I stop it?

At the urging of Forum colleague, Sem (Dima), and others, I am posting this FAQ reqarding one of Oracle's messiest and most difficult-to-manage problems.

* You can see (much) more banter about this topic at thread186-628107.

* Testimony that increasing the sizes of rollback segments does not eliminate the error appears in Beksalur's thread759-707100.

* Tom Kite's treatment of this issue appears at "http://asktom.oracle.com/pls/ask/f?p=4950:1:", then enter "ORA-01555: Snapshot too old" in the Search criteria. Choose the first article. Although Tom's article acknowledges the causes I mention below, his article does not mention the "no-fail" solution that appears, below.

=======================================================
"ORA-01555: Snapshot too old...": Causes and Solutions:
=======================================================
This error is perhaps the most esoteric and non-intuitive error in all of Oracle. The reason it is so esoteric is because understanding both the problem and solutions requires a significant amount of explanation (and patience). Three sections follow: 1) My editorial comment on the problem, 2) Background and a Scenario that causes this error to occur, 3) a fool-proof solution against encountering this error.

1) My editorial comment: The error results from an architectural problem with the Oracle database itself that has existed since OracleÆs very beginning of rollback segments. The fact that it occurs at all is, in my opinion, an Oracle BUG, which Oracle refuses to fix, so you and I must implement our own workarounds to deal with this problem. To compound the problem, the ôfixö that we infer from the error message (in this case, to increase the size of a specific rollback segment) is virtually useless in resolving the problem. To COMMIT ôearly and oftenö as we typically hear, may not be a viable option. So, I shall explain the PROBLEM and some SOLUTIONS in the following.

2) Background and a scenario to understanding the error:
LetÆs assume the following:
Assumption 1: a database with seven rollback segments.

Assumption 2: One of rollback segments, R07, our DBA has made extremely large, and we 'set transaction use rollback segment R07' before our long-running transaction.

Assumption 3: the other six rollback segments are of ænormalÆ size (whatever ænormalÆ is).

Scenario:
At 12:02:35, User A executes an update transaction that Oracle assigns to rollback segment R01, starting its writing at R01Æs Block Number 126 of 128, since User Z is using blocks 1-125.

At 12:02:39, User Z commits her changes (freeing up blocks 1-125 of rollback segment R01).

At 12:02:40, User B begins our large, long-running transaction. As we designed, Oracle assigns this transaction to R07 (the "way-big" rollback segment). This large transaction needs to see database data that was ôtrueö (i.e., committed) at the beginning of this transaction. User A changed database blocks that User B will read later at, say 12:23:30; Oracle copied User AÆs original data blocks (that were ôtrueö at 12:02:40) to rollback segment R01. The only place on earth that User B will be able to find data that was ôtrueö/committed at the start of User B transaction (at 12:02:40) is in rollback segment R01.

At 12:02:45, User A commits her changes. Her pre-change, consistent, PRE-COMMIT images occupied blocks 126, 127, 128, 1, 2, and 3 of rollback segment R01.

At 12:02:50 through 12:03:20, Users C, D, E, F, G, and H initiate transactions that Oracle automatically assigns to rollback segments R02, R03, R04, R05, R06, and R07, respectively.

At 12:03:30, User J initiates a transaction. OracleÆs ôround-robinö-rollback-segment-assignment algorithm goes back to R01. To determine how to use rollback segment R01 (i.e., where to begin writing rollback data), Oracle asks the question, ôAre there any un-committed transactions in rollback segment R01, whose rollback data we still need FOR THE TRANSACTION THAT WROTE THE DATA?ö The answer is ôNoö, (since COMMIT happened at 12:02:45) therefore Oracle begins writing rollback data to R01Æs first available location, Block Number 1. Notice, then, that User JÆs rollback data overwrites User AÆs rollback images, WHICH User B NEEDS TO READ IN 20 MINUTES FROM NOW !!!

At 12:23:30, User B reaches the point where she needs to read the data that was true when User BÆs transaction began at 12:02:40. The only place that data resided was in blocks 126, 127, 128, 1, 2, and 3 of rollback segment R01. Those data are LONG GONE, having been overwritten by at least User JÆs rollback entries. Oracle generates the error: ôORA-01555: snapshot too oldàö Notice also, that the gigantic rollback segment R07 didnÆt help us at all since the data we needed was in one of the ænormalÆ sized rollback segments.

Possible solutions:
Solution 1: Make ALL rollback segments GIGANTIC. How large is GIGANTIC? So large that during the life of your longest-running transaction, the OTHER concurrently running transactions could not possibly generate enough rollback entries to ôrolloverö on itself and overwrite rollback entries from other previously committed/rolledback transactions, which still-running, long-running transactions still need. The problem here is, who has a crystal ball to anticipate just how large each rollback segment must be to ensure no rollover. (Note in thread759-707100 that Beksalur received this problem with rollback segments that are set to grow to 5GB+.) Even if we could anticipate how gigantic ALL rollback segments must be, think of all the space that the rollback segments would NOT be using 99% of the time. What an (expensive) waste of disk space.

Solution 2: Kick everyone else off your Oracle instance while your long-running transaction executes. Yea, right !!! I know what kind mutiny that would cause at our company.

Solution 3: (This is the solution our organization uses to inoculate against ORA-01555 and we have found it to never fail us.) Before running a known long-running transaction, we create a bogus transaction (such as ôINSERT INTO DUMMY VALUES (æXÆ) for each on-line rollback segment in the database (except the SYSTEM rollback segment, which an application transaction would never use.) We do a ôSET TRANSACTION USE ROLLBACK SEGMENT <rollback segment name>;ö for each of the transactions. These bogus transactions, since they remain æactiveÆ during the life of your long-running transaction, prevent Oracle from rolling over a rollback segment on itself; thus preventing the loss of vital rollback data to the long-running, referencing transaction. Instead of rolling over upon themselves, the rollback segments ægrowÆ by adding more extents rather than re-using still-needed extent blocks. For this to work, each of the bogus transactions needs to originate from a different session/window. Once your long-running transaction successfully runs to its natural end (including COMMIT), then you can rollback each of the bogus transactions.

If each of your rollback segments have a æàSTORAGE (OPTIMAL xxMB)àö, then each rollback segment will shrink down to the OPTIMAL size, thus freeing up all of the ôextraö space that the bogus transactions caused in order to preserve the vital rollback entries.

IMPORTANT NOTE: Following the completion of your long-running transaction, you MUST close off each of the bogus transactions with either an explicit or implicit COMMIT or ROLLBACK. If you do not stop the bogus transactions, then they cause the rollback segments to grow until you are out of disk space.

Dave
Sandy, Utah, USA @ 20:51 GMT, 13:51 Mountain Time