Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query from Hell! - ora-01555

RE: Query from Hell! - ora-01555

From: Powell, Mark D <>
Date: Thu, 16 Nov 2006 09:25:56 -0500
Message-ID: <>

Whenever a developer would approach me with a select suffering from 1555 errors one of the first things I would do with them is try to determine how likely the 1555 was due to not being able to see a change made from a relatively recent or concurrent session rather than delayed block cleanout. If the change was believed to be recent, then the first step is tuning the query to make sure it is running in as little time as possible. The quicker a query runs, the less chance that the session will need to reconstruct data due to changes being made by concurrent sessions and if it needs to do so the less chance that the rollback data will have already been overlaid. Look at the explain plan and make sure the query is running as well as it can.

-----Original Message-----
[] On Behalf Of Hemant K Chitale Sent: Wednesday, November 15, 2006 9:00 PM To:; Subject: Re: Query from Hell! - ora-01555

8.1.7 with manual Rollback Segments :

The commit doesn't make a difference to your query. COMMIT only commits the previous transaction in your current session. However, your query is failing to get a Read Consistent Image (ie as of the same SCN) of the rows it requires from all the tables it is accessing. That means that either there are very many updates (update-and-commmit) to the underlying tables by *other* sessions between the time your query begins and the time it get's to the row which it is unable to reconstruct. Those *other* sessions should be using large rollback segments and/or there should be more rollback segments allocated for other transactions so that the probability of one transaction overwriting the committed RBS image of another transaction is reduced.
Also, try unsetting OPTIMAL for RBSs that those transaction use so taht Oracle does not automatically (too frequently) overwrite "released" extents.

Another option is to tune the query so that it runs that much faster that it is able to recreate the images of the rows it needs quickly enough.

Another option is to run the query when the underlying tables are not being updated or have much less DML.

There are more esoteric reasons as well --- delayed block cleanout, insufficient number of transaction slots in the rollback segments, transaction slots being overwritten etc.

You can only try to reduce the probability of ORA-1555s.

See Notes 10630.1 and 40689.1


At 04:34 AM Thursday, you wrote:
>I have a query and even though I set transaction to a specific rollback

>segment, it gives me an error on another rollback segment.
>I cannot cycle the instance at all to set rollback segment only to the
>larger one.
>We are using Version 8.1.7. I have been thinking of using temporary
>tables. Here is what I am doing, any suggestions?:
>set transaction use rollback segment large_rbs; SELECT l.licensee_id
> , li.license_id

Hemant K Chitale


Received on Thu Nov 16 2006 - 08:25:56 CST

Original text of this message