Re: Query performance

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?

I've found watching these things with the script in metalink note 1039126.6 can be informative.

Your manager may get frustrated and just tell you to make the RBS bigger anyways. You aren't using OPTIMAL, are you?

jg

--
@home.com is bogus.
http://story.news.yahoo.com/news?tmpl=story&cid=528&e=2&u=/ap/20040421/ap_on_sc/lemelson_mit_award