Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query slow over Dblink.

Posted on 2013-11-19
2
Medium Priority
?
472 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 77

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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