How to tune a query which is using DBLINK

Question

My concern is, I have a remote view abc@DBLINK and a local table xyz. I need to map a remote view and a local table and fetch a small amount of data from abc@DBLINK view and insert into some other local table say pst. i.e.

INSERT INTO pst 
SELECT remote.col1, remote.col2, remote.col3, remote.col4 
FROM 
  abc@dblink remote, 
  xyz local 
WHERE remote.col1=local.col1 
  AND remote.col2=local.col2;

Can someone please provide me any suggestion how I can make this task faster? Please note that abc@DBLINK contains a huge amount of data, and indices are present on local table col1 and col2.

In Oracle's terminology it is called "Distributed SQL Statement". I think you should read documentation first. There are some limitations when optimizing these queries. Only in some cases an index on remote side can be used. — ibre5041, May 20 '15 at 07:59

Frank Schmitt · Answer 1 · 2015-05-20T11:50:41.693

One possible approach is to force execution of the JOIN on the remote site using the DRIVING_SITE hint:

INSERT INTO pst 
SELECT /*+DRIVING_SITE(abc)*/ 
  remote.col1, remote.col2, remote.col3, remote.col4 
FROM 
  abc@dblink remote, 
  xyz local 
WHERE remote.col1=local.col1 
  AND remote.col2=local.col2;

(I seem to recall there's a limitation when doing an INSERT instead of a plain SELECT, but I can't find it in the docs right now - you might have to switch to a PL/SQL procedure that does a cursor fetch from remote and then performs the INSERT locally to benefit from the hint).

UPDATE

Oracle will indeed ignore the DRIVING_SITE hint when doing an INSERT into a local table. Jonathan Lewis has an interesting article explaining the reasons for this.

So, you can:

switch to a PL/SQL procedure that does a SELECT .. BULK COLLECT with the DRIVING_SITE hint and performs the local INSERT
create a join view remotely, and use that view for the INSERT

UPDATE 2

Here's a complete solution using the PL/SQL approach; it uses an object type and an associated collection (I've used a dummy query for input that simply generates 9500 rows instead of the original DB link one, but adapting it is pretty straightforward):

create table pst(col1 number, col2 number, col3 number, col4 number);

create type pst_t as object (
  col1 number,
  col2 number,
  col3 number,
  col4 number);

create type pst_table_t as table of pst_t;

declare
  v_coll pst_table_t;
  C_LIMIT constant pls_integer := 1000;
  cursor v_cur is 
    select 
      pst_t(level, level*2, level*3, level*4)
    from dual
    connect by level <= 9500;
begin
  open v_cur;
  loop
    fetch v_cur bulk collect into v_coll limit C_LIMIT;
    insert into pst(col1,col2,col3,col4) 
      select col1,col2,col3,col4 from table(v_coll);    
    exit when v_coll.count < C_LIMIT;
  end loop;
end;

Yes, I remember DRIVING_SITE does not work with INSERT or CREATE. Can you please elaborate or help me with an outline of using PLSQL Procedure that SELECT.. BULK COLLECT and then INSERT. — ShobhitSaxena, May 20 '15 at 08:35
DECLARE TYPE ABC_TEXT IS TABLE OF PST%ROWTYPE INDEX BY PLS_INTEGER; v_abc_text ABC_TEXT; BEGIN SELECT /*+DRIVING_SITE(abc)*/ remote.col1, remote.col2, remote.col3, remote.col4 BULK COLLECT INTO v_abc_text FROM abc@dblink remote, xyz local WHERE remote.col1=local.col1 AND remote.col2=local.col2; FOR indx IN 1..v_abc_text.count LOOP insert into pst values (v_abc_text(indx).col1, v_abc_text(indx).col2, v_abc_text(indx).col3, v_abc_text(indx).col4); END LOOP; COMMIT; END; — ShobhitSaxena, May 20 '15 at 08:59
@ShobhitSaxena In principle, yes. Depending on the number of rows you're expecting, I would use an explicit cursor and LIMIT to limit the number of rows fetched with BULK COLLECT, wrap it in a loop that iteratively fetches from the cursor until it is exhausted, and use an INSERT .. SELECT FROM TABLE(collection) instead of an explicit FOR loop. — Frank Schmitt, May 20 '15 at 09:09
If I am expecting a large amount of data, can you please provide me with the outline of the code which you will be using. That will be very helpful. — ShobhitSaxena, May 20 '15 at 10:54

score 0 · Answer 2 · answered Aug 25 '16 at 08:34

Along with other suggestions there's few more approaches:-

Check the latency between two servers(local to DBLink Server). Network latency plays a major role in this.
If data doesn't change frequently on DBLink Database then you can insert data to some landing table on local database and update it time to time and use that landing table into your query instead of using DBLink.

How to tune a query which is using DBLINK

2 Answers2