INSERT over DBLINK having poor performance
We have a PL/SQL stored procedure that reads data from few tables(using join, approx. 3 level) and inserts this record into a remote(co-located) db using a db link.
Here is an example:
insert into target_tab@dl
select t1.col1, t2.col1...tN.col1
from source_tab1 t1
left outer join source_tab2 t2 on t1.xx=t2.xx
left outer join source_tab3 t3 on t2.xx=t3.xx
where t1.col1='value';
Here t1.col1 is having an index. The number of rows is tab1 is approx 280,000.
I checked the explain plan for the SELECT sub query.
SELECT query is executing very fast as it is having an index unique scan. As per plan: time per execution=0.0002 sec
However, the INSERT statement is taking about 0.047 sec. i.e. around 200 times more than the select sub query.
Why is the INSERT statement giving such poor performance?
How can i find out where is the time getting consumed exactly. As it is evident that the select query is getting executed very fast.
Appreciate your thoughts on this!
-Regards.
PS: Oracle version is 10.2.0.3