Home » RDBMS Server » Performance Tuning » DB link query. (10.2.0.3)
DB link query. [message #338384] Mon, 04 August 2008 16:18 Go to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
HI Experts

One of our developers is facing performance issue on one query using dblinks.
When we run the query in remote db it is taking 4-5 mins.
When we execute the query from his local db using db links it is never ending.

Looking at this initially we are suspecting to be network issue.
we did some testing and found
Scenario 1
When we execute the query from his local db which is using some of his local tables and some remote tables it never ends.
I did 10046 session tracing and found that it waiting on

WAIT #1: nam='SQL*Net message from dblink' ela= 43367 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=10635854911875
WAIT #1: nam='SQL*Net message to dblink' ela= 1 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=10635854911949
WAIT #1: nam='SQL*Net message from dblink' ela= 42156 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=10635854954117
WAIT #1: nam='SQL*Net message to dblink' ela= 0 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=10635854954193



Scenario 2

When we execute the query from his local db using all the tables in the remote db using dblinks it is executing in 6-7 mins.

Problem is occuring when we use some of the tables in his local schema and some table in his remote db schema .
Tables that exist in his local schema are very small.

At this stage we are not sure whether this could be due to network or some other issue.
Any Ideas what could be the issue?

ANy help will be highly appreciated.
Thanks

Mehtab
Re: DB link query. [message #338385 is a reply to message #338384] Mon, 04 August 2008 16:26 Go to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>Any Ideas what could be the issue?
I suspect that you are simply moving large amounts of data across the network.
The way to make it faster is to avoid or minimize the amount of data being transferred across network.

Alternatively upgrade the network to have higher bandwidth.
Previous Topic: Merging large amounts of data into large tables
Next Topic: QUERY REWRITING
Goto Forum:
  


Current Time: Tue Dec 06 10:09:57 CST 2016

Total time taken to generate the page: 0.19725 seconds