Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Max permutations

Re: Max permutations

From: <Jared.Still_at_radisys.com>
Date: Mon, 9 Feb 2004 11:07:24 -0800
Message-ID: <OF1A44E674.30182301-ON88256E35.00689B04-88256E35.0068FB01@radisys.com>


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



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
-----------------------------------------------------------------




----------------------------------------------------------------
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US