RE: optimizer time reported as?
- From: "Wolfson Larry - lwolfs" <lawrence.wolfson@xxxxxxxxxx>
- To: <mark.powell@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Jan 2006 16:22:19 -0600
Mark,
I didn't ask to have it changed yet. That's why I was asking if
time is accounted for in the Parsing SQL time and not somewhere else.
Another client has several applications where their test systems have
one setting and their production another.
I don't think that's particularly good for plan consistency. Especially
when they complain that code runs differently in production.
By the way, did the 2000 under 817 significantly help your 4031s?
Thanks
Larry
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: Wednesday, January 11, 2006 3: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
************************************************************************
*
The information contained in this communication is confidential,
is
intended only for the use of the recipient named above, and may
be
legally privileged.
If the reader of this message is not the intended recipient, you
are
hereby notified that any dissemination, distribution or copying
of this
communication is strictly prohibited.
If you have received this communication in error, please resend
this
communication to the sender and delete the original message or
any copy
of it from your computer system.
Thank you.
************************************************************************
*
Other related posts:
