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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Wednesday, January 11, 2006 2:46 PM To: oracle-l_at_freelists.org
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" To: ; 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_,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 Received on Thu Jan 12 2006 - 00:54:55 CST