Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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/
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 11 2006 - 23:46:21 CST
![]() |
![]() |