| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: extremely long parse time
Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C268A7.EB28EC30"
------_=_NextPart_001_01C268A7.EB28EC30
Content-Type: text/plain;
charset="iso-8859-1"
OK, I think we're on to something here.
The DBA_TAB_COL_STATISTICS shows no rows for tables
owned by sys (although strangely, owner is not a column
of this table). However, the DBA_ANALYZE_OBJECTS view IS
listing objects owned by SYS, which implies that they have
been analyzed in the past.
Since I don't see any of the statisics filled in on the
DBA_TABLES entries for tables owned by SYS, what would
you recommend doing at this point?
"analyze table sys.XXXXX delete statistics"?
-----Original Message-----
Sent: Monday, September 30, 2002 1:27 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: Adams, Matthew (GEA, MABG, 088130)
Hmmm...anything SYS in DBA_TAB_COL_STATISTICS or DBA_ANALYZE_OBJECTS?
Is the COST column in your PLAN_TABLE null???
Rich Jesse System/Database Administrator Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
-----Original Message-----
Sent: Monday, September 30, 2002 12:38 PM
To: Multiple recipients of list ORACLE-L
We are using First_rows for the optimizer mode,
but the last_analyzed column in DBA_TABLES and
DBA_INDEXES is NULL for all objects owned by SYS.
The really wierd part is: Changing the query
to use rule based optimization (via the /*+ RULE */ hint
caused it to execute sub-second.
Why would optimization mode affect parsing? Is query
optimization considered part of the parsing routine?
Matt
------_=_NextPart_001_01C268A7.EB28EC30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2655.35">
<TITLE>RE: extremely long parse time</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>OK, I think we're on to something here.</FONT>
<BR><FONT SIZE=3D2>The DBA_TAB_COL_STATISTICS shows no rows for =
tables</FONT>
<BR><FONT SIZE=3D2>owned by sys (although strangely, owner is not a =
column</FONT>
<BR><FONT SIZE=3D2>of this table). However, the =
DBA_ANALYZE_OBJECTS view IS </FONT>
<BR><FONT SIZE=3D2>listing objects owned by SYS, which implies that =
they have</FONT>
<BR><FONT SIZE=3D2>been analyzed in the past.</FONT>
</P>
<P><FONT SIZE=3D2>Since I don't see any of the statisics filled in on =
the</FONT>
<BR><FONT SIZE=3D2>DBA_TABLES entries for tables owned by SYS, what =
would</FONT>
<BR><FONT SIZE=3D2>you recommend doing at this point? </FONT>
<BR><FONT SIZE=3D2>"analyze table sys.XXXXX delete =
statistics"?</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D2>----</FONT>
<BR><FONT SIZE=3D2>Matt Adams - GE Appliances - =
matt.adams_at_appl.ge.com</FONT>
<BR><FONT SIZE=3D2>Their fundamental design flaws are completely</FONT>
<BR><FONT SIZE=3D2>hidden by their superficial design flaws.</FONT>
<BR><FONT =
SIZE=3D2> - =
Douglas Adams</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Jesse, Rich [<A =
HREF=3D"mailto:Rich.Jesse_at_qtiworld.com">mailto:Rich.Jesse_at_qtiworld.com</=
A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Monday, September 30, 2002 1:27 PM</FONT>
<BR><FONT SIZE=3D2>To: 'ORACLE-L_at_fatcity.com'</FONT>
<BR><FONT SIZE=3D2>Cc: Adams, Matthew (GEA, MABG, 088130)</FONT>
<BR><FONT SIZE=3D2>Subject: RE: extremely long parse time</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Hmmm...anything SYS in DBA_TAB_COL_STATISTICS or =
DBA_ANALYZE_OBJECTS?</FONT>
</P>
<P><FONT SIZE=3D2>Is the COST column in your PLAN_TABLE null???</FONT>
</P>
<P><FONT SIZE=3D2>Rich =
Jesse &= nbsp; &= nbsp; System/Database Administrator</FONT>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Adams, Matthew (GEA, MABG, 088130) [<A =
HREF=3D"mailto:MATT.ADAMS_at_APPL.GE.COM">mailto:MATT.ADAMS_at_APPL.GE.COM</A>=
]</FONT>
<BR><FONT SIZE=3D2>Sent: Monday, September 30, 2002 12:38 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: RE: extremely long parse time</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>We are using First_rows for the optimizer mode, =
</FONT>
<BR><FONT SIZE=3D2>but the last_analyzed column in DBA_TABLES and =
</FONT>
<BR><FONT SIZE=3D2>DBA_INDEXES is NULL for all objects owned by SYS. =
</FONT>
<BR><FONT SIZE=3D2>The really wierd part is: Changing the query =
</FONT>
<BR><FONT SIZE=3D2>to use rule based optimization (via the /*+ RULE */ =
hint </FONT>
<BR><FONT SIZE=3D2>caused it to execute sub-second. </FONT>
<BR><FONT SIZE=3D2>Why would optimization mode affect parsing? Is =
query </FONT>
<BR><FONT SIZE=3D2>optimization considered part of the parsing routine? =
</FONT>
<BR><FONT SIZE=3D2>Matt </FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C268A7.EB28EC30--
------=_NextPartTM-000-dda17775-ead2-4e69-aa31-517474773ac9--
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Adams, Matthew (GEA, MABG, 088130) INET: MATT.ADAMS_at_APPL.GE.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Sep 30 2002 - 13:43:30 CDT
 |
 |