John: I am not sure whether the parse time is directly related with number of permutations as we have some code optimizations around that area. The parameters like optimizer_search_limit and _new_initial_join_orders=true takes care some of them. And also to go to 80k permutations you need to have atleast 9 tables in that query which is quite uncommon in standard code . I would file a bug (!) if that happens again :D -Gopal On 1/11/06, John Kanagaraj <john.kanagaraj@xxxxxxx> wrote: > > 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 ** > > > ________________________________ > > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D > Sent: Wednesday, January 11, 2006 1:11 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: RE: optimizer time reported as? > > > I remember the default on 8.1.7 being 80,000 and Oracle support had us > lower it to 2,000 while we were working on an ORA-04031 iTAR. > > ora817 > @mon/parms > 'For all columns: Y = Yes/True N = No/False ' > ' D = Deferred, I = Immediate, S = System, U = User session' > ' Default indicator is unreliable if col SYS indicates chg ' > Enter value for parameter: optimizer_max_permutations > > D S > S M A > e e > y o d > NAME VALUE f s > s d j > ------------------------------- ----------------------------------- - - > - - - > DESCRIPTION > ---------------------------------------------------------------- > optimizer_max_permutations 80000 Y Y > N N N > optimizer maximum join permutations per query block > > > So how did the performance test turn out? > > HTH -- Mark D Powell -- > > > > ________________________________ > > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfson Larry - > lwolfs > Sent: Wednesday, January 11, 2006 4:01 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: optimizer time reported as? > > > I got asked about performance on a database that was recently > upgraded from 8.1.7.4 to 9.2.0.6. > > Looking at the initora I noticed > optimizer_max_permutations integer > 79999 > > The DBA doing the upgrade said she only changed the > parmaeters relating to the upgrade. > I know the default changed from 8's 80000 to 9's 2000 and there > was an earlier TAR telling us to change the > 80000 to 79999. > > I thought this might be an issue and I ran Tim Gorman's > sp_time script to see where the overhead was. > I just wanted to verify that the optimizer_max_permutations time > is accounted for in the > Parsing SQL time and not somewhere else. > > > TIA > Larry Wolfson > >