[20150810]關於提示DRIVING_SITE.txt

[20150810]關於提示DRIVING_SITE.txt

--今天看了提示DRIVING_SITE的使用,透過例子來說明：

1.測試環境：
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--測試前我禁用的主外來鍵關係。dblink :test089.com是10g的資料庫。

SCOTT@test> SELECT  count(*) FROM emp,  dept WHERE emp.deptno = dept.deptno;
  COUNT(*)
----------
        14

Plan hash value: 2112491333
--------------------------------------------------------------------------------
| Id  | Operation           | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |        |       |     3 (100)|          |
|   1 |  SORT AGGREGATE     |         |      1 |     6 |            |          |
|   2 |   NESTED LOOPS      |         |     14 |    84 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP     |     14 |    42 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |      1 |     3 |     0   (0)|          |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / EMP@SEL$1
   4 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

--執行計劃先選擇全表掃描emp，然後dept索引，再nested loop。如果加上執行如下：

2.測試：
SELECT  count(*) FROM emp@test089.com, dept WHERE emp.deptno = dept.deptno;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cdk4c17rdzu23, child number 0
-------------------------------------
SELECT  count(*) FROM emp@test089.com, dept WHERE emp.deptno =dept.deptno
Plan hash value: 2629410705
------------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |        |       |     3 (100)|          |        |      |
|   1 |  SORT AGGREGATE     |         |      1 |    16 |            |          |        |      |
|   2 |   NESTED LOOPS      |         |     14 |   224 |     3   (0)| 00:00:01 |        |      |
|   3 |    REMOTE           | EMP     |     14 |   182 |     3   (0)| 00:00:01 | TEST0~ | R->S |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |      1 |     3 |     0   (0)|          |        |      |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
   3 - SEL$1 / EMP@SEL$1
   4 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "DEPTNO" FROM "EMP" "EMP" (accessing 'TEST089.COM' )

--如果按照上面的執行，將SELECT "DEPTNO" FROM "EMP" "EMP" (accessing 'TEST089.COM' )取回到本地再執行。如果表emp(在
--TEST089.COM)很大，傳輸過來消耗很大，可以透過提示DRIVING_SITE改變處理的方式：

3.使用DRIVING_SITE提示：

SCOTT@test> SELECT  /*+ DRIVING_SITE(emp) */ count(*) FROM emp@test089.com, dept WHERE emp.deptno = dept.deptno;
  COUNT(*)
----------
        14

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g7dc589vcrrbn, child number 0

SELECT  /*+ DRIVING_SITE(emp) */ count(*) FROM emp@test089.com, dept
WHERE emp.deptno = dept.deptno

NOTE: cannot fetch plan for SQL_ID: g7dc589vcrrbn, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

--可以發現一個小問題，加入提示DRIVING_SITE後無法在本地看到執行計劃。使用explain plan呢？

SCOTT@test> explain plan for SELECT  /*+ DRIVING_SITE(emp) */ count(*) FROM emp@test089.com, dept WHERE emp.deptno = dept.deptno;
Explained.

PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 2705760024
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   798 |     7  (15)| 00:00:01 |        |      |
|   1 |  MERGE JOIN                  |         |    14 |   798 |     7  (15)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     5 |   100 |     3   (0)| 00:00:01 |        |      |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     5 |       |     1   (0)| 00:00:01 |        |      |
|*  4 |   SORT JOIN                  |         |    14 |   518 |     4  (25)| 00:00:01 |        |      |
|   5 |    REMOTE                    | EMP     |    14 |   518 |     3   (0)| 00:00:01 | TEST0~ | R->S |
--------------------------------------------------------------------------------------------------------
...
Remote SQL Information (identified by operation id):
----------------------------------------------------
   5 - SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP" "EMP"
       (accessing 'TEST089.COM' )

--很明顯這個時候使用explain plan看到的執行計劃存在一定的誤導，正確嗎？繼續看下面的測試：

4.改用其它方式觀察：
SCOTT@test> set autot traceonly
SCOTT@test> SELECT  /*+ DRIVING_SITE(emp) */ count(*) FROM emp@test089.com, dept WHERE emp.deptno = dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 567242089
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     1 |    16 |     3   (0)| 00:00:01 |        |      |
|   1 |  SORT AGGREGATE        |      |     1 |    16 |            |          |        |      |
|   2 |   NESTED LOOPS         |      |    14 |   224 |     3   (0)| 00:00:01 |        |      |
|   3 |    TABLE ACCESS FULL   | EMP  |    14 |    42 |     3   (0)| 00:00:01 |   TEST |      |
|   4 |    REMOTE              | DEPT |     1 |    13 |     0   (0)| 00:00:01 |      ! | R->S |
-----------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - SELECT "DEPTNO" FROM "DEPT" "A1" WHERE :1="DEPTNO" (accessing '!' )
Note
-----
   - fully remote statement
Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--這個是用test089.com看到的執行計劃嗎？驗證看看：

5.在089機器上：

SCOTT@test> alter system flush shared_pool;
System altered.

--在原來會話再次執行：
set autot off
SELECT  /*+ DRIVING_SITE(emp) */ count(*) FROM emp@test089.com, dept WHERE emp.deptno = dept.deptno;

--089機器:

SCOTT@test> column SQL_TEXT format a100

SCOTT@test> select sql_id,sql_text from v$sql where module='oracle@hisdg (TNS V1-V3)';
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
269cd69gkg3h4 SELECT COUNT(*) FROM "EMP" "A2","DEPT"@! "A1" WHERE "A2"."DEPTNO"="A1"."DEPTNO"
bvggqsm04bnjc SELECT /*+ FULL(P) +*/ * FROM "EMP" P

--奇怪這邊emp選擇全表掃描?

SCOTT@test> @dpc 269cd69gkg3h4 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  269cd69gkg3h4, child number 0
-------------------------------------
SELECT COUNT(*) FROM "EMP" "A2","DEPT"@! "A1" WHERE "A2"."DEPTNO"="A1"."DEPTNO"
Plan hash value: 567242089
---------------------------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |    16 |            |          |        |      |
|   2 |   NESTED LOOPS      |      |     14 |   224 |     3   (0)| 00:00:01 |        |      |
|   3 |    TABLE ACCESS FULL| EMP  |     14 |    42 |     3   (0)| 00:00:01 |        |      |
|   4 |    REMOTE           | DEPT |      1 |    13 |     0   (0)|          |      ! | R->S |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / A2@SEL$1
   4 - SEL$1 / A1@SEL$1

--10g存在小小問題，看不到遠端執行的sql語句。不過可以猜出執行的是
--SELECT "DEPTNO" FROM "DEPT" "A1" WHERE :1="DEPTNO" (accessing '!' ),這裡的INst標識是!,IN-OUT 是 R->S.

--換1句話將執行計劃變成了在test089上執行：
SELECT COUNT(*) FROM "EMP" "A2",DEPT@test040.com "A1" WHERE "A2"."DEPTNO"="A1"."DEPTNO";
然後把就傳過去。

--總之，提示DRIVING_SITE可能導致本地看不到執行計劃，主要目的是減少網路傳輸。這些細節給注意。