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: Thomas Biju <BThomas_at_br-inc.com>
Date: Mon, 9 Feb 2004 16:45:57 -0600
Message-ID: <75DFF91B31A1284992092BDB70C9232401ABDEF4@ftwp45ns.br-inc.net>


In one of our largest OLTP database we had several performance issues after=  upgrading to 8.1.7.1 (the database is now 8.1.7.4). 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.

-Biju

-----Original Message-----
From: Kirtikumar Deshpande [mailto:kirtikumar_deshpande_at_yahoo.com] Sent: Monday, February 09, 2004 4:37 PM
To: oracle-l_at_freelists.org
Subject: Re: Max permutations

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

Regards,
- Kirti=20
=20
--- Wolfgang Breitling <breitliw_at_centrexcc.com> 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=
uate
> - 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
>=20
> Gaja, Kirti, et al. also claim in their book "Performance Tuning 101" tha=
t=20
> OMP 79,999 and 79,998 have "magic" powers. That was for Oracle 8.

>=20

> 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.
>=20

> At 08:51 AM 2/9/2004, you wrote:
>=20

> >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 =3D 2000, a couple of thousand for
> >omp-80000) - the permutation sequences were
> >was identical.
>=20

> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com=20

>=20
>

Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.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
-----------------------------------------------------------------




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: 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 - 16:45:57 CST

Original text of this message

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