Re: Query performance

Two statistics - which will be very close to each other. The name of the second one is self-explanatory.

    consistent changes
    data blocks consistent reads - undo records applied

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"Chuck" <chuckh_nospam_at_softhome.net> wrote in message
news:Xns94D386D0BBA2Dchuckhsofthomenet_at_130.133.1.4...


> 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?

> -- 

> Chuck

> Remove "_nospam" to reply by email