queries across db-link and how driving_site hint does not work for inserts …


One of the feature I really like in Oracle Database is DB-LINK. It’s so simple to use and yet so powerful, with little bit of luck and Oracle Gateway usable even across non-oracle databases. At first db-links work great, but if you start digging deep and try to queeze as much as you can out of Oracle, you might notice some unusual behavior. This mini-post is about INSERT INTO and how this simple DML command does not work as it could (please note I did not say should, but could – this is not a bug).

Let’s assume you have two 10.2 oracle servers – SERVER1_LOCAL and SERVER2_REMOTE. I have same username on both and user public password-less db-links (this works well even with public/private links with password). I will use very small tables just for illustrations, the key point is that local table is smaller than table on remote server.

Statements below creates two tables (one on each server), plus creates basic statistics.

CONNECT JIRI@SERVER2_REMOTE;
CREATE TABLE TABLE2_COLUMNS PCTFREE 0 NOCOMPRESS AS
SELECT OWNER USERNAME, TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS;
EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME => 'JIRI', TABNAME => 'TABLE2_COLUMNS', ESTIMATE_PERCENT => NULL);



CONNECT JIRI@SERVER1_LOCAL;
CREATE TABLE TABLE1_USERS PCTFREE 0 NOCOMPRESS AS
SELECT USERNAME FROM ALL_USERS;
EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME => 'JIRI', TABNAME => 'TABLE1_USERS', ESTIMATE_PERCENT => NULL);

 

Now lets run simple query and look at DRIVING_SITE hint behavior. All queries are executed on SERVER1_LOCAL server only.

First query does not use driving_site hint and you can see Oracle executes it locally

SELECT DISTINCT A.USERNAME
  FROM TABLE2_COLUMNS@SERVER2_REMOTE A,
       TABLE1_USERS B    
 WHERE A.USERNAME = B.USERNAME(+)
   AND B.USERNAME is null
  
  
SELECT STATEMENT  ALL_ROWS Cost: 62  Bytes: 2 K  Cardinality: 86      
 5 SORT UNIQUE  Cost: 62  Bytes: 2 K  Cardinality: 86     
  4 FILTER    
   3 HASH JOIN RIGHT OUTER  Cost: 56  Bytes: 2 M  Cardinality: 69 K   
    1 TABLE ACCESS FULL TABLE JIRI.TABLE1_USERS Cost: 7  Bytes: 2 K  Cardinality: 227 
    2 REMOTE REMOTE SERIAL_FROM_REMOTE TABLE2_COLUMNS SERVER2_REMOTE Cost: 47  Bytes: 1 M  Cardinality: 69 K 
 
 

Second query uses driving_site hint and you can see Oracle executes it on remote server (as we asked in the hint). Notice that the cost is lower (remote table is larger).

SELECT /*+ DRIVING_SITE(A) */
       DISTINCT A.USERNAME
  FROM TABLE2_COLUMNS@SERVER2_REMOTE A,
       TABLE1_USERS B    
 WHERE A.USERNAME = B.USERNAME(+)
   AND B.USERNAME is null
  
  
SELECT STATEMENT REMOTE  ALL_ROWS Cost: 56  Bytes: 14  Cardinality: 1     
 4 HASH UNIQUE  Cost: 56  Bytes: 14  Cardinality: 1    
  3 HASH JOIN RIGHT ANTI  Cost: 55  Bytes: 14  Cardinality: 1   
   1 REMOTE REMOTE SERIAL_FROM_REMOTE TABLE1_USERS ! Cost: 7  Bytes: 2 K  Cardinality: 227 
   2 TABLE ACCESS FULL TABLE JIRI.TABLE2_COLUMNS SERVER2_REMOTE Cost: 47  Bytes: 471 K  Cardinality: 69 K
 

 

The fun starts when you try to use INSERT INTO, as you can see it’s the same query as the second query, but Oracle ignores driving_site hint and executes it locally.

 

INSERT INTO TABLE1_USERS
SELECT /*+ DRIVING_SITE(A) */
       DISTINCT A.USERNAME
  FROM TABLE2_COLUMNS@SERVER2_REMOTE A,
       TABLE1_USERS B    
 WHERE A.USERNAME = B.USERNAME(+)
   AND B.USERNAME is null
  
  
INSERT STATEMENT  ALL_ROWS Cost: 62  Bytes: 2 K  Cardinality: 86      
 5 SORT UNIQUE  Cost: 62  Bytes: 2 K  Cardinality: 86     
  4 FILTER    
   3 HASH JOIN RIGHT OUTER  Cost: 56  Bytes: 2 M  Cardinality: 69 K   
    1 TABLE ACCESS FULL TABLE JIRI.TABLE1_USERS Cost: 7  Bytes: 2 K  Cardinality: 227 
    2 REMOTE REMOTE SERIAL_FROM_REMOTE TABLE2_COLUMNS SERVER2_REMOTE Cost: 47  Bytes: 1 M  Cardinality: 69 K 
 

 

You can try all kinds of hints, trust me none of them work. There is a workaround and it’s actually very simple. Basically you need to use cursor and insert data to a table. This is of course not as fast as pure SQL, but if execution on remote server gives significantly better results, sometimes the only option. Below is small very universal procedure which can do exactly that. I have been using it (in little bit more complex form) for years and it works like a charm. I recommend to tune it for your purposes (maybe remove append hint, add logging, add error trap, change commit after, …).

When do I use it?  Well every time when remote table is extra large, local table small and query result tiny. The real objective is to save on network traffic simply because that is usually the bottle neck.

 
CREATE OR REPLACE PROCEDURE JIRI.INSERT_DATA_TO_TABLE  ( v_target_table VARCHAR2,
                                                         v_sql_to_run   VARCHAR2,
                                                         v_commit_after NUMBER) IS
 
v_limit_sql1    VARCHAR2(300) := ' ';
v_limit_sql2    VARCHAR2(900) := ' ';
v_plsql_to_run  VARCHAR2(32767);

BEGIN

IF NVL(v_commit_after,0) <> 0 THEN

  v_limit_sql1:=  '  LIMIT ' || TO_CHAR(v_commit_after) ;
 
  v_limit_sql2:=  '   IF MOD(v_number_of_rows, ' || TO_CHAR(v_commit_after) || ' ) = 0 THEN     ' ||
                  '       COMMIT;                                                               ' ||
                  '   END IF;                                                                   ' ;
END IF;

v_plsql_to_run:=  '                                                                             ' ||
                  'DECLARE                                                                      ' ||
                  ' v_number_of_rows number:=0;                                                 ' ||
                  '                                                                             ' ||
                  ' TYPE MyType IS REF CURSOR;                                                  ' ||
                  ' CV MyType;                                                                  ' ||
                  ' TYPE RecTyp IS TABLE OF ' || v_target_table || '%ROWTYPE;                   ' ||
                  ' rec RecTyp;                                                                 ' ||
                  '                                                                             ' ||
                  'BEGIN                                                                        ' ||
                  '                                                                             ' ||
                  'OPEN CV FOR                                                                  ' ||
                  '    ' || REPLACE( v_sql_to_run, ';', ' ' )  || ' ;                           ' ||
                  ' LOOP                                                                        ' ||
                  '     FETCH CV BULK COLLECT INTO rec ' || v_limit_sql1 || ';                  ' ||
                  '     FORALL i IN 1..rec.COUNT                                                ' ||
                  '         INSERT /*+ APPEND */ INTO ' || v_target_table || ' VALUES rec(i);   ' ||
                  '         v_number_of_rows  := v_number_of_rows + SQL%ROWCOUNT;               ' ||
                  ' ' || v_limit_sql2 || '                                                      ' ||
                  '     EXIT WHEN CV%NOTFOUND;                                                  ' ||
                  '                                                                             ' ||
                  ' END LOOP;                                                                   ' ||
                  ' COMMIT;                                                                     ' ||
                  ' CLOSE CV;                                                                   ' ||
                  'END;                                                                         ';

EXECUTE IMMEDIATE v_plsql_to_run;
COMMIT;

END;
  
    
   

-- small sample
BEGIN
     JIRI.INSERT_DATA_TO_TABLE (v_target_table => 'TABLE1_USERS',
                                v_sql_to_run   => 'SELECT /*+ DRIVING_SITE(A) */                            ' ||
                                                  '      DISTINCT A.USERNAME                                ' ||
                                                  ' FROM TABLE2_COLUMNS@SERVER2_REMOTE A,                   ' ||
                                                  '      TABLE1_USERS B                                     ' ||
                                                  ' WHERE A.USERNAME = B.USERNAME(+) AND B.USERNAME is null ',
                                v_commit_after => 100000);
END;
 
Advertisements

11 thoughts on “queries across db-link and how driving_site hint does not work for inserts …

  1. LQ says:

    I have similar experience with HINT are ignored when the statement uses SELECT clause as sub-query, eg. CREATE TABLE AS SELECT…, Execution plan changed totally, no matter how I play with HINT, finally I tuned the remote PFILE parameter and fixed the problem. In may case , it is decrease optimizer_index_cost_adj from default 100 to 30.

  2. jiri says:

    one thing what I noticed in 9.2 and 10.1 (I did not notice it in 10.2) was using CASE statement, if I used CASE statement, the execution was always on local server, when I replaced it DECODE, I was able to force the execution on remote server.

    Did optimizer_index_cost_adj fix this issue in general or just changed explain plan for a specific query? It basically decreases chance for using index in CBO. This is very nice article about optimizer_index_cost_adj http://www.dba-oracle.com/oracle_tips_cost_adj.htm

  3. Chuck says:

    It was the CASE Statement!!!!!!!!!!!!!!!!!!!!!! I am doing CTAS – and all the tables are remote ….it was still executing locally……removed the CASE and works!

  4. Luis says:

    we have the next error:

    ERROR en línea 1:
    ORA-22160: element at index [1] does not exist

  5. jiri says:

    a bit harder to help without knowing your oracle version and/or the exact code. Can you share it please.

  6. Luis says:

    Thank you for replay 🙂

    I am sorry. The error was becouse different data presicion: NUMBER and NUMBER(3).

    Thank you for you time.

  7. Alon says:

    Hi Jiri,
    Thank you for publishing the code!
    Saved me time of writing it 🙂
    This is very annoying the case of ignoring DRIVING_SITE on DMLs :-\

  8. Michael says:

    Hi all!

    Thanks Jiri for this awesome code! I’ve a problem using it: I receive the following error message: “Bulk Bind: Truncated Bind”, what I’m doing wrong? The length of my ‘v_sql_to_run’ is 1910 characters, can be a problem?

    Thanks again!

    P.D. Sorry my bad english.

  9. jiri says:

    it’s a bit harder to help without knowing detail….

    1. do you by any change use SELECT *

    if yes, then try to always use column names instead of *

    2. please make sure all source and target columns are the same type and length

  10. Lavallee Alain says:

    Using cardinality hint in subselect seems to work to get work of select done on remote db first
    Driving hint and other hints are ignored for dml insert

    INSERT /*+ append */
    INTO MIG_CGD30_TEST
    SELECT /*+ cardinality(ZFD 400000) cardinality(CGD 60000000)*/
    TRIM (CGD.NUMCPT) AS NUMCPT, TRIM (ZFD.NUMBDC_NEW) AS NUMBDC
    FROM CGD30@DBL_MIG_THALER CGD,
    ZFD10@DBL_MIG_THALER ZFD,
    EVD01_ADS_DR3W2 EVD

  11. jiri says:

    interesting!! Thank you Alain for sharing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: