开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[20150810]关于提示DRIVING_SITE.txt

简介: [20150810]关于提示DRIVING_SITE.txt --今天看了提示DRIVING_SITE的使用,通过例子来说明: 1.测试环境: SCOTT@test> @ver1 PORT_STRING                    VER...
+关注继续查看

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
[20160513]重温11g DRCP.txt
[20160513]重温11g DRCP.txt --以前做过一次测试,再也没有使用过. [20130730]11G的DRCP特性.txt => http://blog.
905 0
[20150929]11g关于行链接.txt
[20150929]11g关于行链接.txt --曾经写过block record flag: http://blog.itpub.net/267265/viewspace-1753924/ http://blog.
855 0
[20150710]11G谓词推入问题2.txt
[20150710]11G谓词推入问题2.txt --生产系统遇到一个sql语句的问题. --生产系统的sql语句比较复杂,做一个简化的例子来说明问题.来说明自己优化遇到的困惑。
693 0
[20150402]11G ACS使用问题.txt
[20150402]11G ACS使用问题.txt --11G ACS(adaptive cursor sharing) 是11G的新特性,主要用来解决绑定变量窥视的问题,当时使用绑定变量时如果带入的参数插线倾斜 --,导致执行计划出现问题.
804 0
[20120803]11G SPM的学习1.txt
[20120803]11G SPM的学习1.txt    开始学习SQL Plan Management(SPM) ,11G开始提供SPM,在10g下我经常使用sql profile看一些bad sql语句,sql profile我觉得已经做的很好,有时候能够提供很好的建议。
658 0
[20120810]11GR2的flashback database.txt
[20120810]11GR2的flashback database.txtflashback database是oracle 10g下非常好的特性,它可以回滚到特定的时刻,而不需要rman之类的程序恢复.
654 0
[20120806]11G SPM的学习4.txt
[20120806]11G SPM的学习4.txt继续上面的学习:SQL> select * from v$version ; BANNER --------------------------------------------------...
689 0
[20120523]关于11GR2无法通过logminer看到DML的问题.txt
[20120523]关于11GR2无法通过logminer看到DML的问题.txt昨天工作需要,使用logminer挖掘一些dml信息,我发现漏掉一些信息,也就是一些dml语句无法抓取.
692 0
[20111230]11Gr2审计.txt
[20111230]11Gr2审计.txt昨天查看v$open_cursor视图,无意中发现如下语句:INSERT INTO SYS.aud$            (sessionid, entryid, STATEMENT, ntimestamp#, use...
825 0
[20111221]关于11G Virtual columns[补充].txt
接着在做一些测试:SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.
679 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
NLP with MLlib--Global Empire Building for Fun and Profit
立即下载
Expert Tips to Command Your Na
立即下载
Borgaonkar-New-Adventures-In-Spying-3G-And-4G-Users-Locate-Track-And-Monitor
立即下载