Query slow over Dblink.

Hi all,

When I am trying to execute the below query, it is very slow(1 to 2 mins), but that not happens everytime, like once in 5 or 6 times.

SELECT SUM(NVL(TOT_NO_PKGS,0))
        FROM MQ_DPC_PKGS MDP,
             MQ_DPC_BOES MDB
       WHERE MDP.DOC_NO    = MDB.DOC_NO
         AND MDP.BILL_NO   = MDB.BILL_NO
         AND MDB.BILL_NO = '303-00006116-13'
         AND REC_TYPE      = 'I'
   AND NVL (MDP.DEL_IND, 'N') = 'N'

table MQ_DPC_PKGS is from remote DB. Help me to find out reason / solution.
query-cost.jpg
sakthikumarAsked:
Who is Participating?
 
magarityCommented:
Your query specifies one particular BILL_NO on the local table but not on the remote table. The optimizer may be translating that to a table scan on the remote.  I suggest either:
1) switch the BILL_NO to the remote table: MDP.BILL_NO = '303-00006116-13' (instead of MDB.BILL_NO =)
OR
2) move the query processing to the remote server with the 'driving_site' hint, see the Oracle documentation here: http://docs.oracle.com/cd/B10501_01/server.920/a96533/hintsref.htm#5699
0
 
slightwv (䄆 Netminder) Commented:
Check the query/plan from the remote database.

Also try monitoring network usage when it is slow.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.