RE: optimizer time reported as?

  • From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 11 Jan 2006 15:54:55 -0800

I replied offlist to Jonathan. The SQL joined 45 tables, which is
typical of some of the Oracle Apps queries. OA specifically requires you
to se opt-max-perm to 2000 8i and above, which was not done. (I was also
incorrect in the timing - the original query before tuning took 219
minutes to parse/execute - this dropped to about 6 minutes after the

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Wednesday, January 11, 2006 2:46 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: optimizer time reported as? 

When the query was taking 10 minutes to parse, did anyone check to find
out how many join orders were examined in each query block ?

I'd be interested to find out whether it was the number of join orders,
or whether the problem was actually something completely different.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Cost Based Oracle: Fundamentals
Public Appearances - schedule updated 10th Jan 2006

----- Original Message ----- 
From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
To: <mark.powell@xxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, January 11, 2006 10:23 PM
Subject: RE: optimizer time reported as? 

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
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **




Other related posts: