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

score 2 · Answer 1 · edited May 23 '17 at 12:40

2

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.

edited May 23 '17 at 12:40

Community

1

answered Mar 27 '13 at 16:09

Vincent Malgrat

4,484
18
23

score 1 · Answer 2 · answered Sep 11 '15 at 19:54

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.

Oracle insert through dblink performance issue

2 Answers2