Spikmeister,
You have encountered 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). If any of you (as readers) do not have the patience or interest in the explanation, you might as well stop here, otherwise, read on. 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 your case, to “increase the size of rollback segment number 4 named R03”) 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 do as you suggest, '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 a 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. 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.
Whew! I hope this helps. Let me know.
Dave
