:RE: extremely long parse time
OK, I think we're on to something here.
The DBA_TAB_COL_STATISTICS shows no rows for tables
owned by sys (although strangely, owner is not a column
of this table). However, the DBA_ANALYZE_OBJECTS view IS
listing objects owned by SYS, which implies
Title: RE: extremely long parse time
I know what a 10046 trace does. What's
a 10053 trace?
Matt Adams - GE Appliances - [EMAIL PROTECTED]
Their fundamental design flaws are completely
hidden by their superficial design flaws.
- Douglas Adams
-Original Message-
From: [EMAIL
Title: RE: extremely long parse time
It reports all the parameter settings and the base
statisticsthat Oracle takes into account when choosing a plan.
-Original Message-From: Adams, Matthew (GEA, MABG,
088130) [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 01,
2002 12:56
11:43 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: extremely long parse time
OK, I think we're on to something here.
The DBA_TAB_COL_STATISTICS shows no rows for tables
owned by sys (although
Title: RE: extremely long parse time
It is useddiagnosing optimiser problems. The trace
showsthe optimiser access
path.One of those "should NOT be
used unless explicitly requested by support"and not documented features.
inka
-Original Message-From: Adams, Matthew
:
[EMAIL PROTECTED] Subject: RE: extremely long parse time
om
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
H, 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
Title: 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
Hmmm...anything SYS in DBA_TAB_COL_STATISTICS or DBA_ANALYZE_OBJECTS?
Is the COST column in your PLAN_TABLE null???
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
Sent: Monday,
Title: RE: extremely long parse time
OK, I think we're on to something here.
The DBA_TAB_COL_STATISTICS shows no rows for tables
owned by sys (although strangely, owner is not a column
of this table). However, the DBA_ANALYZE_OBJECTS view IS
listing objects owned by SYS, which implies
Title: RE: extremely long parse time
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
ALL_INDEXES is a 9-way table join (some outer) with a
few correlated subqueries. ALL_IND_COLUMNS is a 8-way
table join with a few correlated subqueries as well.
That's a lot of work to churn through...
hth
connor
--- Adams, Matthew (GEA, MABG, 088130)
[EMAIL PROTECTED] wrote: why would a
Title: 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 AMTo: Multiple recipients of list
ORACLE-LSubject
Title: 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
15 matches
Mail list logo
