Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query performance

Re: Query performance

From: AnaCDent <anacedent_at_hotmail.com>
Date: Thu, 22 Apr 2004 17:36:25 -0700
Message-ID: <eyZhc.86253$U83.53801@fed1read03>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US