Find a better plan than that computed by the optimizer ??

[Spendius]

Hi,
Did any of you *ever* manage to find a better plan
than the optimizer by appropriately hinting your
query ?
The only time I succeeded in finding a lower cost
than it, it was (thanks to a 10053 trace) because
it reached the same cost for a NL and a HASH but
kept the nested loop in its final solution => I hinted
with the USE_HASH and it appeared that for the same
cost calculation the hash join was much quicker.

But can you be, sometimes, better than the optimizer,
could it be that it missed some join combinations or
whatever ?

Thanks a lot.
Spendius

[Jonathan Lewis]

There are various reasons that you can find
better plans than the optimizer.

First of all, the optimizer is programmed to
stop checking after a (variable) time limit,
so it may never reach the best plan.

Secondly, the optimizer has an algorithm
to generate a set of join orders for testing,
and may therefore skip over an order that
would be better than any order it will otherwise
generate. (This is, in part, a side-effect of the
time-limit above).

Third, the optimizer has a number of assumptions
built in that may not be true for your system, so
the best cost path it finds may be totally inappropriate
for the system you are running.

Finally, you know the business better than the optimizer,
so there may be some information available to you that
allows you to select a path that is technically unsuitable
as far as the optimizer is concerned.

--
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/seminar.html
Public Appearances - schedule updated Jan 21st 2005

"Spendius" <spen...@muchomail.com> wrote in message
news:aba30b75.05022...@posting.google.com...

[Spendius]

So how can you manage to find out -by looking at the result
of a 10053 trace- that the optimizer did miss something ?!?
(as long as of course you have factorial(N) join combinations,
with N as the number of tables in your FROM clause)
This is what I can't figure out...

Thanks.

"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote...

[Spendius]

For example how come you do not find any cost calculation
any further than the first attempt ?!? (we do have our
factorial(3) join combinations), why is there no more
"Best so far" line here ?:

# >egrep "^Best so f|^Join ord" bened01p_ora_22222.trc
Join order[1]: ABC_DEFG [DI] ABC [ D] ABC_DEF_GHI [DPE]
Best so far: TABLE#: 0 CST: 10158 CDN: 1269 BYTES: 29187
Best so far: TABLE#: 1 CST: 12696 CDN: 1269 BYTES: 88830
Best so far: TABLE#: 2 CST: 16523 CDN: 1279 BYTES: 106157
Join order[1]: ABC_DEFG [DI] ABC [ D] ABC_DEF_GHI [DPE]
Join order[2]: ABC_DEFG [DI] ABC_DEF_GHI [DPE] ABC [ D]
Join order[2]: ABC_DEFG [DI] ABC_DEF_GHI [DPE] ABC [ D]
Join order[3]: ABC [ D] ABC_DEFG [DI] ABC_DEF_GHI [DPE]
Join order[3]: ABC [ D] ABC_DEFG [DI] ABC_DEF_GHI [DPE]
Join order[4]: ABC [ D] ABC_DEF_GHI [DPE] ABC_DEFG [DI]
Join order[4]: ABC [ D] ABC_DEF_GHI [DPE] ABC_DEFG [DI]
Join order[5]: ABC_DEF_GHI [DPE] ABC_DEFG [DI] ABC [ D]
Join order[5]: ABC_DEF_GHI [DPE] ABC_DEFG [DI] ABC [ D]
Join order[6]: ABC_DEF_GHI [DPE] ABC [ D] ABC_DEFG [DI]
Join order[6]: ABC_DEF_GHI [DPE] ABC [ D] ABC_DEFG [DI]

[Jonathan Lewis]

The first join order always has a 'Best so far'
because it is the only join so far. After that,
'Best so far' should only appear when a better
(cheaper) join order is found. In this case,
it looks as if the first one checked was the
best.

For a three table join, I can spare the time to
have a quick look at the trace file if you think
it is odd. Feel free to email it direct to me.

--
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/seminar.html
Public Appearances - schedule updated Jan 21st 2005

"Spendius" <spen...@muchomail.com> wrote in message
news:aba30b75.05022...@posting.google.com...