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?

 

[theshowmecanuck]

Here is something interesting from the Oracle manual for 8i:

"Changed data blocks queried by a read-only transaction are reconstructed using data from rollback segments. Therefore, long running read-only transactions sometimes receive a "snapshot too old" error (ORA-01555). Create more, or larger, rollback segments to avoid this. Alternatively, you could issue long-running queries when online transaction processing is at a minimum, or you could obtain a shared lock on the table you were querying, prohibiting any other modifications during the transaction."

By the way, I think this is a GREAT thread. We're beating this one to death (or trying). This is the kind of thing I know I like to find when I run into a crazy kind of error like this!

Thanks for all the posts!

Bill Rosmus (phonetic English for Bill Rosmus)

 

[SantaMufasa]

Bill,

(I guess we participants on this thread are the types that want all the &quot;T's&quot; crossed and the &quot;I's&quot; dotted...Of course, capital &quot;I's&quot; aren't dotted, are they? <smile>).

Unfortunately, most of the suggestions you mention from the Oracle 8i manual do not SOLVE the problem, they just MIGHT SLIGHTLY REDUCE the chances of the error happening. The exception is that obtaining a lock will eliminate the error, but, as we (over)-pointed out in the just previous replies between Dima and me, it brings other valid production work/changes to a halt while our long-running transaction plods along, then finally releases the lock.

Not matter how we try to &quot;work around&quot; this problem, the root cause is still, in my opinion, an architectural/behvioural design flaw in Oracle (that has existed long before I started work at Oracle in 1988). Wouldn't it be nice if Oracle would please finally take responsibility for resolving the issue since it is certainly not an &quot;ERROR&quot; on the users' parts and preventing the error is just a major hassle?

Now, I'm climbing back down from my soapbox, wishing you all a good day.

Dave

 

[avjoshi]

I just wanted to mention to all Data Warehouse folks that chances are you may not face this problem. so.. don't go on changing all your big beefy queries to implement solution #3.

Anand.

 

[SantaMufasa]

Anand,

Can you share your insight as to why Data Warehousing folks might be innoculated against exposure to ORA-01555? The rest of us poor souls need to implement whatever prevents this problem for Data Warehousers.

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

 

[theshowmecanuck]

I would suspect that it is because data warehouses are usually updated/inserted very infrequently relative to an OLTP database. Many times just once a day, when the past days activities are loaded. Once the data is loaded, the data warehouse is used 99% of the time for query only. This means that queries don't have to go to the rollback segment for the 'old' view since nothing is being changed.

 

[avjoshi]

I didn't mean to be generic but majority of the DW applications are built such that the active queries happen out side of load window. That's why chances are you have only select statements in non-load time frame. In that case you don't need the rollback management.
If the DW has a 24/7 uptime or if it is an OLTP/DW hybrid implementation then, obviously you need to address that.

Anand.

 

[theshowmecanuck]

p.s. holy crap... the thread that never dies...

I suspect that eventually we can publish this as a book: &quot;The ORA-01555 Affair&quot; or &quot;How ORA-01555 Gave Me Hearburn&quot;.

 

[SantaMufasa]

Amen, Canuck !!! I'm not aware of any thread anywhere that has generated more reaction/posts. Maybe someday, Oracle will sit up and take notice that this is an issue that Oracle MUST resolve.

Cheers,

Dave
Sandy, Utah, USA @ 21:26 GMT, 14:26 Mountain Time

 

[gunjan14]

Hi,

Great to revisit this thread. I found this thread of immense help. Also I have faced problem when there are no other transaction running but my own and we are getting this problem. Could not understand then and also now as to why this happens?
Can you please explain why this error generates when no other transactions are happening?

Gunjan
(Keep the light of ORA-01555 burning)

 

[SantaMufasa]

Gunjan,

For more on that scenario, I recommend reading Tom Kite's treatment of the issue at asktom.oracle.com/pls/ask/f?p=4950:8:4246450541963543853::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:275215756923. (I hope the Tek-Tips engine does not truncate this link. If it does, just navigate to &quot;asktom.oracle.com&quot;, then search for &quot;ORA-01555: snapshot too old&quot;.) Pay particular attention to his treatise on &quot;delayed block cleanout&quot;.

Dave
Sandy, Utah, USA @ 18:06 (20Nov03) GMT, 11:06 (20Nov03) Mountain Time

 

[ruppula256]

Hi!

You folks observed: &quot;To COMMIT “early and often” as we typically hear, may not be a viable option. . . &quot;

I think I can have COMMIT's more often in my program. Does increasing the frequency from the current 1/(5000 records processed) to, say, 1/(500 records processed) help in any way?

Thanks!
R

 

[carp]

Absolutely.

 

[ruppula256]

Could you please elaborate?

Thanks!
R

 

[SantaMufasa]

Ruppula,

(I'll bet this thread continues to thrive long after we older folks are gone ).

Sorry we didn't get back to you sooner. Your question must have been buried under other posts, so Carp and I (and others) just didn't see it.

The answer to why &quot;increasing the frequency from the current 1/(5000 records [committed]) to, say, 1/(500 records [committed] helps&quot; is because there is a certain amount of overhead that Oracle incurs from a COMMIT statement. 90% of that overhead disappears when COMMITs occur every 5000 rows instead of every 500 rows.

And as a wise DBA once observed, &quot;COMMIT happens&quot; .

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:11 (16Dec03) GMT, 02:11 (16Dec03) Mountain Time)

 

[JediDan]

Wow. I guess other people are having this problem. Maybe someone can help me a little further:

I am inserting between 100000 to 250000 records into an otherwise-empty table on a monthly basis. While committing every 5000 or 25000 rows, etc., I get the ORA-01555 error. If I commit only at the end of the run, I get no error but the entire process takes much longer.

I don't believe I can use Dave's solution #3 (creating a dummy transaction for each rollback segment) because those transactions would have to be created from a separate SQL*Plus session (I believe...) and that wouldn't work in our production environment.

So question #1 is: Am I correct that creating the dummy transactions within my PL/SQL program will not work?

Question #2: What can do to get around this error? Opening and closing the cursor at smaller intervals instead of one big loop? Creating a separate rollback segment with its own separate tablespace and then using that new RBS for the big transaction?

Any help or thoughts are appreciated.

Thanks,

Dan

Happy New Year!

 

[AOLEURO]

Hi All

Oracle doesn’t consider ORA-1555 as a bug – but because of bad design in application and bad DBA work.

If you got an OTLP environment and you suddenly got a long running session, then you have not design your application right.

Oracle has a read consistency – something none other RDMBS has. If you want the same functionality in others RDBMS, then you need to lock transaction, and that’s bad because it drop performance and reduce concurrent sessions.

If you want to make a long running session, then you need to careful study the environment the long running session is working. A lot of times, you need to “feed” data from OTLP tables into temporary tables. In these tables you got complete control of data. You have none indexes and none constraints, triggers and so on to reduce speed and no latch. You got high speed and you do not slow down the OTLP sessions. Then you are ready to update/merge back into tables, you got speed and only slow down other sessions for a split of the time.

The first reply from Musafa – I have seen this before but you still have a bad design.



Regards
Allan
Icq: 346225948

 

[theshowmecanuck]

And if you have a large volume of data that needs to be fed into the temporary table you will have the same problem.

 

[AOLEURO]

No.

If Oracle has a problem with ORA-1550 they would have used energy to solve the problem – and if not Microsoft, IBM, Sybase and others would indeed be yelling the problem on all street counters.

Regards
Allan
Icq: 346225948

 

[theshowmecanuck]

Well...
one,
obviously there is a problem, or people would not be asking about it. Or are you saying people here are not intelligent. I would say there is a lot of experience here. A lot of the folks posting and replying here are not newbies. In fact, I would say that a good number probably have more experience with Oracle than either you or I.

two,
it is not Oracle's problem, it is the problem of the people trying to use their database.

three,
if you think this is incorrect, how about trying to provide a helpful answer to someone instead of just telling them they don't know what they're doing?

I for one need to pull large volumes of data from an OLTP database. Whether I place the data first in a temporary table or not, I still have to pull large volumes of data. This is a database that holds close to 20 million customers, with tables that hold tens or even hundreds of millions of rows. The database is active. My queries retrieve sometimes 10,000 rows, sometimes 200,000. Sometimes 0. No matter the size of the result set, sometimes I get this error, some times I do not.

Please explain to me how using temporary tables will help? And understand that I do use temp tables where they are useful to me.

 

[sbix]

Well Muf gave the best explanation of how rollbacks segs works I have ever seen and that's all.
AOLEURO is correct too, in the most of cases ORA-1555 depends on design lacks.
The Muf's solution is absolutely fantastic ... for whom can use.
I don't think is something magic ... and I bet nor Muf thinks something like.
Everybody's here trying to give good advises not to be the shinest star in the sky