Re: Tunning question

Then do some 10053 traces and statistics comparisions to see where does the difference come from (also check init parameter differences from 10053 trace, not from v$parameter).
Note that retrieving 2611 rows can take 2 minutes longer for sorting, if the sorting is done intermediately with larger row sets, before final filtering or joins (but yeah, your stats did not show on-disk sorting so this isn't probably an issue)

Can't help you more without execution plans, Tanel.

"Adon Keber" <adon_at_cool.iskon.local> wrote in message news:slrnbmdhs6.1pc.adon_at_cool.iskon.local...
> On Mon, 15 Sep 2003 20:28:08 +0300, Tanel Poder wrote:
> >> Elapsed: 00:03:183.84
> >> Elapsed: 00:01:116.47
> >
> >Hm, since when is there over 60 seconds in a minute?
> >Posting your query and execution plans would be helpful.
> >
>
> Well, it seems it is a 'feature' in sqlplus.
> Number of seconds (eg. 183) is total number of seconds elapsed.
>
> Query is quite complex involving views and quite a few tables and
> execution plan is also large. But the key difference in
> execution plans is in only one place where there is the choice
> between two indexes.
> Optimizer chooses 'wrong' index and it takes 2 minutes longer than when
> the 'right' index is choosen.
> So my problem isn't query optimization. I just want optimizer to 'realize'
> the right execution plan.
>
> My key question is how faster query could have so much larger values
> for execution statistics (especially physical reads) than slower query.
> Because then I'll probably understand why the optimizer makes
> the wrong choice.
>
> a.
>
Received on Tue Sep 16 2003 - 04:41:05 CDT