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?

 

[sem]

He didn't state that those blocks belong to the same table: they're in the same RS.

Regards, Dima

 

[SantaMufasa]

Spikmeister, That's an interesting wrinkle: "...people changing the data that would then be read by the long procedures cursor...cannot happen with my table."

I, too, have witnessed the same situation (before implementing our automated version of Solution #3, above): We had no other users on an instance, no other in-flight transactions during our long-running transaction, yet we still encountered ORA-01555 for a rollback segment that our long-running transaction was not even using. ...A real puzzler. It was as though some (phantom) process relating to our own long-running transaction was executing in parallel to our long transaction, using another rollback segment, producing rollback entries that the long-running transaction apparently needed. Seems impossible, but all I can state is the facts.

We immediately implemented Solution #3, and never encountered the problem again. Strange...

(Since it is 3:40 a.m. here, and I'm getting sleepy, as much as I'd like to stay up for more fun with you all, I believe I'll get some shut eye and pick this up again in the morning.) Cheers,

Dave

 

[JimboNWUK]

Well,Dave, I for one have copied and pasted your explanation into my personal 'Oracle Tips & Tricks' book!
I've been an Oracle techo for over 18 years during which I have been an A/P, a trainer, dipped my toe into DBA-land, and currently am a systems support analyst and have never seen an explanation of that 'snapshot too old' error ANYWHERE, much less one that was so clearly stated and easily understood (AND an elegant solution given to boot!). Companies pay contractors mega-bucks for the kind of expertise you possess, and some people must be just jealous of you obvious wealth of knowledge and experience to display such pouty-faced ingratitude as that displayed in this thread by certain individuals.
I also admire your patience in your further replies an insistence about SET TRANSACTION and your solution 3 being the only viable winner.
Bravo matey!!

 

[SantaMufasa]

Very kind words, Jimbo. I'm glad you found the post(s) useful !

Very best regards,

Dave

 

[theshowmecanuck]

Hi Guys,
sorry for tagging on to the end of this, but it sound like the folks here might have an answer to a very similar issue.

I am getting the same error message (ORA-01555: snapshot too old... rollback blah blah blah), but it is relating to the use of a global temporary table. I don't believe that these tables are logged so shouldn't have rollback issues. Are the same segments used for temp tables as for rollback, or is the message just some sort of thrown in generic message that is telling me that I don't have enough space assigned to for the temp tables?

Any help would be appreciated.

Thanks,

BillR

 

[SantaMufasa]

Bill,

It gives me goosebumps (in the Commomwealth, read, "gooseflesh&quot to think that we're revisiting this thread since it carried with it such a range of emotions, but no problem...

Again, Bill, your receiving this error has nothing really to do with the size of your global temporary table(s) or whether or not the temp tables generate any logging. The problem comes from some need in your query/temp table usage for consistent-image data from SOMEONE ELSE'S earlier (now-committed and gone) transaction.

(And, no, the same segments are not used for temporary tables as for rollback segments.)

If you encounter this error, then unquestionably, the same solution options from earlier posts apply.

Cheers,

Dave

 

[theshowmecanuck]

So now I am at a loss. The short of it is that the only DML is an insert into a temp table. What confuses me is that I thought temp tables don't require the use of a rollback seqment since operations on them are not logged.

For background, the error occurs when running a complex report type query. It is basically a very complex select that requires the use of a temp table to grab a subset of data from a very very large table and removes the need for a quite complex subquery. I first populate the temp table, then complete the rest of the query by joining to the temp table. This is output to file.

The process doesn't actually disconnect from Oracle as the process is actually a daemon that extracts data every 6 to 60 minutes depending a number of circumstances. We truncate the temp table prior to each round. In addition, there could be several (similar) processes using the same temp table. Could this be where the problem lies?

Thanks... BTW, I read the earlier posts and I don't blame your reaction. I had a similar incident occur.

BillR

 

[SantaMufasa]

Bill,

You are correct: &quot;...temp tables don't require the use of a rollback segment since operations on them are not logged.&quot; The TEMP tables are not logging (writing) to a rollback segment, BUT (and this is a Big But <g>), as you mention, you are &quot;...running a complex report-type query...(where you grab) a subset of data from a very very large table...&quot;

Now, where do those data come from? From a very very large table that someone was CHANGING when your query began. In such a case, the only place on earth from where you can read consistent data that was changing when your TEMP-table create began is (was) from SOMEONE ELSE's rollback segment that THEY were using when your query began. Since your query is admittedly long-running, you cannot expect the other updating users to wait around to COMMIT their transaction while your query finishes. Therefore, after they commit, their rollback entries are over-writable &quot;fair game&quot;, and the rollback entries are GONE before you can read them for your TEMP table. Thus, &quot;ORA-01555: Snapshot too old...&quot;

So, the way to prevent loss of SOMEONE ELSE's rollback entries despite their commit, is to prevent the rollback entries from being overwritten by some third or fourth party's subsequent transaction. I do this, as it mentions, above, by creating bogus transactions in each on-line rollback segment that stay uncommitted during the long-running transaction.

Is this un-fuzzy yet?

Dave

 

[theshowmecanuck]

Ya... I get it now. Trouble is that this is part of an enterprise solutions that is really really huge. This means that usually I don't have access to how many rollback segments that we have or how they are named. And likely I won't know if they add or decrease the number of rollback segments. So it is virtually impossible for me to explicitly create bogus transactions for each rollback segment. A lot of the rest of the mechanism you recommend is also difficult to impossible due the humungus size of this enterprise solution and the crazy bureaucracy that goes with software applications of this size.

However... this doesn't mean we won't try to figure something out! <grin> Who knows, maybe we can sneak something in. <wink>

Thanks again.

BillR

 

[theshowmecanuck]

Hey Dave,
We have determined that we have a way to 'chunk' up our data. That is, grab the data that makes up the report in chunks, rather than all at once. This reduces the amount of data returned from the query making it less likely that rollback segment becomes an issue, since it will need to spend less time looking there. Mind you, I think the rollback segment will still be a factor, but only as much as for any transaction/query that might need to look at the before data. But it should reduce the chances of an error happening by a bunch.

Cheers,

BillR

 

[sem]

BillR, unfortunately Oracle doesn't provide ,MoreThanStatement-level read consistency by default, so the chance exists that 2 similar consecutive queries may return different results. Thus in some situations splitting large and dangerous but consistent query to a number of smaller but inconsistent ones is unsuitable. Seting transaction as read-only may cause the same error again.

Regards, Dima

 

[theshowmecanuck]

Hey Dima,
I thought about that too. I think you are right about this for most users. In our case the consistancy is not as much of an issue. This operation is repeated over and over again all day in a near real time data feed.

This means if we made a large data consitant read this time we would pick up the changes next read. By breaking it up, it means we will pick up the data one extract operation sooner (for the records from the second and later chunks that were broken up). I thought about this over-night, and I don't believe that this is an issue for us.

I agree though, that in probably 99% of the cases it would not be a good thing. So it's good that we're special here, even though we have to where a hocky helmet but they won't put us on a team!

Thanks for the feedback.

BillR

 

[sem]

As for 99%, the probability of Chernobyl accident (if you know what I'm talking about) was less than 0.0001%, but the consequences ...

Regards, Dima

 

[SantaMufasa]

Hey, Dima (Sybaseguru, Carp, and other experts),

I don't want to (re-)open up a big can of worms on this issue, but I just had a thought on another possiblity to avoid the ORA-01555 error...and this idea is more THEORETICAL than practical, but would it not avoid this error if the error-susceptible, long-running transaction began with a &quot;SELECT...FOR UPDATE&quot; command on the target records the the transaction processes? Yes, the long-running transaction would possibly need to wait for locks on the rows, but wouldn't you guarantee no exposure to ORA-01555?

Thoughts,

Dave

 

[sem]

Dave, are you from MS world ? To lock data just to read it? How about other users, ones who need to do the same?

I don't know the origin of this new idea, probably some of your queries failed at last , but I still successfully use issuing dummy updates before long running queries.

So, I hope that I understood your suggestion incorrectly due to my bad English.

Regards, Dima

 

[SantaMufasa]

Whooooaaaa, Dima ! (The English word to focus on in my reply, above, is the word &quot;THEORETICAL&quot; !) I AM NOT SUGGESTING &quot;SELECT...FOR UPDATE&quot; AS A SOLUTION TO IMPLEMENT. I am just examining the THEORY ! I certainly understand the implications of &quot;SELECT...FOR UPDATE&quot;. I would NEVER suggest doing this in real life for multiple reasons, including: 1) Others cannot work on rows that are locked via &quot;SELECT...FOR UPDATE&quot;, 2) The long-running transaction would become even longer-running, 3) And, MOST IMPORTANT, if everyone used this methodology on an installation, the entire database instance would become SINGLE THREADED, et cetera.

So, NO, I am not suggesting this as a method; I am simply looking at it as a THEORY (never to be actually implemented).

Whew!

Dave

 

[sem]

Again me. NO even for theoretical assumption. How do we handle subqueries and views? Shall we manually lock all the tables involved?

Regards, Dima

 

[sem]

Oops, my last post was not an answer on your last one. Yes, it should probably work. Theoretically

Regards, Dima

 

[SantaMufasa]

I am smiling when I say this, &quot;Focus, Dima, Focus&quot;<smiling><smiling>...

You are still looking at the PRACTICALITIES (such as &quot;How do we handle subqueries and views&quot;; those are PRACTICAL issues). I am simply asking, TOTALLY IN THEORY, &quot;Will a 'SELECT...FOR UPDATE', by definition, prevent ORA-01555?&quot; That's it. I'm not at all wondering is it practical or implementable.

Cheers,

Dave

 

[SantaMufasa]

Sorry, Dima, your &quot;oops&quot; posted before my reply, so since your reply is an &quot;oops&quot;, so also is my reply and &quot;oops&quot;, because it looks like you agree, IN THEORY <woohoo!>. (I'm away for the day now, so have a great one.)

Fir Milengue, (Phonetic Hindi for, &quot;See you later&quot

Dave Hunt (aka: Davinder Shikari, in Hindi)