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

Home -> Community -> Mailing Lists -> Oracle-L -> Tuning Query w/database link

Tuning Query w/database link

From: Hostetter, Jay M <JHostetter_at_decommunications.com>
Date: Fri, 6 Aug 2004 15:31:59 -0400
Message-ID: <D67EB7CEECD4334F9C85759227553BBC1CABA6@CL-EXCHANGE1.dande.com>


I am trying to help a developer tune a query that uses a database link. For testing purposes, I've broken the query down to the simple query shown below. TEST_VIEW is a remote view that joins three tables, which results in approximately 1 million rows. The query that Oracle passes to the remote database never has a predicate - it is always 'select * from test_view'. I have tried various hints, but I cannot get Oracle to use a predicate in the remote query. The user has to wait 6 minutes (or so) while the remote query pulls 1 million records to the local database. If I execute an explain plan for 'select * from test_view where a.alra_id=100001', an index is used an the query completes in milliseconds. Any thoughts on how I can help this user, without creating local snapshots of the tables?    

select * from test_view_at_remotenode <mailto:j_at_remotenode> a, project_accounting_costs b
where a.alra_id=b.source_id
and b.source_id=100001;    

Both databases are 8.1.7.3 on Tru64. Both databases have statistics computed and the optimizer_mode=CHOOSE. I've tried various hints including DRIVING_SITE, FIRST_ROWS, ALL_ROWS, INDEX, etc. I also put a hint in the remote view.  

Thanks,
Jay

**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.



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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Aug 06 2004 - 14:27:52 CDT

Original text of this message

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