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.....);
LVL 15
Devinder Singh VirdiLead Oracle DBA TeamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Franck PachotOracle DBACommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.