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.....);