optimizer time reported as?

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

optimizer time reported as?

optimizer time reported as?

2006-01-11 - By Powell, Mark D

Reply: 1 2 3 4 5 6 7 8 9 10

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 (See ORA-04031.ora-code.com) iTAR.

ora817 > @(protected)/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@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Wolfson Larry -
lwolfs
Sent: Wednesday, January 11, 2006 4:01 PM
To: oracle-l@(protected)
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.

************************************************************************
*

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Re: Rman/NetBackup problem</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=090070821-11012006>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 (See ORA-04031.ora-code.com)
iTAR.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=090070821-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face="Courier New" color=#0000ff size=2><SPAN
class=090070821-11012006>ora817 > @(protected)/parms<BR>'For all columns: Y =
Yes/True    N = No/False '<BR>' D = Deferred,  I =
Immediate,  S = System,  U = User session'<BR>' Default indicator is
unreliable if col SYS indicates chg '<BR>Enter value for parameter:
optimizer_max_permutations</SPAN></FONT></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=090070821-11012006><FONT
face="Courier New">          
             
             
             
             
     
D S S M
A<BR>            
             
             
             
             
   
e e y o
d<BR>NAME           
             
   
VALUE            
             
     
f s s d j<BR>-- ---- ---- ---- ---- ---- --- -- ---- ---- ---- ---- ---- ------
-
- - - -
-<BR>DESCRIPTION<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- --<BR>optimizer_max_permutations     
80000            
             
     
Y Y N N N<BR>optimizer maximum join permutations per query
block</FONT><BR></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=090070821-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=090070821-11012006>So how did the performance test turn
out?</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=090070821-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=090070821-11012006>HTH -- Mark D Powell --</DIV></SPAN></FONT><BR>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Wolfson Larry -
lwolfs<BR><B>Sent:</B> Wednesday, January 11, 2006 4:01 PM<BR><B>To:</B>
oracle-l@(protected)<BR><B>Subject:</B> optimizer time reported as?
<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>I got asked about performance on a database that was
recently upgraded from 8.1.7.4 to 9.2.0.6.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>Looking at the initora I noticed</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>optimizer_max_permutations    
      
integer           
             
 
79999</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>    The DBA doing the upgrade said
she
only changed the parmaeters relating to the upgrade.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>I know the default changed from 8's 80000 to 9's
2000
and there was an earlier TAR telling us to change the </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>80000 to 79999.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>    I thought this might be an issue
and I ran Tim Gorman's sp_time script to see where the overhead
was.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>I just wanted to verify that the
optimizer_max_permutations time is accounted for in
the </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>Parsing SQL time and not somewhere
else.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>    TIA</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>    Larry Wolfson</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=350444120-11012006>    </SPAN></FONT></DIV><PRE>********
*****************************************************************
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.
*************************************************************************
</PRE></BLOCKQUOTE></BODY></HTML>