【新炬網路名師大講堂】有關DBLINK操作的語句執行機制及最佳化方式

shsnchyw發表於2014-12-22

分散式查詢語句對於遠端物件的查詢在遠端庫執行，在遠端可以執行的語句會透過最佳化器的查詢轉換，執行的是轉換後的語句，然後結果集返回到本地，再與本地表運算。當然，
本地還是遠端是相對的，我們可以透過driving_hint改變主查詢計劃的執行位置，但是對DML，driving_site是失效的。另外對遠端表也可以使用其他hint。

   分散式查詢可能一條SQL語句中有不同遠端庫的表，最佳化分散式查詢要達到3點效果：
1.訪問同一個遠端庫的次數要儘量少,也就是同一遠端庫的物件應該儘量轉為一條SQL運算，一次運算，運算後將結果返回給本地庫
2.從遠端庫上返回到本地庫的結果要儘量少，只訪問遠端物件需要的欄位
3.遠端庫上執行的語句的計劃以及遠端庫返回的結果與本地可以聯合查詢的計劃應該高效
 最佳化分散式查詢需要從以上3個方面著手。

下面的local_tab 7萬多條，remote_big_tab百萬條，remote_small_tab 7萬多條。

 1.使用Collocated內聯檢視
  也就是SQL要對引用不同遠端庫的表，要組織好，將相同庫的表放一起組織成內聯檢視，這樣ORACLE就很容易知道這個內聯檢視裡的表是在同一遠端庫作完查詢
  後再返回給本地庫，這樣減少了本地庫與遠端庫的互動次數和傳輸結果集的數量和次數。比如上面的查詢

SELECT  * FROM local_tab a
 WHERE EXISTS
 (SELECT  1 FROM remote_big_tab@remote b,remote_small_tab@remote c
 WHERE b.object_id=c.object_id AND a.object_type=b.object_type);


執行計劃
———————————————————-
Plan hash value: 49311412
————————————————————————————————
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
————————————————————————————————
|   0 | SELECT STATEMENT   |           | 42747 |  4508K|  2152   (4)| 00:00:26 |        |      |
|*  1 |  HASH JOIN         |           | 42747 |  4508K|  2152   (4)| 00:00:26 |        |      |
|   2 |   VIEW             | VW_SQ_1   |    26 |   286 |  1855   (4)| 00:00:23 |        |      |
|   3 |    REMOTE          |           |       |       |            |          | REMOTE | R->S |
|   4 |   TABLE ACCESS FULL| LOCAL_TAB | 73985 |  7008K|   296   (1)| 00:00:04 |        |      |
————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
   1 – access(“A”.”OBJECT_TYPE”=”ITEM_0″)
Remote SQL Information (identified by operation id):
—————————————————-
   3 – EXPLAIN PLAN SET STATEMENT_ID=’PLUS5801659′ INTO PLAN_TABLE@! FOR SELECT
       DISTINCT “A2″.”OBJECT_TYPE” FROM “REMOTE_BIG_TAB” “A2″,”REMOTE_SMALL_TAB” “A1″ WHERE
       ”A2″.”OBJECT_ID”=”A1″.”OBJECT_ID” (accessing ‘REMOTE’ )

  可以看出，在遠端庫remote上執行的語句是兩個遠端表關聯後，並經過查詢轉換（全轉為大寫，自己取了別名A1，A2，ORACLE內部自己改造為止查詢DISTINCT   remote_big_tab.object_type)，之後遠端查詢結果返回給本地，可以去遠端庫裡查詢實際的計劃，走的是HASH JOIN。

2.瞭解CBO最佳化器對分散式查詢的處理
   CBO對分散式查詢的處理，也是儘量轉為Collocated內聯檢視，CBO會做如下動作：
   1）所有可mergeable的檢視會merge
   2 ) CBO會測試Collocated內聯檢視的query BLOCK
   3 ) 如果可以使用，就使用合併
   當然，CBO對分散式查詢的處理，可能是不高效的，這時候得用其他的方法，比如使用HINT,改造SQL，改造分散式查詢的方法（遠端庫用檢視）等。
   特別當分散式查詢包含下列情況，CBO可能是不高效的：
   1）有分組運算
   2）有子查詢
   3）SQL很複雜
   
   比如下面語句含有子查詢：

SELECT  * FROM local_tab a,remote_big_tab@remote b,remote_small_tab@remote c
 WHERE b.object_id=c.object_id AND a.object_type=b.object_type
 AND a.object_id IN (SELECT object_id from sub);
執行計劃
———————————————————-
Plan hash value: 252158753
———————————————————————————————————-
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
———————————————————————————————————-
|   0 | SELECT STATEMENT      |                  |    79M|    20G|  3843  (46)| 00:00:47 |        |   |
|*  1 |  HASH JOIN            |                  |    79M|    20G|  3843  (46)| 00:00:47 |        |   |
|   2 |   REMOTE              | REMOTE_SMALL_TAB |  5320 |   431K|     8   (0)| 00:00:01 | REMOTE | R->S |
|*  3 |   HASH JOIN           |                  |   172M|    31G|  2978  (31)| 00:00:36 |        |   |
|*  4 |    HASH JOIN          |                  |  5260 |   565K|   303   (2)| 00:00:04 |        |   |
|   5 |     SORT UNIQUE       |                  |  5320 | 69160 |     5   (0)| 00:00:01 |        |   |
|   6 |      TABLE ACCESS FULL| SUB              |  5320 | 69160 |     5   (0)| 00:00:01 |        |   |
|   7 |     TABLE ACCESS FULL | LOCAL_TAB        | 73985 |  7008K|   296   (1)| 00:00:04 |        |   |
|   8 |    REMOTE             | REMOTE_BIG_TAB   |  1479K|   119M|  1819   (2)| 00:00:22 | REMOTE | R->S |
———————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   1 – access(“B”.”OBJECT_ID”=”C”.”OBJECT_ID”)
   3 – access(“A”.”OBJECT_TYPE”=”B”.”OBJECT_TYPE”)
   4 – access(“A”.”OBJECT_ID”=”OBJECT_ID”)


Remote SQL Information (identified by operation id):
—————————————————-

   2 – SELECT “OBJECT_NAME”,”SUBOBJECT_NAME”,”OBJECT_ID”,”DATA_OBJECT_ID”,”OBJECT_TYPE”,”CREATED”,
       ”LAST_DDL_TIME”,”TIMESTAMP”,”STATUS”,”TEMPORARY”,”GENERATED”,”SECONDARY” FROM “REMOTE_SMALL_TAB”
       ”C” (accessing ‘REMOTE’ )

   8 – SELECT “OWNER”,”OBJECT_NAME”,”SUBOBJECT_NAME”,”OBJECT_ID”,”DATA_OBJECT_ID”,”OBJECT_TYPE”,”C
       REATED”,”LAST_DDL_TIME”,”TIMESTAMP”,”STATUS”,”TEMPORARY”,”GENERATED”,”SECONDARY” FROM
       ”REMOTE_BIG_TAB” “B” (accessing ‘REMOTE’ )

 

 透過計劃可以看到REMOTE有兩個，兩張遠端表無法做Collocated inline VIEW運算。
 
  再比如下面的語句，有分組運算：

SELECT  * FROM local_tab a,remote_big_tab@remote b,(SELECT max(object_id) object_id FROM remote_small_tab@remote c GROUP BY c.object_type) c
WHERE b.object_id=c.object_id AND a.object_type=b.object_type;


執行計劃
———————————————————-
Plan hash value: 2122363341
—————————————————————————————————–
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
—————————————————————————————————–
|   0 | SELECT STATEMENT   |                |  2321K|   431M|  2144   (3)| 00:00:26 |        |      |
|*  1 |  HASH JOIN         |                |  2321K|   431M|  2144   (3)| 00:00:26 |        |      |
|*  2 |   HASH JOIN        |                |  1412 |   135K|  1836   (3)| 00:00:23 |        |      |
|   3 |    VIEW            |                |    11 |   143 |     9  (12)| 00:00:01 |        |      |
|   4 |     REMOTE         |                |       |       |            |          | REMOTE | R->S |
|   5 |    REMOTE          | REMOTE_BIG_TAB |  1479K|   119M|  1819   (2)| 00:00:22 | REMOTE | R->S |
|   6 |   TABLE ACCESS FULL| LOCAL_TAB      | 73985 |  7008K|   296   (1)| 00:00:04 |        |      |
—————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   1 – access(“A”.”OBJECT_TYPE”=”B”.”OBJECT_TYPE”)
   2 – access(“B”.”OBJECT_ID”=”C”.”OBJECT_ID”)

Remote SQL Information (identified by operation id):
—————————————————-
   4 – EXPLAIN PLAN SET STATEMENT_ID=’PLUS5801659′ INTO PLAN_TABLE@! FOR SELECT
       MAX(“A1″.”OBJECT_ID”) FROM “REMOTE_SMALL_TAB” “A1″ GROUP BY “A1″.”OBJECT_TYPE” (accessing
       ’REMOTE’ )
   5 – SELECT “OWNER”,”OBJECT_NAME”,”SUBOBJECT_NAME”,”OBJECT_ID”,”DATA_OBJECT_ID”,”OBJECT_TYP
       E”,”CREATED”,”LAST_DDL_TIME”,”TIMESTAMP”,”STATUS”,”TEMPORARY”,”GENERATED”,”SECONDARY” FROM
       ”REMOTE_BIG_TAB” “B” (accessing ‘REMOTE’ )

   透過計劃看出，將遠端表進行分組運算後，傳輸給本地庫，然後大表傳輸給本地庫，之後做HASH JOIN，這是不高效的。執行時間：已用時間:  00: 02: 12.22

 可以改造分散式查詢，手動組織Collocated inline VIEW,在遠端庫建立view:

CREATE OR REPLACE VIEW v_remote
AS
SELECT  b.* FROM remote_big_tab b,(SELECT max(object_id) object_id FROM remote_small_tab c GROUP BY c.object_type) c
WHERE b.object_id=c.object_id; 


查詢改為：
 SELECT   * FROM local_tab a,v_remote@remote v WHERE a.object_type=v.object_type;

SQL> SELECT   * FROM local_tab a,v_remote@remote v WHERE a.object_type=v.object_type;
已選擇1727104行。
已用時間:  00: 01: 02.81

執行計劃
———————————————————-
Plan hash value: 2216230941
————————————————————————————————
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
————————————————————————————————
|   0 | SELECT STATEMENT   |           |     1 |   274 |   299   (2)| 00:00:04 |        |      |
|*  1 |  HASH JOIN         |           |     1 |   274 |   299   (2)| 00:00:04 |        |      |
|   2 |   REMOTE           | V_REMOTE  |     1 |   177 |     2   (0)| 00:00:01 | REMOTE | R->S |
|   3 |   TABLE ACCESS FULL| LOCAL_TAB | 73985 |  7008K|   296   (1)| 00:00:04 |        |      |
————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
   1 – access(“A”.”OBJECT_TYPE”=”V”.”OBJECT_TYPE”)

 透過計劃可以看出，現在是遠端表做整體操作之後才返回到本地了。

 3.使用HINT，特別是driving_site HINT
  對遠端表可以使用hint,比如parallel,use_nl,use_hash,FULL等。
  driving_site hint能夠指定執行計劃在遠端還是本地做，比如下面使用driving_site(b),那麼原來的遠端表就相當於本地表，本地表要傳輸給remote庫，主計劃在remote庫上執行
  

SELECT/*+driving_site(b)*/  * FROM local_tab a,remote_big_tab@remote b,(SELECT max(object_id) object_id FROM remote_small_tab@remote c GROUP BY c.object_type) c
WHERE b.object_id=c.object_id AND a.object_type=b.object_type;

   當然，如果是driving_site(a)那麼就是本地驅動的，預設的是本地驅動的。
   
   使用driving_site,特別是本地小結果集，遠端大結果集的時候，總體結果集較小，希望計劃在遠端驅動，這樣遠端執行完畢，將結果集傳輸到本地，這樣避免大結果集的傳輸。
   
   例1：
         小表9998條，大表3169376條記錄,遠端大表sub_id,acc_id上聯合索引

SQL>  SELECT  COUNT(*)  FROM small_tab_local a, big_tab_remote@remote b
  2   WHERE a.sub_id=b.sub_id AND a.acc_id=b.acc_id;
 
       859
已用時間:  00: 00: 50.76


執行計劃
———————————————————-
Plan hash value: 1507576754
——————————————————————————————————–
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
——————————————————————————————————–
|   0 | SELECT STATEMENT     |                 |     1 |    41 |    44   (3)| 00:00:01 |        |      |
|   1 |  SORT AGGREGATE      |                 |     1 |    41 |            |          |        |      |
|   2 |   MERGE JOIN         |                 |  9998 |   400K|    44   (3)| 00:00:01 |        |      |
|   3 |    REMOTE            | BIG_TAB_REMOTE  |  6771K|   167M|    26   (0)| 00:00:01 | REMOTE | R->S |
|*  4 |    SORT JOIN         |                 |  9998 |   146K|    18   (6)| 00:00:01 |        |      |
|   5 |     TABLE ACCESS FULL| SMALL_TAB_LOCAL |  9998 |   146K|    17   (0)| 00:00:01 |        |      |
——————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
   4 – access(“A”.”SUB_ID”=”B”.”SUB_ID” AND “A”.”ACC_ID”=”B”.”ACC_ID”)
       filter(“A”.”ACC_ID”=”B”.”ACC_ID” AND “A”.”SUB_ID”=”B”.”SUB_ID”)

Remote SQL Information (identified by operation id):
—————————————————-

   3 – SELECT “SUB_ID”,”ACC_ID” FROM “BIG_TAB_REMOTE” “B” ORDER BY “SUB_ID”,”ACC_ID”
       (accessing ‘REMOTE’ )

    查詢876條資料，耗時50s,顯然將大結果集拉到本地做運算是不好的，因為本地表很小，遠端大表有索引，如果能在遠端執行，並走nl,那麼顯然效率非常好。使用driving_site hint改造查詢如下：
  

SELECT/*+driving_site(b)  ordered use_nl(b)*/  COUNT(*) FROM small_tab_local a, big_tab_remote@remote b
 WHERE a.sub_id=b.sub_id AND a.acc_id=b.acc_id;


計劃如下：
———————————————————————————————
| Id  | Operation              | Name               | Rows  | Bytes | Cost  | Inst   |IN-OUT|
———————————————————————————————
|   0 | SELECT STATEMENT REMOTE|                    |     1 |    52 | 10009 |        |      |
|   1 | SORT AGGREGATE         |                    |     1 |    52 |       |        |      |
|   2 | NESTED LOOPS           |                    |   681 | 35412 | 10009 |        |      |
|   3 | REMOTE                 |                    |  9998 |   253K|    11 |      ! | R->S |
|*  4 | INDEX RANGE SCAN       | IDX_BIG_TAB_REMOTE |     1 |    26 |     1 | MZT~ |      |
———————————————————————————————

Predicate Information (identified by operation id):
—————————————————
   4 – access(“A2″.”SUB_ID”=”A1″.”SUB_ID” AND “A2″.”ACC_ID”=”A1″.”ACC_ID”)

Remote SQL Information (identified by operation id):
—————————————————-
   3 – SELECT “SUB_ID”,”ACC_ID” FROM “SMALL_TAB_LOCAL” “A2″ (accessing ‘!’ )

     現在主計劃是在遠端remote上執行的，本地表small_tab_local變成了遠端表，會講small_tab_local結果集送到遠端，只查詢了sub_id,acc_id,然後作為驅動表，與遠端表做nl運算，
計劃裡可以看到遠端表走索引了，最後將遠端結果返回到本地。（事實上這裡的遠端庫與本地庫換了）

  driving_site hint注意點：
  driving_site對dml無效，dml以目標表所在庫驅動SQL計劃。比如下面的driving_site失效，後面的hint還是有效的。
   

  CREATE TABLE test_cnt (cnt NUMBER);
  INSERT INTO test_cnt
  SELECT/*+driving_site(b) ordered use_nl(b)*/  COUNT(*) FROM small_tab_local a, big_tab_remote@remote b
  WHERE a.sub_id=b.sub_id AND a.acc_id=b.acc_id;
已用時間:  00: 01: 31.48


執行計劃
———————————————————-
Plan hash value: 259989953
————————————————————————————————————
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
————————————————————————————————————
|   0 | INSERT STATEMENT         |                 |     1 |    41 | 10035   (1)| 00:02:01 |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_CNT        |       |       |            |          |        |      |
|   2 |   SORT AGGREGATE         |                 |     1 |    41 |            |          |        |      |
|   3 |    NESTED LOOPS          |                 |  9998 |   400K| 10035   (1)| 00:02:01 |        |      |
|   4 |     TABLE ACCESS FULL    | SMALL_TAB_LOCAL |  9998 |   146K|    17   (0)| 00:00:01 |        |      |
|   5 |     REMOTE               | BIG_TAB_REMOTE  |     1 |    26 |     1   (0)| 00:00:01 | REMOTE | R->S |
————————————————————————————————————

Remote SQL Information (identified by operation id):
—————————————————-

   5 – SELECT /*+ OPAQUE_TRANSFORM USE_NL (“B”) */ ”SUB_ID”,”ACC_ID” FROM “BIG_TAB_REMOTE” “B”
       WHERE :1=”SUB_ID” AND :2=”ACC_ID” (accessing ‘REMOTE’ )

  語句執行1分31s,driving_site hint失效，但是後面的NL沒有失效，可以從計劃中看出類似繫結變數的東西，這實際對於每個small_tab_local的結果集的行，將sub_id,acc_id傳給遠端表big_tab_remote,也就是：1，：2，這樣本地的表篩選出多少行，遠端語句 SELECT /*+ OPAQUE_TRANSFORM USE_NL (“B”) */ ”SUB_ID”,”ACC_ID” FROM “BIG_TAB_REMOTE” “B”
 WHERE :1=”SUB_ID” AND :2=”ACC_ID” 就執行多少次。

 這裡本地表9998條，無過濾條件，因此遠端表語句執行了9998次，雖然遠端查詢也是走索引的，但是SQL被執行了9998次，是非常影響效能的。可以去遠端庫查詢下：
 

SQL> SELECT sql_text,executions FROM v$sql WHERE sql_text LIKE ‘%SELECT /*+ USE_NL (“B”) */ “SUB_ID”,”ACC_ID” FROM “BIG_TAB_REMOTE”%’
  2  /
 
SQL_TEXT                                                                   EXECUTIONS
——————————————————————————– ————————————————————–
SELECT /*+ USE_NL (“B”) */ “SUB_ID”,”ACC_ID” FROM “BIG_TAB_REMOTE” “B” WHERE :1=       9998

   這裡driving_site失效，但是後面的nl還有效，遠端表執行的次數是small_tab_local表的數量（因為這裡沒有謂詞過濾small_tab_local),可以使用其他hint,比如。

  INSERT INTO test_cnt
  SELECT/*+ordered use_hash(b)*/   COUNT(*) FROM small_tab_local a, big_tab_remote@remote b
  WHERE a.sub_id=b.sub_id AND a.acc_id=b.acc_id;
  
  當然效率不一定很好，因為這裡由遠端驅動效率最好，為了不想driving_site失效，可以使用PL/SQL(這裡是只查詢數量，如果查詢結果集可以使用PL/SQL批處理插入）。

BEGIN
    FOR i IN (SELECT/*+driving_site(b) ordered use_nl(b)*/  COUNT(*) cnt FROM small_tab_local a, big_tab_remote@remote b
                   WHERE a.sub_id=b.sub_id AND a.acc_id=b.acc_id)
   LOOP
     INSERT INTO test_cnt VALUES(i.cnt);
  END LOOP;
  COMMIT;
END;
已用時間:  00: 00: 00.89

     
  例2：
      查詢語句：

SELECT * FROM v_remote WHERE object_id IN (
  SELECT c.object_id FROM c WHERE c.object_name
           IN (SELECT d.object_name FROM d WHERE d.object_id=11)
);

 比較慢，返回32行，需要10來秒。其中v_remote是個檢視，此檢視連線到遠端表，其中遠端的兩張表的object_id都有索引：

CREATE OR REPLACE VIEW v_remote
AS
SELECT object_name,object_id,object_type FROM a@remote  
UNION ALL
SELECT  object_name,object_id,object_type FROM b@remote;


兩表記錄數如下：
SQL> SELECT COUNT(*) FROM a;
 
  COUNT(*)
———-
    369888
SQL> SELECT COUNT(*) FROM b;
 
  COUNT(*)
———-
      5323

  c和d是本地表，d.object_id以及c.object_name有索引。單獨查詢很快，<1s就會返回：

–單獨本地語句消耗時間00: 00: 00.01
SQL> SELECT c.object_id FROM c WHERE c.object_name IN (SELECT d.object_name FROM d WHERE d.object_id=11);
已用時間:  00: 00: 00.01
執行計劃
———————————————————-
Plan hash value: 2528799293
—————————————————————————————-
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT               |       |     2 |    94 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS                  |       |       |       |            |          |
|   2 |   NESTED LOOPS                 |       |     2 |    94 |     6  (17)| 00:00:01 |
|   3 |    SORT UNIQUE                 |       |     1 |    17 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| D     |     1 |    17 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_D |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | IDX_C |     2 |       |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | C     |     2 |    60 |     3   (0)| 00:00:01 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   5 – access(“D”.”OBJECT_ID”=11)
   6 – access(“C”.”OBJECT_NAME”=”D”.”OBJECT_NAME”)


–單獨遠端語句消耗時間 00: 00: 00.06
SQL> SELECT * FROM v_remote WHERE object_id=11;
已選擇32行。
已用時間:  00: 00: 00.06
執行計劃
———————————————————-
Plan hash value: 1788691278
————————————————————–
| Id  | Operation        | Name | Cost (%CPU)| Inst   |IN-OUT|
————————————————————–
|   0 | SELECT STATEMENT |      |     0   (0)|        |      |
|   1 |  REMOTE          |      |            | REMOTE | R->S |
————————————————————–

Remote SQL Information (identified by operation id):
—————————————————-
   1 – EXPLAIN PLAN SET STATEMENT_ID=’PLUS5821518′ INTO PLAN_TABLE@!
       FOR SELECT “A1″.”OBJECT_NAME”,”A1″.”OBJECT_ID”,”A1″.”OBJECT_TYPE” FROM
       ( (SELECT “A4″.”OBJECT_NAME” “OBJECT_NAME”,”A4″.”OBJECT_ID”
       ”OBJECT_ID”,”A4″.”OBJECT_TYPE” “OBJECT_TYPE” FROM “A” “A4″ WHERE
       ”A4″.”OBJECT_ID”=11) UNION ALL  (SELECT “A3″.”OBJECT_NAME”
       ”OBJECT_NAME”,”A3″.”OBJECT_ID” “OBJECT_ID”,”A3″.”OBJECT_TYPE”
       ”OBJECT_TYPE” FROM “B” “A3″ WHERE “A3″.”OBJECT_ID”=11)) “A1″ (accessing
       ’REMOTE’ )
–聯合查詢消耗時間00: 00: 10.95
SQL> SELECT * FROM v_remote WHERE object_id IN (
  2  SELECT c.object_id FROM c WHERE c.object_name IN (SELECT d.object_name FROM d WHERE d.object_id=11)
  3  );
已選擇32行。
已用時間:  00: 00: 10.95
執行計劃
———————————————————-
Plan hash value: 2118901120
————————————————————————————————————-
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
————————————————————————————————————-
|   0 | SELECT STATEMENT                 |          |    65 |  6695 |   471   (3)| 00:00:06 |        |      |
|*  1 |  HASH JOIN                       |          |    65 |  6695 |   471   (3)| 00:00:06 |        |      |
|   2 |   VIEW                           | VW_NSO_1 |     2 |    26 |     6  (17)| 00:00:01 |        |      |
|   3 |    HASH UNIQUE                   |          |     2 |    94 |     6  (17)| 00:00:01 |        |      |
|   4 |     NESTED LOOPS                 |          |       |       |            |          |        |      |
|   5 |      NESTED LOOPS                |          |     2 |    94 |     5   (0)| 00:00:01 |        |      |
|   6 |       TABLE ACCESS BY INDEX ROWID| D        |     1 |    17 |     2   (0)| 00:00:01 |        |      |
|*  7 |        INDEX RANGE SCAN          | IDX_D    |     1 |       |     1   (0)| 00:00:01 |        |      |
|*  8 |       INDEX RANGE SCAN           | IDX_C    |     2 |       |     2   (0)| 00:00:01 |        |      |
|   9 |      TABLE ACCESS BY INDEX ROWID | C        |     2 |    60 |     3   (0)| 00:00:01 |        |      |
|  10 |   VIEW                           | V_REMOTE |   375K|    32M|   462   (2)| 00:00:06 |        |      |
|  11 |    UNION-ALL                     |          |       |       |            |          |        |      |
|  12 |     REMOTE                       | A        |   369K|    29M|   454   (2)| 00:00:06 | REMOTE | R->S |
|  13 |     REMOTE                       | B        |  5323 |   431K|     8   (0)| 00:00:01 | REMOTE | R->S |
————————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
   1 – access(“OBJECT_ID”=”OBJECT_ID”)
   7 – access(“D”.”OBJECT_ID”=11)
   8 – access(“C”.”OBJECT_NAME”=”D”.”OBJECT_NAME”)

Remote SQL Information (identified by operation id):
—————————————————-
  12 – SELECT “OBJECT_NAME”,”OBJECT_ID”,”OBJECT_TYPE” FROM “A” “A” (accessing ‘REMOTE’ )
  13 – SELECT “OBJECT_NAME”,”OBJECT_ID”,”OBJECT_TYPE” FROM “B” “B” (accessing ‘REMOTE’ )

 

     單獨查詢很快，為什麼聯合查詢就慢了呢？原因在於：
 

單獨執行遠端查詢 本地與遠端混合查詢
直接執行檢視，並將OBJECT_ID=11謂詞推入到檢視中，走索引，最後只將32行結果返回給本地 從計劃中可以看到，本地查詢與遠端查詢做HASH JOIN，但是訪問遠端的SQL是沒有謂詞的，這樣必然全表從遠端拉到本地，因為行數較多，所以慢
 
    因此，最佳化此混合查詢的語句可以由多種辦法（比如本地查詢的數量較少，可以採用上面的方法，本地與遠端查詢拆分為2條語句），另外就是可以使用driving_site hint,將主計劃推到遠端庫去執行，本地的結果集少，推到遠端，遠端檢視走索引，效率高。如下：


–耗時已用時間:  00: 00: 00.08
SQL> SELECT/*+driving_site(v_remote.a)*/ * FROM v_remote WHERE object_id IN (
  2  SELECT c.object_id FROM c WHERE c.object_name IN (SELECT d.object_name FROM d WHERE d.object_id=11
  3  );
已選擇32行。
已用時間:  00: 00: 00.08
—————————————————————————————
| Id  | Operation                  | Name     | Rows  | Bytes | Cost  | Inst   |IN-OUT|
—————————————————————————————
|   0 | SELECT STATEMENT REMOTE    |          |    15 |  1425 |   109 |        |      |
|   1 | NESTED LOOPS               |          |    15 |  1425 |   109 |        |      |
|   2 | SORT UNIQUE                |          |       |       |       |        |      |
|   3 | VIEW                       | VW_NSO_1 |     3 |    39 |     8 | MZT~ |      |
|   4 | REMOTE                     |          |       |       |       |      ! | R->S |
|   5 | VIEW                       |          |     5 |   410 |    33 |        |      |
|   6 | UNION-ALL PARTITION        |          |       |       |       |        |      |
|   7 | TABLE ACCESS BY INDEX ROWID| A        |    32 |   960 |    35 |MZT~ |      |
|*  8 | INDEX RANGE SCAN           | IDX_A    |    32 |       |     3 |MZT~ |      |
|   9 | TABLE ACCESS BY INDEX ROWID| B        |     1 |    32 |     2 | MZT~ |      |
|* 10 | INDEX RANGE SCAN           | IDX_B    |     1 |       |     1 | MZT~ |      |
—————————————————————————————


Predicate Information (identified by operation id):
—————————————————
   8 – access(“A6″.”OBJECT_ID”=”VW_NSO_1″.”OBJECT_ID”)
  10 – access(“A5″.”OBJECT_ID”=”VW_NSO_1″.”OBJECT_ID”)

Remote SQL Information (identified by operation id):
—————————————————-
   4 – SELECT /*+ */ “A1″.”OBJECT_ID” FROM “D” “A2″,”C” “A1″ WHERE
       ”A1″.”OBJECT_NAME”=”A2″.”OBJECT_NAME” AND “A2″.”OBJECT_ID”=11 (accessing ‘!’ )

    現在效率很高，從計劃中可以看出，現在計劃在遠端庫上執行，本地的查詢查詢一行object_id=11傳輸給遠端，並驅動檢視查詢，檢視走索引，然後再將結果返回給本地。另外注意hint 檢視的用法是hint(檢視名.表名)。

  再說說driving_site失效的情況：
 DML,DDL讓driving_site失效,driving_site hint會自動被oracle忽略掉，因為此時以目標表所在的庫為主計劃驅動，相當於driving_site(目標表庫）
  1）DML，DDL如果是對本地表DML，主計劃是在本地做的，遠端資料拉到本地,driving_site(remote)失效,當然driving_site(local)是自動的，寫不寫無所謂
  2）DML如果是對遠端表DML，主計劃是在遠端做的，本地資料送到遠端，相當於自動driving_site(remote)

–本地建表和操作表test,driving_site失效
CREATE TABLE test AS
SELECT/*+driving_site(v_remote.a)*/ * FROM v_remote WHERE object_id IN (
SELECT c.object_id FROM c WHERE c.object_name IN (SELECT d.object_name FROM d WHERE d.object_id=11)
);


————————————————————————————————————–
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
————————————————————————————————————–
|   0 | CREATE TABLE STATEMENT            |          |    65 |  6695 |  1267   (1)| 00:00:16 |        |      |
|   1 |  LOAD AS SELECT                   | TEST     |       |       |            |          |        |      |
|*  2 |   HASH JOIN                       |          |    65 |  6695 |  1266   (1)| 00:00:16 |        |      |
|   3 |    VIEW                           | VW_NSO_1 |     2 |    26 |     7  (15)| 00:00:01 |        |      |
|   4 |     HASH UNIQUE                   |          |     2 |    94 |     6  (17)| 00:00:01 |        |      |
|   5 |      NESTED LOOPS                 |          |       |       |            |          |        |      |
|   6 |       NESTED LOOPS                |          |     2 |    94 |     5   (0)| 00:00:01 |        |      |
|   7 |        TABLE ACCESS BY INDEX ROWID| D        |     1 |    17 |     2   (0)| 00:00:01 |        |      |
|*  8 |         INDEX RANGE SCAN          | IDX_D    |     1 |       |     1   (0)| 00:00:01 |        |      |
|*  9 |        INDEX RANGE SCAN           | IDX_C    |     2 |       |     2   (0)| 00:00:01 |        |      |
|  10 |       TABLE ACCESS BY INDEX ROWID | C        |     2 |    60 |     3   (0)| 00:00:01 |        |      |
|  11 |    VIEW                           | V_REMOTE |   375K|    32M|  1257   (1)| 00:00:16 |        |      |
|  12 |     UNION-ALL                     |          |       |       |            |          |        |      |
|  13 |      REMOTE                       | A        |   369K|    29M|   454   (2)| 00:00:06 | REMOTE | R->S |
|  14 |      REMOTE                       | B        |  5323 |   431K|     8   (0)| 00:00:01 | REMOTE | R->S |
————————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
   2 – access(“OBJECT_ID”=”OBJECT_ID”)
   8 – access(“D”.”OBJECT_ID”=11)
   9 – access(“C”.”OBJECT_NAME”=”D”.”OBJECT_NAME”)
Remote SQL Information (identified by operation id):
—————————————————-
  13 – SELECT /*+ */ ”OBJECT_NAME”,”OBJECT_ID”,”OBJECT_TYPE” FROM “A” “A” (accessing ‘REMOTE’ )
  14 – SELECT “OBJECT_NAME”,”OBJECT_ID”,”OBJECT_TYPE” FROM “B” “B” (accessing ‘REMOTE’ )   

–與上面類似
DELETE FROM test WHERE object_id IN (
SELECT/*+driving_site(v_remote.a)*/  object_id FROM v_remote WHERE object_id IN (
SELECT c.object_id FROM c WHERE c.object_name IN (SELECT d.object_name FROM d WHERE d.object_id=11)
));

–遠端表操作，自動driving_site(remote)
INSERT INTO xm@remote( object_name,object_id,object_type)
SELECT  *  FROM v_remote WHERE object_id IN (
SELECT c.object_id FROM c WHERE c.object_name IN (SELECT d.object_name FROM d WHERE d.object_id=11)
);

—————————————————————————————
| Id  | Operation                  | Name     | Rows  | Bytes | Cost  | Inst   |IN-OUT|
—————————————————————————————
|   0 | INSERT STATEMENT REMOTE    |          |    15 |  1425 |   109 |        |      |
|   1 | NESTED LOOPS               |          |    15 |  1425 |   109 |        |      |
|   2 | SORT UNIQUE                |          |       |       |       |        |      |
|   3 | VIEW                       | VW_NSO_1 |     3 |    39 |     8 | MZT~ |      |
|   4 | REMOTE                     |          |       |       |       |      ! | R->S |
|   5 | VIEW                       |          |     5 |   410 |    33 |        |      |
|   6 | UNION-ALL PARTITION        |          |       |       |       |        |      |
|   7 | TABLE ACCESS BY INDEX ROWID| A        |    32 |   960 |    35 | MZT~ |      |
|*  8 | INDEX RANGE SCAN           | IDX_A    |    32 |       |     3 | MZT~ |      |
|   9 | TABLE ACCESS BY INDEX ROWID| B        |     1 |    32 |     2 | MZT~ |      |
|* 10 | INDEX RANGE SCAN           | IDX_B    |     1 |       |     1 | MZT~ |      |
—————————————————————————————
Predicate Information (identified by operation id):
—————————————————
   8 – access(“A7″.”OBJECT_ID”=”VW_NSO_1″.”OBJECT_ID”)
  10 – access(“A6″.”OBJECT_ID”=”VW_NSO_1″.”OBJECT_ID”)

Remote SQL Information (identified by operation id):
—————————————————-
   4 – SELECT /*+ */ “A1″.”OBJECT_ID” FROM “D” “A2″,”C” “A1″ WHERE
       ”A1″.”OBJECT_NAME”=”A2″.”OBJECT_NAME” AND “A2″.”OBJECT_ID”=11 (accessing ‘!’ )

–和上面一樣
INSERT INTO xm@remote( object_name,object_id,object_type)
SELECT/*+driving_site(v_remote.a)*/  *  FROM v_remote WHERE object_id IN (
SELECT c.object_id FROM c WHERE c.object_name IN (SELECT d.object_name FROM d WHERE d.object_id=11)
)

     含有dblink的SQL，特別是DML SQL，最佳化是很複雜的，特別是遠端表與本地表結果集都很大，或含有多個不同的遠端物件，這時候更加複雜。透過Collocated inline view，自定義檢視，driving_site hint(當然有時候其它hint也有效，比如use_hash),PL/SQL程式等，在業務允許的情況下也可以透過MV等各種表同步技術，減少dblink使用，可以在一定程度上最佳化含有DBLINK的分散式操作語句。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29960155/viewspace-1376273/，如需轉載，請註明出處，否則將追究法律責任。

相關文章