Re: Inconsistent SQL tuning results

If the entire task takes 5 hours to complete, and this one query only takes 7 minutes at worst case, why are you focusing on optimizing this query?
I would advise looking at a trace of the entire task and finding out what was happening during the other 293 minutes.

Of course, it's very possible that the same problem will show itself with all of the other queries in that task, but why guess?

Ron Crisco

>> Then I run the SQL, but the first time I run it, it can take as much as 7
>> minutes. On the 2nd, 3rd, and 4th runs, it takes
>> 40 sec, 49 sec, 35 sec respectively.
>>
>> So my question is: What might account for the huge difference in run time
>> between the first run and successive runs?
>>

> I wouldn't worry about the 7 minute run if this SELECT statement ran several
> times a day, but it does not. It runs once a month and the actual job is taking
> about 5 hours. I'm afraid that the 7 minute (first time) test is more analogous
> to what is likely to happen in prod. Anyway, I'll wait until tomorrow morning and
> see if I can reproduce the 7 min run.

--
http://www.freelists.org/webpage/oracle-l