Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id g8UHfQV28154 for ; Mon, 30 Sep 2002 12:41:26 -0500 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id g8UHfQH28143 for ; Mon, 30 Sep 2002 12:41:26 -0500 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id IAA14403; Mon, 30 Sep 2002 08:24:23 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004DC6D6; Mon, 30 Sep 2002 07:03:33 -0800 Message-ID: Date: Mon, 30 Sep 2002 07:03:33 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Adams, Matthew (GEA, MABG, 088130)" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Adams, Matthew (GEA, MABG, 088130)" Subject: RE: extremely long parse time Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-fc639092-dfc7-4e87-872c-0ba5d227cfcd" 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@appl.ge.com Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -----Original Message----- [mailto:Alexander.Feinstein@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@appl.ge.com Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams ------_=_NextPart_001_01C26888.B30E1722 Content-Type: text/html; charset="iso-8859-1" extremely long parse time
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@appl.ge.com
Their fundamental design flaws are completely
hidden by their superficial design flaws.
          - Douglas Adams

 
-----Original Message-----
From: Alexander.Feinstein@mitchell1.com [mailto:Alexander.Feinstein@mitchell1.com]
Sent: Friday, September 27, 2002 4:28 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: extremely long parse time

Matt,
 
Is it Oracle 9?
If yes, time is in microseconds.
 
Alex.
 
-----Original Message-----
From: Adams, Matthew (GEA, MABG, 088130) [mailto:MATT.ADAMS@APPL.GE.COM]
Sent: Thursday, September 26, 2002 10:30 AM
To: Multiple recipients of list ORACLE-L
Subject: extremely long parse time

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@appl.ge.com
Their fundamental design flaws are completely
hidden by their superficial design flaws.
          - Douglas Adams

------_=_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@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@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).