Re: Query from Hell! - ora-01555
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: paulastankus@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 16 Nov 2006 10:00:00 +0800
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
Other related posts:
