Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Max permutations

From: Thomas Biju <>
Date: Mon, 9 Feb 2004 16:45:57 -0600
Message-ID: <>

In one of our largest OLTP database we had several performance issues after=  upgrading to (the database is now When we went back and forth with Oracle support, the case was escalated and the develope= r suggested setting the OMP parameter to 2000. And that solved all the issues!! I don't know what it really means, but the default value o= f OMP is 2000 in 9i, whereas it is 80000 in 8i.


-----Original Message-----
From: Kirtikumar Deshpande [] Sent: Monday, February 09, 2004 4:37 PM
Subject: Re: Max permutations

 The book mentions any number less than 80,000. Gaja did some tests with 79= ,000 (with on
Solaris, I believe) to observe the CBO behavior. There is no "magic" number=  as such.=20

- Kirti=20
--- Wolfgang Breitling <> wrote:
> 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 eval=
> - 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" tha=
> 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
> > and where the only change
> >was the number of join orders examined
> >before the optimizer stopped (a few hundred
> >for omp =3D 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


Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

This electronic transmission and any attached files are intended solely for=
 the person or entity to which they are addressed and may contain informati=
on that is privileged, confidential or otherwise protected from disclosure.=
 Any review, retransmission, dissemination or other use, including taking a=
ny action concerning this information by anyone other than the named recipi=
ent, is strictly prohibited. If you are not the intended recipient or have =
received this communication in error, please immediately notify the sender =
and destroy this communication.
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Feb 09 2004 - 16:45:57 CST

Original text of this message