Oracle hint之DRIVING_SITE

lhrbest發表於2017-03-24
Oracle

Oracle hint之DRIVING_SITE

日常工作中經常會用到分散式資料庫查詢，即通過DBLINK同時查詢本地表和遠端表。分散式查詢一般有兩種處理方式：一種將遠端表資料取回本地，然後和本地表關聯查詢，獲取最終結果；另一種將本地表資料傳到遠端和遠端表關聯查詢後，再將關聯結果取回。前一種處理方式可理解為只有一次網路傳輸操作比後一種少，也就作為了資料庫的預設處理方式；driving_site提示能夠指定執行計劃在遠端還是本地做，使用driving_site，特別是本地小結果集，遠端大結果集，最終結果集較小時，希望計劃在遠端驅動，這樣遠端執行完畢，將結果集傳輸到本地，避免了大結果集的網路傳輸，從而達到整體優化的效果。

但是注意對於DML，DDL語句，driving_site提示是失效的，會自動被ORACLE忽略掉，此時將以目標表所在庫為主計劃驅動，相當於driving_site(目標表庫)；DML，DDL中如果是對本地表做DML，主計劃總是在本地做，會將遠端資料拉到本地，相當於driving_site(本地表)；如果是對遠端表做DML，主計劃總是在遠端做，會將本地資料送到遠端，相當於自動driving_site(遠端表)。

1.  driving_site優化，以減少總體的網路傳輸資料量為目標；

2.  當driving_site驅動的物件巢狀在檢視中時，可通過driving_site(V.T)方式來指定，其中V表示檢視別名或名稱，T表示檢視裡表的別名或名稱；

3. DML，DDL語句中driving_site提示無效，總是會以目標表所在的庫為主計劃驅動，此時可以通過檢視轉換來達到優化目的。

這個提示在分散式資料庫操作中有用，指定表處理連線所在的位置。可以限制通過網路處理的資訊量。此外，還可以建立遠端表的本地檢視來限制從遠端站點檢索的行。本地檢視應該有where子句，從而檢視可以在將行傳送回本地資料庫之前限制從遠端資料庫返回的行。



driving_site用於分散式查詢中，指定資料集傳送到那個資料庫上執行。在某些情況下可以大大提高SQL的效能。下面是一個小測試：
 
1. 在遠端資料庫上，建立測試表test_remote
 
pd@DWTEST>create table test_remote
  2  as
  3  select rownum rn, a.* from user_objects a;
 
Table created.
 
pd@DWTEST>insert into test_remote select * from test_remote;
 
10 rows created.
 
pd@DWTEST>/
 
20 rows created.
 
pd@DWTEST>/
 
40 rows created.
 
pd@DWTEST>/
 
...
 
655360 rows created.
 
pd@DWTEST>update test_remote set rn = rownum;
 
1310720 rows updated.
 
pd@DWTEST>commit;
 
Commit complete.
 
2. 在本地資料庫，建立測試表test_local：
 
C:\Documents and Settings\yuechao.tianyc>sqlplus test/test
 
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 4月 29 14:37:24 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
連線到:
 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> drop table test_local purge;
 
表已刪除。
 
SQL> create table test_local
  2  as
  3  select rownum rn, a.* from user_objects a;
 
表已建立。
 
SQL> insert into test_local select * from test_local;
 
已建立48行。
 
SQL> /
...
 
已建立768行。
 
SQL> update test_local set rn = rownum;
 
已更新1536行。
 
SQL> commit;
 
提交完成。
 
3. 通過driving_site，比較資料在遠端和本地執行速度的不同：
 
-- 1. 直接執行SQL，耗時0.93m，通過執行計劃發現是將遠端表test_remote拉到本地後執行hash join的。
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 100
SQL> explain plan for
  2  select count(*) from test_local l, test_remote@to_s12 r
  3  where l.rn = r.rn;
 
已解釋。
 
已用時間:  00: 00: 00.00
 
SQL> select count(*) from test_local l, test_remote@to_s12 r
  2  where l.rn = r.rn;
 
  COUNT(*)
----------
      1536
 
已用時間:  00: 00: 00.93
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2814429697
 
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |    26 |    10  (10)| 00:00:01 |        |      |
|   1 |  SORT AGGREGATE     |             |     1 |    26 |            |          |        |      |
|*  2 |   HASH JOIN         |             |   327 |  8502 |    10  (10)| 00:00:01 |        |      |
|   3 |    REMOTE           | TEST_REMOTE |   327 |  4251 |     2   (0)| 00:00:01 | TO_S12 | R->S |
|   4 |    TABLE ACCESS FULL| TEST_LOCAL  |  1536 | 19968 |     7   (0)| 00:00:01 |        |      |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("L"."RN"="R"."RN")
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "RN" FROM "TEST_REMOTE" "R" (accessing
       'TO_S12.REGRESS.RDBMS.DEV.US.ORACLE.COM' )
 
Note
-----
   - dynamic sampling used for this statement
 
已選擇27行。
 
已用時間:  00: 00: 00.01
 
-- 2. 通過driving_site，將本地表test_local傳送到遠端執行，再將結果集返回本地。耗時0.34m
 
SQL> select/*+driving_site(r)*/ count(*) from test_local l, test_remote@to_s12 r
  2  where l.rn = r.rn;
 
  COUNT(*)
----------
      1536
 
已用時間:  00: 00: 00.34
 
SQL> explain plan for
  2  select/*+driving_site(r)*/ count(*) from test_local l, test_remote@to_s12 r
  3  where l.rn = r.rn;
 
已解釋。
 
已用時間:  00: 00: 00.14
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3396146028
 
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|             |     1 |    26 |  4970   (1)| 00:01:00 |        |      |
|   1 |  SORT AGGREGATE        |             |     1 |    26 |            |          |        |      |
|*  2 |   HASH JOIN            |             |   327 |  8502 |  4970   (1)| 00:01:00 |        |      |
|   3 |    REMOTE              | TEST_LOCAL  |   327 |  4251 |     3   (0)| 00:00:01 |      ! | R->S |
|   4 |    TABLE ACCESS FULL   | TEST_REMOTE |  1130K|    14M|  4961   (1)| 00:01:00 | DWTEST |      |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A2"."RN"="A1"."RN")
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "RN" FROM "TEST_LOCAL" "A2" (accessing '!' )
 
Note
-----
   - fully remote statement
   - dynamic sampling used for this statement
 
已選擇27行。
 
已用時間:  00: 00: 00.01
 
4. 通過上面的測試，可以發現二者的執行時間是不一樣的。我們再將二者分別執行100次，比較一下平均時間：
 
-- 將遠端表拉到本地執行，耗時65.71（單位為1/100秒）
SQL> set serveroutput on
SQL> declare
  2    n_count number;
  3    n_begin_time number;
  4    n_sum_time number;
  5  begin
  6    n_sum_time := 0;
  7    for n_loop in 1..100 loop
  8      n_begin_time := dbms_utility.get_cpu_time;
  9       select count(*)
 10         into n_count
 11         from test_local l, test_remote@to_s12 r
 12        where l.rn = r.rn;
 13      n_sum_time := n_sum_time + (dbms_utility.get_cpu_time - n_begin_time);
 14    end loop;
 15    dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
 16  end;
 17  /
 
avg cpu_time:65.71
 
PL/SQL 過程已成功完成。
 
已用時間:  00: 01: 28.39
 
-- 將本地表傳送到遠端執行，再將結果返回到本地，耗時0.05（單位為1/100秒）
SQL> declare
  2    n_count number;
  3    n_begin_time number;
  4    n_sum_time number;
  5  begin
  6    n_sum_time := 0;
  7    for n_loop in 1..100 loop
  8      n_begin_time := dbms_utility.get_cpu_time;
  9       select/*+driving_site(r)*/ count(*)
 10         into n_count
 11         from test_local l, test_remote@to_s12 r
 12        where l.rn = r.rn;
 13      n_sum_time := n_sum_time + (dbms_utility.get_cpu_time - n_begin_time);
 14    end loop;
 15    dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
 16  end;
 17  /
 
avg cpu_time:.05
 
PL/SQL 過程已成功完成。
 
已用時間:  00: 00: 23.14
 
5. 結論
 
在分散式查詢中，當一張表比較小，而且最終得到的結果集也比較小的話，使用driving_site將小表傳送到大表端執行是比較快的。



About Me

...............................................................................................................................

● 本文整理自網路

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-03-24 09:00 ~ 2017-03-24 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

 Oracle hint之DRIVING_SITE  DBA筆試面試講解

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

相關文章