RE: Max permutations
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Feb 2004 22:49:09 +0800
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
-----------------------------------------------------------------
Other related posts:
