Re: Max permutations
- From: Kirtikumar Deshpande <kirtikumar_deshpande@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 9 Feb 2004 14:37:07 -0800 (PST)
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.
Regards,
- Kirti
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> 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 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
>
>
__________________________________
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
