| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Max permutations
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
>=20
>=20> >omp-80000) - the permutation sequences were
> >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
>=20
-- 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
 |
 |