Improve company productivity with a Business Account.Sign Up

x
?
Solved

Query slow over Dblink.

Posted on 2013-11-19
2
Medium Priority
?
476 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:sakthikumar
2 Comments
 
LVL 79

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39659245
Check the query/plan from the remote database.

Also try monitoring network usage when it is slow.
0
 
LVL 13

Accepted Solution

by:
magarity earned 1000 total points
ID: 39661339
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question