RE: extremely long parse time

From: Adams, Matthew (GEA, MABG, 088130) <MATT.ADAMS_at_APPL.GE.COM>
Date: Mon, 30 Sep 2002 09:38:29 -0800
Message-ID: <F001.004DCA29.20020930093829@fatcity.com>

Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C2689F.0E9DCB8A" ------_=_NextPart_001_01C2689F.0E9DCB8A
Content-Type: text/plain;
charset="iso-8859-1"

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.

Are you using CBO?
If "yes" from 1, verify that there are no stats gathered in SYS.
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

in my init.ora, in case those might also somehow affect parse time. I wouldn't think it would in this case, since these should be CBO-only and there shouldn't be stats on the data dictionary.

HTH! GL! :)

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,
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.

Douglas Adams

-----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.

------_=_NextPart_001_01C2689F.0E9DCB8A
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>

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-----
 From: Jesse, Rich [<A =
HREF=3D"mailto:Rich.Jesse_at_qtiworld.com">mailto:Rich.Jesse_at_qtiworld.com</= A>]
 Sent: Monday, September 30, 2002 11:36 AM
 To: 'ORACLE-L_at_fatcity.com'
 Cc: Adams, Matthew (GEA, MABG, 088130)
 Subject: RE: extremely long parse time

 

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 =3D =
2000".  The default is "80000"
 in 8 and 8i and "2000" in 9i.  Aside =
from cursor_sharing=3Dforce, that's the
 only parameter I have that I think could affect =
parse times that severely.  


I also have:
         optimizer_index_caching =3D 90
         optimizer_index_cost_adj =3D 50
 in my init.ora, in case those might also somehow =
affect parse time.  I
 wouldn't think it would in this case, since these =
should be CBO-only and
 there shouldn't be stats on the data =
dictionary.


HTH!  GL!  :)


Rich =

Jesse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; System/Database Administrator</FONT>

 Rich.Jesse_at_qtiworld.com      &nbs= p;       Quad/Tech International, Sussex, = WI USA


-----Original Message-----
 From: Adams, Matthew (GEA, MABG, 088130) [<A =
HREF=3D"mailto:MATT.ADAMS_at_APPL.GE.COM">mailto:MATT.ADAMS_at_APPL.GE.COM</A>= ]
 Sent: Monday, September 30, 2002 10:04 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: 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 =3D '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 =3D 'INDEXENTRIES' and
 i.index_name =3D c.index_name and
 i.table_owner =3D c.table_owner and
 i.table_name =3D c.table_name and
 i.owner =3D 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.
           - = Douglas Adams 


-----Original Message-----
 From: Alexander.Feinstein_at_mitchell1.com
 [<A =

HREF=3D"mailto:Alexander.Feinstein_at_mitchell1.com">mailto:Alexander.Feins= tein_at_mitchell1.com</A>]
 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) [<A =
HREF=3D"mailto:MATT.ADAMS_at_APPL.GE.COM">mailto:MATT.ADAMS_at_APPL.GE.COM</A>= ]
 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=3D14868 in the PARSE #1 line. 


</BODY>
</HTML>

------_=_NextPart_001_01C2689F.0E9DCB8A--

------=_NextPartTM-000-cc91d5e3-4a70-4151-beb2-e8c29776a183--

-- 
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 - 12:38:29 CDT