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