Re: Inconsistent SQL tuning results
Date: Tue, 8 Feb 2011 09:15:39 -0800
Message-ID: <AANLkTin3c65R9LWDEeajEioWro1oxE3T_xEYh57ipgQy_at_mail.gmail.com>
Ron,
Sorry, I should have been more clear. My test ran for 7 minutes and my test processes far fewer transaction records than the actual production run. The actual production run takes 5 hours and processes about 700,000 transactions.
Regards,
Mike
On Mon, Feb 7, 2011 at 10:03 PM, Ron Crisco <ron.crisco_at_method-r.com> wrote:
> Michael,
>
> 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-lReceived on Tue Feb 08 2011 - 11:15:39 CST