Oracle insert through dblink performance issue

database-linkoracleperformance

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

Best Answer

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.