Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DB link performance.

598695Aug 7 2008 — edited Aug 10 2008
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.
We did 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?

Thanks in Advance.

Mehtab
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 7 2008
Added on Aug 7 2008
7 comments
3,048 views