Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: optimizer_mode=FIRST_ROWS

RE: optimizer_mode=FIRST_ROWS

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Mon, 17 Mar 2003 13:23:42 -0800
Message-ID: <F001.0056C078.20030317132342@fatcity.com>


Karen

   It sounds as if these queries don't work well with the optimizer goal set to FIRST_ROWS. Essentially you are forcing the optimizer goal. Is there a particular reason you have FIRST_ROWS in your init.ora as your optimizer goal? My guess is that when you allow the optimizer to CHOOSE, it switches to ALL for these queries. Why not set the init.ora to CHOOSE? If that won't work for you, you could add the ALL_ROWS hint to these queries.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, March 17, 2003 2:39 PM
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 ;

--

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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 - 15:23:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US