Re: 10053 Trace File anomolies

<mccmx_at_hotmail.com> wrote in message
news:1119867821.243974.52230_at_z14g2000cwz.googlegroups.com...
> Oracle 9.2.0.6 EE on Win2K Adv Server SP3
>
> I have an SQL statement which runs in 2/3 seconds in one database and
> over 30 minutes in another environment.
>
> I have narrowed the problem down to the optimizer choosing 2 totally
> different plans between these 2 databases.
>
> It appears that the slow database is not selecting to use an index on a
> 1.8 million row table despite the fact that the 2 columns it contains
> are used in the where condition. If I force it to use this index via a
> hint, the plan and response time matches the fast database.
>
> In addition to this, the fast database only considers 25 possible join
> orders whereas the slow database tries over 195 combinations of join
> order.
>
> The 10053 trace files show no differences in optimizer parameters in
> the header of the file and they are both running the exact same Oracle
> version/patch. The row counts in each table are very close....
>
> Why would one database consider so many more join combinations than the
> other with the same optimizer parameters and row counts..?
>
> Thanks in advance
>
> Matt
>

In simple terms: if the optimizer thinks a query will complete in 3/100 of a second, and has already taken 3/100 of a second working
that out, then it won't spend more time trying to find a faster plan.

If your first join orders produce a very cheap execution plan, then the optimizer will not examine many more join orders. The more expensive the join is, the more orders will be examined.

In practical terms, the optimizer compares the best cost so far with the number of
join orders evaluated so far and stops
optimising when the number of join
orders examined exceeds some simple
function of the cost.

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated June 22nd 2005