RE: extremely long parse time

[Adams, Matthew (GEA, MABG, 088130)]

Title: 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 - [EMAIL PROTECTED]
Their fundamental design flaws are completely
hidden by their superficial design flaws.
          - Douglas Adams

 

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
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:[EMAIL PROTECTED]]
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 - [EMAIL PROTECTED]
Their fundamental design flaws are completely
hidden by their superficial design flaws.
          - Douglas Adams