ORA-01555: snapshot too old - Problem with Rollback Segments? 15

[Spikmeister]

I'm using Oracle 8i and doing a data load of almost 800000 records. I then need to update all these records after doing some complex searching on other tables.

However I keep getting this error (normally only a couple of hundred records short as well!):
ORA-01555: snapshot too old: rollback segment number 4 with name "R03" too small

Our DBA has increased one rollback segment (R07) to be very large and I've tried using "execute immediate 'set transaction use rollback segment R07';" within the procedure but it still appears to be using the wrong rollback segment and erroring.

Any ideas on how I can stop it from erroring?

 

[Chalco]

Whenever a commit happens, you will lose your explicit "set transaction" - if you are using sqlloader, set the commit size to mare than the No of records, if no other users are on the databases, you could offline the other rollback segments

HTH

 

[ajayarora]

It is due to your outer cursor, you need to open and close your outer cursor at a certain commit frequency ( after processing 5000 records may be ).If you can post the structure of your procedure then I would be able to make it more clear to you.

Regards

 

[SantaMufasa]

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

 

[carp]

Dave -
Kudos for a lovely explanation (I've come to expect no less from you) as well as a beautiful solution!

 

[sybaseguru]

I fail to see how Solution 3 by Dave if I am correct can be a practical solution let alone "a beautiful" one? How about tens of unknown long running transactions in the database? For a solution to be practical it needs to address real life scenarios. This answer if I may say and with great respect looks more like a text book cut and paste from Oracle metalink as an explanation for causes of ORA-01555.

 

[carp]

As far as I can tell, this WOULD address &quot;real life&quot; situations! The original question was not &quot;what should we do about our tens of unknown long running transactions&quot;. It was &quot;how can we prevent ORA-1555 while we are doing a specific, KNOWN long load&quot;? And Dave's solution should take care of the problem by effectively sticking a wedge into each rollback segment so that the rollback information does not get lost. This strikes me as a far better approach than &quot;take all of the other rollback segments offline&quot;. The fact that Dave is maintaining multiple databases worldwide and - as he notes - this approach has never failed them would indicate to me that it IS a &quot;real life&quot; solution - and looks &quot;beautiful&quot; to me. But of course, that's in the eye of THIS beholder.

A cut/paste from Metalink would have given the rote answer:
- Bigger rollback segments.
- More rollback segments.
- At least 20 extents/rollback segment.
which might or might not have helped Spikmeister.

 

[SantaMufasa]

Sybaseguru,

1) A fully automated version of my Solution #3, above, is, in fact, installed in 60+ database sites from London to San Francisco. We haven't had an ORA-01555 problem at any of the sites during the four years that the solution has been in place.

2) Nothing was plagiarized from the Oracle MetaLink. Since you won't find my editorial, above, anywhere else but on my harddrive, or in classroom handouts that I authored and distributed for years at Oracle, I'll accept your compliment. Since I authored many of the classroom materials for Oracle Education (as Int'l Manager of Education Product Development), I expect that you'll find a lot of my materials in electronic and hardcopy form at Oracle.

 

[Spikmeister]

Well I'll end this by thanking everyone for their helpful suggestions.

I managed to get the full load to run without tripping ORA-01555, mainly by using the &quot;set transaction use rollback segment R07&quot; within my loops and therefore before every commit. I'm not sure if this will work when the full system is up and running and there are more processes (and another huge system on the same DB) running but when I hit that hurdle I'll try and find another way round it, probably by using some of the things suggested in here!

Cheers,

Spikmeister

 

[sem]

I suppose that &quot;before every commit&quot; was a typo, because this statement should be the first in transaction (error ORA-01453 may be generated).

Regards, Dima

 

[Spikmeister]

I've had that error many times in my playing with this code to get it to work.

I've put it in before all the UPDATEs and INSERTs and &quot;CREATE TABLE AS SELECT&quot;s which are followed by COMMITs. I've had a lot of trial and error while writing this and trying to get the &quot;SET TRANSACTION&quot; in the right place.

 

[sem]

So have you resolved it?
The idea is you should place it at the beginning of your transaction. The safe place is AFTER commit/rollback statement, because after finishing previous transaction you may guarantee that NO STATEMNTS were executed in this new one. Of course, if you call it separately, you should also set it at the beginning of your code. Otherwise you must commit or rollback previous changes before it.
BTW, does your code contain a lot of huge transactions?

Regards, Dima

 

[SantaMufasa]

Folks, I can absolutely, positively guarantee that using the SET TRANSACTION command to assign your long running transaction to a large rollback segment WILL NOT SOLVE YOUR PROBLEM WITH ORA-01555. The problem results from ANY OTHER transactions that were in process at the time your large transaction began, and those OTHER transactions then commit and their rollback data (residing in standard-sized rollback segments) is overwritten by yet additional transactions. The SET TRANSACTION issue is absolutely a RED HERRING.

As a separate issue, then, is how to guarantee the proper behavior/use of SET TRANSACTION. The method is to always issue a COMMIT as the command prior to the SET TRANSACTION. This method guarantees the end of previous transaction activity.

But again, I cannot emphasize enough, SET TRANSACTION of your long-running transaction to a large rollback segment WILL NOT (by itself) PREVENT ORA-01555. You must use either Chalco's solution to off-line all but the large rollback segment, or Solutions #2 or #3 from my admittedly long-winded explanation from Aug. 12.

Cheers,

Dave

 

[sem]

Speaking frankly, I also use the same technique as Dave. But assigning rollback segment is also usefull feature and now I'm trying to help in resolving ORA-01453, not ORA-01555

Regards, Dima

 

[SantaMufasa]

Dima, doesn't a COMMIT just prior to the SET TRANSACTION command ALWAYS prevent ORA-01453? Even if you have already done a COMMIT or ROLLBACK prior to this COMMIT, this COMMIT is absolutely harmless and it guarantees that ORA-01453 won't happen.

 

[sem]

Dave, how does it contradict my message? The same way as placing COMMIT before SET TRANSACTION is harmless, placing COMMIT after SET TRANSACTION is useless. That was a clue of my message.

Regards, Dima

 

[Spikmeister]

BTW, does your code contain a lot of huge transactions?

Yep it does...

I don't wish for an arguement to happen in here.

The fact that the rollback segement that I'm using as the largest and also the last to be used in the round robin approach that Oracle uses must surely play a part in it, also we're considering taking the large rollback segment offline when it's not being used by myself. We can't take the other rollback segments offline as this data load is part of an important system that shares a DB with an even more important and larger system (that thankfully doesn't have such huge data loads, I think it's largest is about 20000 records!).

The possibility of people overwriting the data I'm using is not a problem as that table is not used by anything until after my process has completed - we have two identical tables that we switch between (one live, one ready for the load) as we knew that that could potentially be a problem.

All of our &quot;SET TRANSACTION&quot; statements are after COMMITs and just before we do the huge transactions.

 

[sem]

Spikmeister, I suppose that you don't clearly undestand an issue: you loose your data not because somebody changes it in your table, but because somebody needs a space for its own data and overwrites the space used by unchanged image of your table.
I think Dave with his instructor's skills may explain it better.

Regards, Dima

 

[Spikmeister]

But in his long example he spoke about people changing the data that would then be read by the long procedures cursor - this cannot happen with my table.

 

[SantaMufasa]

Dima, My apologies if I misunderstood when you said, &quot;...now I'm trying to help in resolving ORA-01453.&quot; My just-previous post (...COMMIT before SET TRANSACTION) was just my suggestion for a fail-safe method to avoid ORA-01453. Where Spikmeister says, '...All of our &quot;SET TRANSACTION&quot; statements are after COMMITs and just before we do the huge transactions...', it appears he is properly preventing ORA-01453.

However, as mentioned before, SET TRANSACTION for the long-running transactions alone WILL NOT PREVENT ORA-01555.

Spikmeister, you mentioned also that &quot;...We can't take the other rollback segments offline...&quot;. Tell me more. Oracle's rollback segments are designed to each accommodate 20+ concurrent transactions without performance problems. Have you confirmed the number of in-flight transactions occurring in the rest of your system during the long-running transaction(s). If that number remains < 20, then you CAN off-line the other rollback segments without concern; they can all concurrently share your &quot;large&quot; rbs.

Another comment: Oracle designed its rollback segments to grow as large as necessary to accommodate even your largest transaction without need of &quot;making it big&quot; before your transaction begins. Making it big before-hand improves performance DURING the transaction since extent allocation occurs before the transaction begins. But so long as your transaction never causes a rollback segment to occupy more than 32,765 extents (where maxextents unlimited for your rollback segments), and as long as you have enough free extents in your RBS tablespace, then you won't ever find your transaction getting too big for its rollback segment. (This is another reason for SET TRANSACTION causing a false sense of security insofar as ORA-01555 is concerned.)

If you choose not to off-line all but one of your non-SYSTEM rollback segments, and you cannot kick your &quot;other&quot; users off the system while running your large transactions, then guess what -- Your only guaranteed method of preventing ORA-01555 is &quot;Solution #3&quot;, above. If this does not make sense to you, then I suggest you re-read the long-winded post above until it does make sense.

Thanks, Dima, for your compliment re: the explanation skills; I'll suggest a re-read of the previous (long) post as the best explanation for anyone not yet understanding the problem.

Cheers,

Dave