Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: extremely long parse time

From: Jesse, Rich <>
Date: Mon, 30 Sep 2002 08:38:55 -0800
Message-ID: <>

Hmmmm, also running on 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

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              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 on HP-UX 11. 148 seconds on the wall clock to parse:    

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'

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 - Their fundamental design flaws are completely hidden by their superficial design flaws.

-----Original Message-----

[] Sent: Friday, September 27, 2002 4:28 PM To: Multiple recipients of list ORACLE-L


Is it Oracle 9?
If yes, time is in microseconds.  


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

Please see the official ORACLE-L FAQ:
Author: Jesse, Rich

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message to: (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 - 11:38:55 CDT

Original text of this message