[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可能导致本地看不到执行计划，主要目的是减少网络传输。这些细节给注意。