Re: Query performance

Chuck wrote:
> I have a user running a query that runs in 20 minutes using one date range
> but takes hours and evenutally fails with ORA-1555 on a much smaller date
> range. The number of rows in the smaller date range is also much smaller.
> The query uses bind variables for the dates and the execution plan is
> identical for both date ranges. One of the tables contains hudreds of
> millions of rows and is partitioned by month. The smaller date range that
> returns the ORA-1555 also happens to include the current month. I suspect
> what's happening is that the current partition is being updated while the
> query is running and it's going back to the rollback segments to get the
> correct version of rows that changed after the query started. The ORA-1555
> is a dead giveaway to me. The problem is my manager wants more proof. Is
> there a session level statistic that will show how many times it had to go
> to the RBS to get the correct version of a row?

SELECTs do not "cause" ORA-01555 errors, but can be victims and fail by reporting the ORA-01555.

The cause of ORA-01555 are either INSERT, UPDATE or DELETE; typically along with "frequent" COMMITs from other sessions.

go to http://asktom.oracle.com
and do a keyword search on ORA-01555
Tom Kyte has done a great job of explaining this error! Received on Thu Apr 22 2004 - 19:36:25 CDT