 | | | optimizer time reported as? | optimizer time reported as? 2006-01-11 - By K Gopalakrishnan
John:
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>
|
|
|