Oracle-L: RE: optimizer_mode=FIRST

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Mon, 17 Mar 2003 13:19:24 -0800
Message-ID: <F001.0056C05C.20030317131924@fatcity.com>

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 optimisationeg

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_database_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: Bruce.Reardon_at_comalco.riotinto.com.au

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 Mar 17 2003 - 15:19:24 CST

RE: optimizer_mode=FIRST_ROWS