RE: optimizer time reported as?

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
change....

John
-----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.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
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
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 **

 


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: