I am not sure whether the parse time is directly related with number of permutations as we have some code optimizations around that area. The parameters like optimizer_search_limit and _new_initial_join_orders=true takes care some of them. And also to go to 80k permutations you need to have atleast 9 tables in that query which is quite uncommon in standard code .
I would file a bug (!) if that happens again :D
-Gopal
On 1/11/06, John Kanagaraj <john.kanagaraj@(protected)> wrote: > > Note that in 10g, this parameter is hidden (becomes > "_optimizer_max_permutations"), and the value is defaulted to 2000. > > Oracle seems to have noticed that the large default value in 8i was > probably incorrect. At least for Oracle's E-Biz Suite (Apps 11i), even > that based on a 8i database, this value necessarily needs to be set to > 2000. I had helped someone (albeit on an Apps database) debug a query > that took 10 mins to parse (and less than a min to execute!)- the > parameter was incorrectly set to default and the query parsed and > performed under a minute. > > So go ahead and satisfy our curiousity... What did sp_time (or is it > sp_systime_<Version>,sql!) point to? > > John Kanagaraj <>< > DB Soft Inc > Phone: 408-970-7002 (W) > > Co-Author: Oracle Database 10g Insider Solutions > http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/ > > ** The opinions and facts contained in this message are entirely mine > and do not reflect those of my employer or customers ** > > > __ ____ ____ ____ ____ ____ ____ > > From: oracle-l-bounce@(protected) > [mailto:oracle-l-bounce@(protected)] On Behalf Of Powell, Mark D > Sent: Wednesday, January 11, 2006 1:11 PM > To: oracle-l@(protected) > Subject: RE: optimizer time reported as? > > > I remember the default on 8.1.7 being 80,000 and Oracle support had us > lower it to 2,000 while we were working on an ORA-04031 (See ORA-04031.ora-code.com) iTAR. > > ora817 > @(protected)/parms > 'For all columns: Y = Yes/True N = No/False ' > ' D = Deferred, I = Immediate, S = System, U = User session' > ' Default indicator is unreliable if col SYS indicates chg ' > Enter value for parameter: optimizer_max_permutations > > D S > S M A > e e > y o d > NAME VALUE f s > s d j > -- ---- ---- ---- ---- ---- --- -- ---- ---- ---- ---- ---- ---- -- - - > - - - > DESCRIPTION > -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ > optimizer_max_permutations 80000 Y Y > N N N > optimizer maximum join permutations per query block > > > So how did the performance test turn out? > > HTH -- Mark D Powell -- > > > > __ ____ ____ ____ ____ ____ ____ > > From: oracle-l-bounce@(protected) > [mailto:oracle-l-bounce@(protected)] On Behalf Of Wolfson Larry - > lwolfs > Sent: Wednesday, January 11, 2006 4:01 PM > To: oracle-l@(protected) > Subject: optimizer time reported as? > > > I got asked about performance on a database that was recently > upgraded from 8.1.7.4 to 9.2.0.6. > > Looking at the initora I noticed > optimizer_max_permutations integer > 79999 > > The DBA doing the upgrade said she only changed the > parmaeters relating to the upgrade. > I know the default changed from 8's 80000 to 9's 2000 and there > was an earlier TAR telling us to change the > 80000 to 79999. > > I thought this might be an issue and I ran Tim Gorman's > sp_time script to see where the overhead was. > I just wanted to verify that the optimizer_max_permutations time > is accounted for in the > Parsing SQL time and not somewhere else. > > > TIA > Larry Wolfson > >
John:<br><br>I am not sure whether the parse time is directly related with number of permutations as we have some code optimizations around that area. The parameters like optimizer_search_limit and _new_initial_join_orders=true takes care some of them. And also to go to 80k permutations you need to have atleast 9 tables in that query which is quite uncommon in standard code . <br><br>I would file a bug (!) if that happens again :D<br><br>-Gopal<br><br> <br><div><span class="gmail_quote">On 1/11/06, <b class="gmail_sendername">John Kanagaraj</b> <<a href="mailto:john.kanagaraj@(protected)">john.kanagaraj@(protected) .com </a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Note that in 10g, this parameter is hidden (becomes<br>"_optimizer_max _permutations"), and the value is defaulted to 2000. <br><br>Oracle seems to have noticed that the large default value in 8i was<br >probably incorrect. At least for Oracle's E-Biz Suite (Apps 11i), even<br>that based on a 8i database, this value necessarily needs to be set to <br>2000. I had helped someone (albeit on an Apps database) debug a query<br >that took 10 mins to parse (and less than a min to execute!)- the<br>parameter was incorrectly set to default and the query parsed and<br>performed under a minute. <br><br>So go ahead and satisfy our curiousity... What did sp_time (or is it<br >sp_systime_<Version>,sql!) point to?<br><br>John Kanagaraj <>< <br>DB Soft Inc<br>Phone: 408-970-7002 (W)<br><br>Co-Author: Oracle Database 10g Insider Solutions <br><a href="http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/">http:/ /www.amazon.com/exec/obidos/tg/detail/-/0672327910/</a><br><br>** The opinions and facts contained in this message are entirely mine<br>and do not reflect those of my employer or customers ** <br><br><br>__ ____ ____ ____ ____ ____ ____<br><br>From: <a href="mailto :oracle-l-bounce@(protected)">oracle-l-bounce@(protected)</a><br>[mailto:<a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected) </a>] On Behalf Of Powell, Mark D<br>Sent: Wednesday, January 11, 2006 1:11 PM <br>To: <a href="mailto:oracle-l@(protected)">oracle-l@(protected)</a><br >Subject: RE: optimizer time reported as?<br><br><br>I remember the default on 8.1.7 being 80,000 and Oracle support had us<br>lower it to 2,000 while we were working on an ORA-04031 (See ORA-04031.ora-code.com) iTAR.<br><br>ora817 > @(protected)/parms<br>'For all columns : Y = Yes/True N = No/False '<br>' D = Deferred, I = Immediate, S = System, U = User session' <br>' Default indicator is unreliable if col SYS indicates chg '<br>Enter value for parameter: optimizer_max_permutations<br><br> D S<br>S M A <br> e e <br>y o d<br>NAME VALUE f s<br>s d j<br>--- -- ---- ---- ---- ---- ----- -- ---- ---- ---- ---- ---- ---- -- - -<br>- - -<br >DESCRIPTION<br>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <br>optimizer_max_permutations 80000 Y Y<br>N N N<br>optimizer maximum join permutations per query block<br><br><br>So how did the performance test turn out?<br><br>HTH -- Mark D Powell -- <br><br><br><br>__ ____ ____ ____ ____ ____ ____<br><br>   ; From: <a href="mailto:oracle-l-bounce@(protected)" >oracle-l-bounce@(protected)</a><br>[mailto:<a href="mailto:oracle-l-bounce @(protected)">oracle-l-bounce@(protected) </a>] On Behalf Of Wolfson Larry -<br>lwolfs<br> Sent: Wednesday, January 11, 2006 4:01 PM<br>   ; To: <a href="mailto:oracle-l@(protected)" >oracle-l@(protected)</a><br>   ;Subject: optimizer time reported as? <br><br><br> I got asked about performance on a database that was recently<br>upgraded from <a href="http://8 .1.7.4">8.1.7.4</a> to <a href="http://9.2.0.6">9.2.0.6</a>.<br><br> Looking at the initora I noticed <br> optimizer_max_permutations integer<br>79999<br ><br> The DBA doing the upgrade said she only changed the<br>parmaeters relating to the upgrade.<br> I know the default changed from 8's 80000 to 9's 2000 and there <br>was an earlier TAR telling us to change the<br> 80000 to 79999.<br><br> I thought this might be an issue and I ran Tim Gorman's<br>sp_time script to see where the overhead was.<br> I just wanted to verify that the optimizer_max_permutations time <br>is accounted for in the<br>   ;Parsing SQL time and not somewhere else.<br><br><br> TIA<br> Larry Wolfson<br><br>< /blockquote></div><br>
