Oracle-L: optimizer_mode=FIRST

From: Karen Morton <oracledba_at_morton-consulting.com>
Date: Mon, 17 Mar 2003 12:38:54 -0800
Message-ID: <F001.0056C00F.20030317123854@fatcity.com>

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: Karen Morton
INET: oracledba_at_morton-consulting.com

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 - 14:38:54 CST

optimizer_mode=FIRST_ROWS