Re: Max permutations

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 09 Feb 2004 13:41:33 -0700
Message-Id: <6.0.3.0.2.20040209133612.02d69a10@pop.centrexcc.com>

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_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 - 14:41:33 CST