| 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_01C268F2.3CDCC1B0"
------_=_NextPart_001_01C268F2.3CDCC1B0
Content-Type: text/plain;
charset="iso-8859-1"
Matt,
optimizer_mode = FIRST_ROWS means CBO, and for SYS schema without statistics.
Oracle 8.1.7.2 on HP-UX 11.
optimizer_mode = CHOOSE
I run your original SELECT, then with the hint FIRST_ROWS.
>From trace file:
PARSING IN CURSOR #1 len=888 dep=0 uid=20 oct=3 lid=20 tim=264275830
hv=275513964 ad='a1e7360'
select
...
END OF STMT
PARSE #1:c=37,e=271,p=7,cr=19,cu=0,mis=1,r=0,dep=0,og=4,tim=264275831
...
PARSING IN CURSOR #1 len=906 dep=0 uid=20 oct=3 lid=20 tim=264293190
hv=3966396081 ad='bfbf750'
select /*+ FIRST_ROWS */
...
END OF STMT
PARSE #1:c=13234,e=13619,p=1,cr=58,cu=0,mis=1,r=0,dep=0,og=2,tim=264293190
Look at og
Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose
In my case RBO took 2.71 sec, CBO 136.19 sec.
Alex.
-----Original Message-----
Sent: Monday, September 30, 2002 10:38 AM
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
-----Original Message-----
Sent: Monday, September 30, 2002 11:36 AM
To: 'ORACLE-L_at_fatcity.com'
Cc: Adams, Matthew (GEA, MABG, 088130)
Hmmmm, also running on 8.1.7.2 on HPUX 11, this comes back to me sub-second.
My explain plan pukes in TOAD, but that's a TOAD issue...everything looks good in SQL*Plus.
1) Are you using CBO? 2) If "yes" from 1, verify that there are no stats gathered in SYS. 3) Try init.ora "optimizer_max_permutations = 2000". The default is "80000"
in 8 and 8i and "2000" in 9i. Aside from cursor_sharing=force, that's the only parameter I have that I think could affect parse times that severely. I also have:
optimizer_index_caching = 90
optimizer_index_cost_adj = 50
Rich Jesse System/Database Administrator Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
-----Original Message-----
Sent: Monday, September 30, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L
Nope, Oracle 8.1.7.2 on HP-UX 11. 148 seconds on the wall clock to parse:
select
null as table_cat, owner as table_schem, table_name,
0 as NON_UNIQUE, null as index_qualifier, null as index_name,
0 as type, 0 as ordinal_position, null as column_name, null as asc_or_desc,
num_rows as cardinality, blocks as pages, null as filter_condition
from all_tables
where table_name = 'INDEXENTRIES'
union
select null as table_cat, i.owner as table_schem, i.table_name, decode (i.uniqueness, 'UNIQUE', 0, 1), null as index_qualifier, i.index_name, 1 as type, c.column_position as ordinal_position,c.column_name, null as asc_or_desc, i.distinct_keys as cardinality, i.leaf_blocks as pages, null as filter_condition from all_indexes i,
i.index_name = c.index_name and i.table_owner = c.table_owner and i.table_name = c.table_name and i.owner = c.index_owner
Matt,
Is it Oracle 9?
If yes, time is in microseconds.
Alex.
-----Original Message-----
Sent: Thursday, September 26, 2002 10:30 AM
To: Multiple recipients of list ORACLE-L
why would a query take 148 seconds to
parse? It is a two way union
where the first half is going against all_tables
and the second half is a join
between all_indexes and all_ind_columns.
The shared pool has 50M large parts of it are
free. I generated a 10046 (level 12) trace, and I just
don't see anything out of whack, except of the
c=14868 in the PARSE #1 line.
------_=_NextPart_001_01C268F2.3CDCC1B0
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">
<TITLE>RE: extremely long parse time</TITLE> </HEAD> <BODY>
<P><FONT SIZE=3D2>Matt,</FONT>
</P>
<P><FONT SIZE=3D2>optimizer_mode =3D FIRST_ROWS means CBO, and for SYS =
schema without statistics.</FONT>
</P>
<P><FONT SIZE=3D2>Oracle 8.1.7.2 on HP-UX 11.</FONT> <BR><FONT SIZE=3D2>optimizer_mode =3D CHOOSE</FONT> </P>
<P><FONT SIZE=3D2>I run your original SELECT, then with the hint =
FIRST_ROWS.</FONT>
</P>
<P><FONT SIZE=3D2>From trace file:</FONT> </P>
<P><FONT SIZE=3D2>PARSING IN CURSOR #1 len=3D888 dep=3D0 uid=3D20 = oct=3D3 lid=3D20 tim=3D264275830 hv=3D275513964 ad=3D'a1e7360'</FONT>
<BR><FONT SIZE=3D2>select</FONT> <BR><FONT SIZE=3D2>...</FONT> <BR><FONT SIZE=3D2>END OF STMT</FONT> <BR><FONT SIZE=3D2>PARSE =
<BR><FONT SIZE=3D2>select /*+ FIRST_ROWS */</FONT> <BR><FONT SIZE=3D2>...</FONT> <BR><FONT SIZE=3D2>END OF STMT</FONT> <BR><FONT SIZE=3D2>PARSE =
<P><FONT SIZE=3D2>Look at og</FONT>
<BR><FONT SIZE=3D2>Optimizer goal: 1=3DAll_Rows, 2=3DFirst_Rows, =
3=3DRule, 4=3DChoose</FONT>
</P>
<P><FONT SIZE=3D2>In my case RBO took 2.71 sec, CBO 136.19 sec.</FONT> </P>
<P><FONT SIZE=3D2>Alex.</FONT>
</P>
<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 10:38 AM</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>optimization considered part of the parsing routine? = </FONT> <BR><FONT SIZE=3D2>Matt </FONT> <BR><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 11:36 AM </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>
<P><FONT SIZE=3D2>Hmmmm, also running on 8.1.7.2 on HPUX 11, this comes =
back to me sub-second. </FONT>
<BR><FONT SIZE=3D2>My explain plan pukes in TOAD, but that's a TOAD =
issue...everything looks </FONT>
<BR><FONT SIZE=3D2>good in SQL*Plus. </FONT> <BR><FONT SIZE=3D2>1) Are you using CBO? </FONT> <BR><FONT SIZE=3D2>2) If "yes" from 1, verify that there are =no stats gathered in SYS. </FONT>
<BR><FONT SIZE=3D2>there shouldn't be stats on the data dictionary. = </FONT> <BR><FONT SIZE=3D2>HTH! GL! :) </FONT> <BR><FONT SIZE=3D2>Rich = Jesse &=nbsp; &= nbsp; System/Database Administrator </FONT> <BR><FONT =
<BR><FONT SIZE=3D2>Sent: Monday, September 30, 2002 10:04 AM </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>Nope, Oracle 8.1.7.2 on HP-UX 11. 148 seconds = </FONT> <BR><FONT SIZE=3D2>on the wall clock to parse: </FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2>select </FONT> <BR><FONT SIZE=3D2>null as table_cat, owner as table_schem, table_name, =</FONT>
<BR><FONT SIZE=3D2>0 as NON_UNIQUE, null as index_qualifier, null as = index_name, </FONT> <BR><FONT SIZE=3D2>0 as type, 0 as ordinal_position, null as =column_name, null as asc_or_desc, </FONT> <BR><FONT SIZE=3D2>num_rows as cardinality, blocks as pages, null as = filter_condition </FONT>
<BR><FONT SIZE=3D2>from all_tables </FONT> <BR><FONT SIZE=3D2>where table_name =3D 'INDEXENTRIES' </FONT> <BR><FONT SIZE=3D2>union </FONT> <BR><FONT SIZE=3D2>select null as table_cat, i.owner as table_schem, =i.table_name, </FONT>
<BR><FONT SIZE=3D2>from all_indexes i, </FONT> <BR><FONT SIZE=3D2>all_ind_columns c </FONT> <BR><FONT SIZE=3D2>where i.table_name =3D 'INDEXENTRIES' and </FONT> <BR><FONT SIZE=3D2>i.index_name =3D c.index_name and </FONT> <BR><FONT SIZE=3D2>i.table_owner =3D c.table_owner and </FONT> <BR><FONT SIZE=3D2>i.table_name =3D c.table_name and </FONT> <BR><FONT SIZE=3D2>i.owner =3D c.index_owner </FONT> <BR><FONT SIZE=3D2>order by non_unique, type, index_name, =ordinal_position </FONT>
<BR><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 =
<BR><FONT SIZE=3D2>-----Original Message----- </FONT> <BR><FONT SIZE=3D2>From: Alexander.Feinstein_at_mitchell1.com </FONT> <BR><FONT SIZE=3D2>[<A =
<BR><FONT SIZE=3D2>Sent: Friday, September 27, 2002 4:28 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>Matt, </FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2>Is it Oracle 9? </FONT> <BR><FONT SIZE=3D2>If yes, time is in microseconds. </FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2>Alex. </FONT> <BR><FONT SIZE=3D2> </FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, September 26, 2002 10:30 AM </FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L </FONT> <BR><FONT SIZE=3D2>Subject: extremely long parse time </FONT> </P> <BR> <P><FONT SIZE=3D2>why would a query take 148 seconds to </FONT> <BR><FONT SIZE=3D2>parse? It is a two way union </FONT> <BR><FONT SIZE=3D2>where the first half is going against all_tables = </FONT> <BR><FONT SIZE=3D2>and the second half is a join </FONT> <BR><FONT SIZE=3D2>between all_indexes and all_ind_columns. </FONT> <BR><FONT SIZE=3D2>The shared pool has 50M large parts of it are =</FONT>
<BR><FONT SIZE=3D2>don't see anything out of whack, except of the = </FONT> <BR><FONT SIZE=3D2>c=3D14868 in the PARSE #1 line. </FONT> </P>
</BODY>
</HTML>
------_=_NextPart_001_01C268F2.3CDCC1B0--
------=_NextPartTM-000-b6b96d48-d496-11d6-984b-0008c79fc2b3--
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Alexander.Feinstein_at_mitchell1.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 - 22:28:20 CDT
 |
 |