RE: Tuning Query w/database link

  • From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 6 Aug 2004 15:57:27 -0400

> select * from test_view@remotenode a, 
> project_accounting_costs b where a.alra_id=b.source_id and 
> b.source_id=100001;

since a.alra_id=b.source_id, could you change your query to:

a.alra_id=100001 (instead of b.source_id=100001), so:

select * from test_view@remotenode a, 
 project_accounting_costs b 
where a.alra_id=b.source_id 
 and a.alra_id=100001; 

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guang Mei
Sent: Friday, August 06, 2004 2:41 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Tuning Query w/database link

If you could login directly into this remotenode, and see how long it
takes
to run

select * from test_view  a,
project_accounting_costs b
where a.alra_id=b.source_id
and b.source_id=100001;

If it runs fast, then the 6 min. time you saw maybe due to the transfer
of
data (1 million records) between machines.

Guang

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hostetter, Jay M
Sent: Friday, August 06, 2004 3:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Tuning Query w/database link


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@remotenode <mailto:j@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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: