Re: Max permutations
- From: Jared.Still@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 9 Feb 2004 11:07:24 -0800
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@xxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/09/2004 07:51 AM
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
