Matt,
optimizer_mode = FIRST_ROWS means CBO, and for SYS schema without statistics.
Oracle 8.1.7.2 on HP-UX 11.
optimizer_mode = CHOOSE
I run your original SELECT, then with the hint FIRST_ROWS.
From trace file:
PARSING IN CURSOR #1 len=888 dep=0 uid=20 oct=3 lid=20 tim=264275830 hv=275513964 ad='a1e7360'
select
...
END OF STMT
PARSE #1:c=37,e=271,p=7,cr=19,cu=0,mis=1,r=0,dep=0,og=4,tim=264275831
...
PARSING IN CURSOR #1 len=906 dep=0 uid=20 oct=3 lid=20 tim=264293190 hv=3966396081 ad='bfbf750'
select /*+ FIRST_ROWS */
...
END OF STMT
PARSE #1:c=13234,e=13619,p=1,cr=58,cu=0,mis=1,r=0,dep=0,og=2,tim=264293190
Look at og
Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose
In my case RBO took 2.71 sec, CBO 136.19 sec.
Alex.
-----Original Message-----
From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 30, 2002 10:38 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: extremely long parse time
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 [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 30, 2002 11:36 AM
To: '[EMAIL PROTECTED]'
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 = 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
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-----Original Message-----
From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]
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 = '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.
