Message-ID: <35CFD500D7BDCE43B9030BBA5979DC181D92BE@ussccem13.hds.com>
From: John Kanagaraj <john.kanagaraj@hds.com>
To: "'oracle-l@freelists.org'" <oracle-l@freelists.org>
Subject: RE: Max permutations
Date: Mon, 9 Feb 2004 14:49:45 -0800 
MIME-Version: 1.0
Content-type: text/plain
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-To: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle-l@freelists.org

I just checked the autographed copy of OPT 101. Paraphrasing Gaja/Kirti's
words, when this is set to a value below 80,000, the optimizer is forced to
try upto eight different tables as the driving tables for queries that
involve joins. 

OTOH, I was digging through some of my old email from another list (Oracle
Apps) where _very_ complex and large table joins are common-place, someone
reported that the time taken to execute (i.e. parse + fetch) was as below:

optimizer_max_permutations = 50             Query Time = 42  sec.
optimizer_max_permutations = 100           Query Time = 50  sec.
optimizer_max_permutations = 500           Query Time = 3    min.
optimizer_max_permutations = 1,000        Query Time = 6    min.
optimizer_max_permutations = 10,000      Query Time = 55   min.
optimizer_max_permutations = 50,000      Query Time = 117 min.
optimizer_max_permutations = 80,000      Query Time = 219 min.

Dunno why so many combinations were tested though! Nor do I have the SQL or
explain plans... :(

I do know however, that this was on 8.1.7.2 (i.e an erly version of 8.1.7)
and that Oracle recommends a setting of 2000 for OMP in Oracle Apps 11i
installs.	

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-----Original Message-----
>From: oracle-l-bounce@freelists.org 
>[mailto:oracle-l-bounce@freelists.org] On Behalf Of Wolfgang Breitling
>Sent: Monday, February 09, 2004 12:42 PM
>To: oracle-l@freelists.org
>Subject: Re: Max permutations
>
>
>Dave Ensor, in his presentation at UKOUG claims
>- reduce the setting (say to 40,320) and
>   - Oracle uses an enhanced strategy to decide which join 
>orders to evaluate
>   - very significantly increases the probability of picking 
>the correct 
>driving table for a join of more than 8 tables
>   - decreases parse time for joins of more than 8 tables
>
>Gaja, Kirti, et al. also claim in their book "Performance 
>Tuning 101" that 
>OMP 79,999 and 79,998 have "magic" powers. That was for Oracle 8.
>
>That last piece is from memory, so it may not be correct. The 
>book is at 
>home and I'm at a client's site.
>Kirti, care to confirm/deny/comment.
>
>At 08:51 AM 2/9/2004, you wrote:
>
>>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.
>
>Wolfgang Breitling
>Oracle7, 8, 8i, 9i OCP DBA
>Centrex Consulting Corporation
>http://www.centrexcc.com 
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to:  oracle-l-request@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@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
-----------------------------------------------------------------