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.
The Co-operative Oracle Users' FAQ
Cost Based Oracle: Fundamentals
Public Appearances - schedule updated 10th Jan 2006
----- Original Message -----
From: "John Kanagaraj" <john.kanagaraj@(protected)>
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 **