• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2132
  • Last Modified:

SQL *Net more data from dblink

I have a simple merge statement, but sometime it is taking unusual time. I check the problem and found that whenever it stuck, More Data from DBlink was the wait event on it.
I am passing a number value and corresponding select statement returns nearly 20,000 records.

Execution Plan is very good.
Stats are good.
if we replace merge with insert, there is no problem,

Is there any way to avoid ths slowness issue using merge?

Merge into table T
( select * from table@db_link where col1=&index_col) S
on (T.join1 = s.join1 )
when not matched then
insert values( s.col1, s.col2.....);
Devinder Singh Virdi
Devinder Singh Virdi
1 Solution
Franck PachotCommented:
how much data comes from the remote table ?
Can you show an execution plan ?
normal select query with where clause execution plan depends on the underlying index buit on criteria column. But when the same select query fired over db_link will not yield the same execution plan though it shows. That could be the reason it is slow.

The other workaround to have faster execution of same query is create a materialzed view in target database with above where clause and access the select * from above materialized view in combination with your merge. It should result good compared to current query.
slightwv (䄆 Netminder) Commented:
Are you on a moderately stable version of 10g?  There might be a bug somewhere.

Are you doing any updates as part of the merge or only inserts?  

Since you said the insert and select pieces run fast and if you don't need the update part of MERGE, can you tweak it to use a INSERT into ( select ... MINUS select ...) query?
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Devinder Singh VirdiLead Oracle DBA TeamAuthor Commented:
1. Yes I am trying to copy from production to test env and both are at
2. I am doing only insert right now. May need update in future.
3. Yes it works fast if we use minus with insert... select .... minus....select...
   Problem(theoretically), Current size of .sql file is 735538 bytes for all tables.
   There are nearly 500 tables involved and one table is creating problem.
   We are using DBMS_OUTPUT.PUT_LINE and Set ServerOutput On Size 1000000.
   Expected size with new code would be 735538*2 = 1471076. Therefore we may not able to generate .sql file.

Actually I have already developed the tool and was looking if I can avoid that problem without modifying it.

1. There are nearly 20,000 records needs to be merge. (Not always)
2. Please look for attached code

We are copying records from source to target and we don't need materialized views for that. There are nearly 500 tables involved for that.

OPERATION                                OPTIONS                                  OBJECT_NAME                            ID  PARENT_ID  
---------------------------------------- ---------------------------------------- ------------------------------ ---------- ---------- -
MERGE STATEMENT                                                                                                           0             
  MERGE                                                                           XXXXXXXXXXXXXXXXXXX                     1          0  
    VIEW                                                                                                                  2          1  
      NESTED LOOPS                       OUTER                                                                            3          2  
        REMOTE                                                                    XXXXXXXXXXXXXXXXXXX                     4          3  
        TABLE ACCESS                     BY INDEX ROWID                           XXXXXXXXXXXXXXXXXXX                     5          3  
          INDEX                          RANGE SCAN                               XXXXXXXXXXXXXXXXXXX_NDX                 6          5

Open in new window

slightwv (䄆 Netminder) Commented:

Are you generating the MERGE/INSERT statements dynamically from a PL/SQL block?

Also, serveroutput got a boost in 10gR2 to UNLIMITED:

Devinder Singh VirdiLead Oracle DBA TeamAuthor Commented:
Yes I have written a pl/block and using DBMS_OUTPUT there to generate .sql file which is used by another tool.
I believe Unlimited will solve my problem. Let me change the code.
Thanks Slightwv.
Devinder Singh VirdiLead Oracle DBA TeamAuthor Commented:
I changed the script and it runs fine with Insert.
Since data in two database with same pk can be different (prod with Dev), I have to use NOT IN on PK rather than Minus.

Can you please search if there is a bug in when useing merge.
slightwv (䄆 Netminder) Commented:
I wouldn't have any idea what bug you might or might not be hitting.

Even if I could find a bug , you would still need to patch and access to Metalink for any work-around.

For example here's one talking about insert/select from a dblink affecting

Patch : Bug 9119194 - Excessive remote roundtrips with fix for Bug 5354469

Now who knows if that's the problem or not.  Only Oracle can nail it down for you.
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

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now