RE: Query from Hell! - ora-01555
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Nov 2006 09:25:56 -0500
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.
-- Mark D Powell --
Phone (313) 592-5148
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hemant K Chitale
Sent: Wednesday, November 15, 2006 9:00 PM
To: paulastankus@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
Hemant
At 04:34 AM Thursday, you wrote:
>Guys,
>
>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?:
>
>commit;
>set transaction use rollback segment large_rbs; SELECT l.licensee_id
> , li.license_id
>
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
