Oracle insert through dblink performance issue

Question

I'm trying to do an insert as select to a table via dblink. The query looks like this:

 Insert into MARS_SUPERVISORS_PUB@EDHPUB
 select 
 {...}
 from edhper a, edhper b
 where {...
 a.idn not in (select s.tarcfsra idn from cfsra s where s.srtcfsra = 'MAPSSPA' and s.stscfsra = 'A')
 ...}

The select itself works quite fast here is its execution plan shows all the joins, but the insert takes very very long and the plan consists only of "REMOTE" steps :

INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
 LOAD TABLE CONVENTIONAL
 SORT AGGREGATE
 FILTER
 REMOTE
 REMOTE
 FILTER
 HASH JOIN
 REMOTE
 REMOTE
 REMOTE

I wonder if Oracle tries to push both tables a and b through the database link and then joins it there. Can I instruct the optimizer to do a select inside the current DB and pass only the results to @EDHPUB?

P.S/

If I create a test table locally, insert this select there and then do an insert like:

Insert into MARS_SUPERVISORS_PUB@EDHPUB
select * from TEMP_TBL

it works fast. For me it looks like an evidence that in my previous example Oracle really tries to push unjoined tables thtough dblink.

UPDATE/ I figured out that the insert takes long because of a a.idn not in ... condition. without it the plan looks like this:

INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
 LOAD TABLE CONVENTIONAL
 SORT AGGREGATE
 FILTER
 REMOTE
 REMOTE
 HASH JOIN
 REMOTE
 REMOTE

• Alex Poole: You could see if the driving_site hint affects the plan; I'm not sure on insert...
• Gaurav Soni: @AlexPoole:I think this will not make any difference ,fetching of data is done at local DB ,and inserting bulk data is done at remote DB ,the amount of data is the problem .
• Vincent Malgrat: @gaurav it could help if it worked since the plan looks like the hash join is done remotely!
• willOEM: I am curious if you see a difference by trying: Insert into MARS_SUPERVISORS_PUB@EDHPUB select * from ( select {...} from edhper a, edhper b where {...} )
• Gaurav Soni: @VincentMalgrat:Yes possible ,but one thing ,why there is a hash join with remote table ,does the select query is joining with remote table?

Answer 1

The driving_site hint, as suggested by Alex Poole would be an interesting solution to consider/investigate. I'm not sure how/if it works for remote inserts though.

If the hint doesn't work, you can use a local temporary table. Insert the data locally into your temporary table. Then INSERT INTO tab@remote FROM temp should perform well.

• andreybavt: Yes, I tried the driving_site hint - didn't work. In fact, as far as I understand this hint does the opposite (when you're pulling data it joins everything on remote DB side), here I need to push data.
• Alex Poole: @andreybavt - it depends which table you give as an argument. If you did insert /*+ driving_site(MARS_SUPERVISORS_PUB) */ ... then yes, it would push it all to the remote side. If you gave it the local table (or one of the aliases in this case) then it might keep it local. That's if it does anything in this case, and if the aliases form the subquery are meaningful to the insert...
• Alex Poole: As far as I can tell from some testing, driving_site is ignored by the insert anyway. MOS note 825677.1 suggests this too: 'a distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this', as does not-a-bug 5517609. So, worth investigating, but doesn't work *8-)
• Vincent Malgrat: @andreybavt it seems that you will have to do some workaround to have the work done locally. A temporary table should work.

Answer 2

When sending data from local table to remote table across a link, the best results we have had is to save into a local temp table, then copy the local temp table to an image of it (another temp table) on the remote database, then run a script on the remote database that inserts/merges from the remote temp into the remote 'real' tables. Every time we tried accessing a 'real' table across the link we got performance hits.