| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Max permutations
Jonathan,
Here are join orders from a system where we normally run O_M_P = 1000 due
to excessive parse times on certain views.
These are all join orders (11) from the 1k file and the first 11 from the 80k file.
Notice that the 1k considers a join on FACILITY that is not seen in the 80k file.
This join order is not considered in the 80k trace file until join order #61
Jared
O_M_P=1000
Join order[1]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] PRODUCT [ZZ]
Join order[2]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] WIP_CONTENT_REF [ K]
Join order[3]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] PRODUCT [ZZ]
WIP_SERIAL_NO [ J]
Join order[4]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] PRODUCT [ZZ]
Join order[5]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] WIP_CONTENT_REF [ K]
Join order[6]: FACILITY [PP] SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J]
Join order[1]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] PRODUCT [ZZ]
Join order[2]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] PRODUCT [ZZ]
WIP_SERIAL_NO [ J]
Join order[3]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] PRODUCT [ZZ]
Join order[4]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] PRODUCT [ZZ]
MATERIAL_ORDER_CONTENT [ E]
Join order[5]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] PRODUCT [ZZ] MATERIAL_ORDER_CONTENT [ E]
WIP_SERIAL_NO [ J]
O_M_P=80000
Join order[1]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] PRODUCT [ZZ]
Join order[2]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] WIP_CONTENT_REF [ K]
Join order[3]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] PRODUCT [ZZ]
WIP_SERIAL_NO [ J]
Join order[4]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] PRODUCT [ZZ]
Join order[5]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] WIP_CONTENT_REF [ K]
Join order[1]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] PRODUCT [ZZ]
Join order[2]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] PRODUCT [ZZ]
WIP_SERIAL_NO [ J]
Join order[3]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] PRODUCT [ZZ]
Join order[4]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] PRODUCT [ZZ]
MATERIAL_ORDER_CONTENT [ E]
Join order[5]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] PRODUCT [ZZ] MATERIAL_ORDER_CONTENT [ E]
WIP_SERIAL_NO [ J]
Join order[6]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] PRODUCT [ZZ] WIP_SERIAL_NO [ J]
MATERIAL_ORDER_CONTENT [ E]
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: oracle-l-bounce_at_freelists.org
02/09/2004 07:51 AM
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: Max permutations
Has anyone done any recent testing on the effect of optimizer_max_permutations.
I recall seeing a note on metalink once said the CBO would change the way in which it permuted join orders if the parameter was set to any value other than 80,000. I'm also fairly sure that I ran up a test a few years ago that demonstrated this effect.
However, I've just run up a simple test on
8.1.7.4 and 9.2.0.4 where the only change
was the number of join orders examined
before the optimizer stopped (a few hundred
for omp = 2000, a couple of thousand for
omp-80000) - the permutation sequences were
was identical.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Feb 09 2004 - 13:07:24 CST
 |
 |