I just checked the autographed copy of OPT 101. Paraphrasing Gaja/Kirti's words, when this is set to a value below 80,000, the optimizer is forced to try upto eight different tables as the driving tables for queries that involve joins. OTOH, I was digging through some of my old email from another list (Oracle Apps) where _very_ complex and large table joins are common-place, someone reported that the time taken to execute (i.e. parse + fetch) was as below: optimizer_max_permutations = 50 Query Time = 42 sec. optimizer_max_permutations = 100 Query Time = 50 sec. optimizer_max_permutations = 500 Query Time = 3 min. optimizer_max_permutations = 1,000 Query Time = 6 min. optimizer_max_permutations = 10,000 Query Time = 55 min. optimizer_max_permutations = 50,000 Query Time = 117 min. optimizer_max_permutations = 80,000 Query Time = 219 min. Dunno why so many combinations were tested though! Nor do I have the SQL or explain plans... :( I do know however, that this was on 8.1.7.2 (i.e an erly version of 8.1.7) and that Oracle recommends a setting of 2000 for OMP in Oracle Apps 11i installs. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-----Original Message----- >From: oracle-l-bounce@xxxxxxxxxxxxx >[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling >Sent: Monday, February 09, 2004 12:42 PM >To: oracle-l@xxxxxxxxxxxxx >Subject: Re: Max permutations > > >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 >----------------------------------------------------------------- > ---------------------------------------------------------------- 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 -----------------------------------------------------------------