Thanks Bruce & Dennis for your replies.
The init.ora is set to FIRST_ROWS because statistics are always current for
the application tables and testing showed that the plans generated in that
optimizer mode offered better overall response times than with CHOOSE.
I think the idea about hinting the sys views will likely fix the problems
although we wanted to avoid hints if possible and just re-write the queries.
I just couldn't figure out a way to re-write the one query for dba_indexes
that made it any better......
Thanks,
Karen
-----Original Message-----
Bruce (CALBBAY)
Sent: Monday, March 17, 2003 4:19 PM
To: Multiple recipients of list ORACLE-L
Karen,
Are you on version 8? I imagine so given the problem you are seeing.
By using FIRST_ROWS you are forcing the optimizer to use CBO even when there
are no statistics.
Most likely you have no stats on your sys objects (and this is a good thing)
and thus the execution plan the CBO is providing will be a bad one.
There a few options:
rewrite the query - as you have done
hint the query with specific hints to cause the correct execution path
For DBA queries like this, the easiest may be to hint to use RULE base
optimisation-
eg
select /*+RULE*/ ....
Some notes suggested by Anita Bardeen when I posted on a similar topic in
Nov 2001.
Note: 35272.1 "Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY
SYS)?"
Note: 35934.1 "TECH: Cost Based Optimizer - Common Misconceptions and
Issues"
Note: 66484.1 "Which Optimizer is used"
Some other points I have found:
In first_rows mode you will encounter some very bad queries against the data
dictionary.
An example of 1 which has been fixed by Oracle is catblock.sql -
there is an updated version available on Metalink -
see note 122567.1 titled "Poor Performance in Query onDBA_WAITERS"
Whilst searching for the notes suggested by Anita, I came across a good
forum discussion
(see
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=FOR&p_id=279251.999 )
This describes how the ODBC driver 8.1.7.4 has been fixed / improved to use
rule hints when accessing the data dictionary.
Before this if you used the Oracle ODBC driver and were in first_rows mode
we had to wait 5 - 10 minutes just to link a table in Access
HTH,
Bruce Reardon
-----Original Message-----
Sent: Tuesday, 18 March 2003 7:39 AM
To: Multiple recipients of list ORACLE-L
All,
I've run into the following queries "hanging" when ran on a database with
the
optimizer_mode set to FIRST_ROWS. If the optimizer_mode is CHOOSE, no
problems. When set to FIRST_ROWS both queries show never-ending wait events
for "direct path read". I killed the sessions before they finished after
waiting for almost an hour for the queries to complete.
I re-wrote the 1st query against dba_tables to use EXISTS (also shown below)
and that seemed to work fine. But I'm not sure why or how to re-write the
2nd
query to also be able to work....I've tried a couple of things with no luck.
Any ideas?
Thanks for the help,
Karen Morton
select dba_tab_columns.table_name, dba_tab_columns.column_name,
dba_tab_columns.column_id, dba_tab_columns.data_length,
dba_tab_columns.data_type, dba_tab_columns.nullable,
dba_tab_columns.data_precision
from dba_tables, dba_tab_columns
where dba_tables.owner = 'XYZDBA'
and dba_tables.table_name = dba_tab_columns.table_name
order by dba_tab_columns.table_name, dba_tab_columns.column_id ;
select dba_indexes.table_name, dba_indexes.index_name,
dba_indexes.uniqueness, dba_ind_columns.column_name,
dba_ind_columns.column_position
from dba_indexes, dba_ind_columns
where dba_indexes.owner = 'XYZDBA'
and dba_indexes.index_name = dba_ind_columns.index_name
order by dba_indexes.table_name, dba_indexes.index_name,
dba_ind_columns.column_position ;
-- Rewritten dba_tables query that works
select dba_tab_columns.table_name,
dba_tab_columns.column_name,
dba_tab_columns.column_id,
dba_tab_columns.data_length,
dba_tab_columns.data_type,
dba_tab_columns.nullable,
dba_tab_columns.data_precision
from dba_tab_columns
where EXISTS (SELECT *
FROM dba_tables
WHERE owner = 'XYZDBA'
AND table_name = dba_tab_columns.table_name)
order by dba_tab_columns.table_name, dba_tab_columns.column_id ;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Karen Morton
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
