Re: optimizer time reported as?

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 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_,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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
> Sent: Wednesday, January 11, 2006 1:11 PM
> To: oracle-l_at_freelists.org
> 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 iTAR.
>
> ora817 > @mon/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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfson Larry -
> lwolfs
> Sent: Wednesday, January 11, 2006 4:01 PM
> To: oracle-l_at_freelists.org
> 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
>
>
Received on Thu Jan 12 2006 - 00:39:32 CST