RE: optimizer time reported as?

  • From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
  • To: <mark.powell@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 11 Jan 2006 14:23:34 -0800

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 **


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
                                                                    e e
y o d
NAME                            VALUE                               f s
s d j
------------------------------- ----------------------------------- - -
- - -
optimizer_max_permutations      80000                               Y Y
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 -
        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 to
        Looking at the initora I noticed
        optimizer_max_permutations           integer
            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.
            Larry Wolfson
        The information contained in this communication is confidential,
        intended only for the use of the recipient named above, and may
        legally privileged.
        If the reader of this message is not the intended recipient, you
        hereby notified that any dissemination, distribution or copying
of this
        communication is strictly prohibited.
        If you have received this communication in error, please resend
        communication to the sender and delete the original message or
any copy
        of it from your computer system.
        Thank you.


Other related posts: