RE: DB Links

Kevin,

Trace the task on the local host. Use
DBMS_SUPPORT.START_TRACE_IN_SESSION(:sid, :serial, true, true). And = create a
trigger on the remote host that traces the remote portion of the task as well (p116 of "Optimizing Oracle Performance" will give you a beginning point, although I'd prefer for you use = DBMS_SUPPORT.START_TRACE(true,true)
than use the ALTER SESSION command I've suggested in the book).

The resulting trace files will contain the answers to all the questions you've asked. You'll be able to see where the local host stops working = and
begins awaiting the result from the remote host ('SQL*Net message from dblink'), and on the remote host for the same time interval, you'll be = able
to see exactly what SQL it was trying to service, and how it went about servicing it.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 10/5 Charlotte, 10/26 Toronto, 1/4 Calgary

• SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
• Hotsos Symposium 2005: March 6-10 Dallas
• Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Kevin Lange
Sent: Wednesday, September 22, 2004 4:44 PM To: Oracle-L (E-mail)
Subject: DB Links

Does anyone happen to have a reasonable explaination on what happens to = a
query when you try to access tables accross db_links ?? Does it still = use
the indexes on the remote machine ?? Does it bring all the data locally into temp tables ??

Any info will do. Book names .... Actual explainations ... directions = to
web sites ... Anything.

We have a procedure, a fairly complex procedure, that takes about 13 = hours
to run when it is ran against tables that exist accross a DB link. But, when we copy all the tables locally, it runs in about an hour. We would like to try and find a way to fix this. But first, I need to know = exactly
what is happening when the cursor access those linked tables.

Any help would be appreciated.

Kevin

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l