Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: extremely long parse time

RE: extremely long parse time

From: Adams, Matthew (GEA, MABG, 088130) <MATT.ADAMS_at_APPL.GE.COM>
Date: Mon, 30 Sep 2002 07:03:33 -0800
Message-ID: <F001.004DC6D6.20020930070333@fatcity.com>


Content-Type: multipart/alternative;
 boundary="----_=_NextPart_001_01C26888.B30E1722" ------_=_NextPart_001_01C26888.B30E1722
Content-Type: text/plain;
 charset="iso-8859-1"

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,
all_ind_columns c
where i.table_name = 'INDEXENTRIES' and
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

order by non_unique, type, index_name, ordinal_position

Matt Adams - GE Appliances - matt.adams_at_appl.ge.com Their fundamental design flaws are completely hidden by their superficial design flaws.

-----Original Message-----
[mailto:Alexander.Feinstein_at_mitchell1.com] Sent: Friday, September 27, 2002 4:28 PM To: Multiple recipients of list ORACLE-L

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.



Matt Adams - GE Appliances - matt.adams_at_appl.ge.com Their fundamental design flaws are completely hidden by their superficial design flaws.

------_=_NextPart_001_01C26888.B30E1722
Content-Type: text/html;
 charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>extremely long parse time</TITLE>

<META content="MSHTML 5.00.3105.105" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face="Courier New" size=2><SPAN
class=530315513-30092002>Nope, Oracle 8.1.7.2 on HP-UX 11.&nbsp; 148 seconds</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face="Courier New" size=2><SPAN
class=530315513-30092002>on the wall clock to parse:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2><SPAN

class=530315513-30092002></SPAN></FONT>&nbsp;</DIV>

<DIV><FONT color=#0000ff size=2><SPAN

class=530315513-30092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT color=#0000ff face=Arial size=2>select </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>null as table_cat, owner as
table_schem, table_name, </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>0 as NON_UNIQUE, null as
index_qualifier, null as index_name, </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>0 as type, 0 as ordinal_position,
null as column_name, null as asc_or_desc, </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>num_rows as cardinality, blocks as
pages, null as filter_condition</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>from all_tables</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>where table_name =
'INDEXENTRIES'</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>union </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>select null as table_cat, i.owner as
table_schem, i.table_name, </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>decode (i.uniqueness, 'UNIQUE', 0,
1), null as index_qualifier, </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>i.index_name, 1 as type,
c.column_position as ordinal_position, </FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>c.column_name, null as asc_or_desc,
i.distinct_keys as cardinality,</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>&nbsp;i.leaf_blocks as pages, null as
filter_condition</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>from all_indexes i,</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>all_ind_columns c</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>where i.table_name = 'INDEXENTRIES'
and</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>i.index_name = c.index_name
and</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>i.table_owner = c.table_owner
and</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>i.table_name = c.table_name
and</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>i.owner = c.index_owner</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2>order by non_unique, type,
index_name, ordinal_position</FONT></DIV>
<P><FONT size=2><FONT face="Courier New">----</FONT><BR>Matt Adams - GE
Appliances - matt.adams_at_appl.ge.com<BR>Their fundamental design flaws are completely<BR>hidden by their superficial design flaws.<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - Douglas Adams</FONT> </P>
<DIV>&nbsp;</DIV>
<DIV class=OutlookMessageHeader><FONT face="Times New Roman"
size=2>-----Original Message-----<BR><B>From:</B> Alexander.Feinstein_at_mitchell1.com
[mailto:Alexander.Feinstein_at_mitchell1.com]<BR><B>Sent:</B> Friday, September 27, 2002 4:28 PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: extremely long parse time<BR><BR></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=781062519-27092002>Matt,</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=781062519-27092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=781062519-27092002>Is it
Oracle 9?</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=781062519-27092002>If
yes, time is in microseconds.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=781062519-27092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=781062519-27092002>Alex.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=781062519-27092002></SPAN></FONT>&nbsp;</DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">

  <DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma   size=2>-----Original Message-----<BR><B>From:</B> Adams, Matthew (GEA, MABG,   088130) [mailto:MATT.ADAMS_at_APPL.GE.COM]<BR><B>Sent:</B> Thursday, September   26, 2002 10:30 AM<BR><B>To:</B> Multiple recipients of list   ORACLE-L<BR><B>Subject:</B> extremely long parse time<BR><BR></DIV></FONT>   <P><FONT size=2>why would a query take 148 seconds to </FONT><BR><FONT   size=2>parse?&nbsp; It is a two way union </FONT><BR><FONT size=2>where the   first half is going against all_tables</FONT> <BR><FONT size=2>and the second   half is a join </FONT><BR><FONT size=2>between all_indexes and   all_ind_columns.</FONT> </P>
  <P><FONT size=2>The shared pool has 50M large parts of it are</FONT> <BR><FONT
  size=2>free.&nbsp; I generated a 10046 (level 12) trace, and I just 
  </FONT><BR><FONT size=2>don't see anything out of whack, except of the 
  </FONT><BR><FONT size=2>c=14868 in the PARSE #1 line.</FONT> </P><BR><BR>
  <P><FONT size=2>----</FONT> <BR><FONT size=2>Matt Adams - GE Appliances -   matt.adams_at_appl.ge.com</FONT> <BR><FONT size=2>Their fundamental design flaws   are completely</FONT> <BR><FONT size=2>hidden by their superficial design   flaws.</FONT> <BR><FONT
  size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - Douglas   Adams</FONT> </P></BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C26888.B30E1722--

------=_NextPartTM-000-fc639092-dfc7-4e87-872c-0ba5d227cfcd--

-- 
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 - 10:03:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US