RE: Max permutations
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Feb 2004 12:21:07 -0800
Looked that way... In any case, from what I remember, Oracle recommended a
setting of 2000 and the problem was buried...
John
>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hemant K Chitale
>Sent: Wednesday, February 11, 2004 6:49 AM
>To: oracle-l@xxxxxxxxxxxxx
>Subject: RE: Max permutations
>
>
>
>why is query_time higher with higher o_m_p ?
>I can understand parse_time being higher.
>does a higher o_m_p result in a less-than-optimal query plan such that
>the query tiem actually goes up ?
>
>hemant
>
>At 02:49 PM 09-02-04 -0800, you wrote:
>>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
>>-----------------------------------------------------------------
>
>Hemant K Chitale
>Oracle 9i Database Administrator Certified Professional
>http://hkchital.tripod.com {last updated 24-Jan-04}
>
>
>----------------------------------------------------------------
>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
-----------------------------------------------------------------
Other related posts:
