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.
[EMAIL PROTECTED]
-----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 ;
-- 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: [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: DENNIS WILLIAMS
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).
