| DB link query. [message #338384] |
Mon, 04 August 2008 16:18  |
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  |
 |
BlackSwan
Messages: 26766
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
