Re: Max permutations
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 09 Feb 2004 13:41:33 -0700
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@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:
