[20140221]执行计划以及dblink.txt

[20140221]执行计划以及dblink.txt

--昨天我调整一个sql语句,忽略一个细节,sql里面使用了视图,视图里面使用dblink.我正是关注逻辑读不大,让我忽视了优化.
--举一些例子来说明问题.

1.建立测试环境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--在本地建立一张表t.主要是为了测试的需要.

SCOTT@test> create table t as select dept_code from dept_dict@test1.com where rownumTable created.

SCOTT@test> exec dbms_stats.gather_table_stats(user, 't', method_opt => 'FOR ALL COLUMNS SIZE 1',no_invalidate=>false);
PL/SQL procedure successfully completed.

2.测试1:

SCOTT@test> select * from dept_dict@test1.com where dept_code='0202';
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------
SQL_ID  4nuvmzgzyb8b5, child number 0

select * from dept_dict@test1.com where dept_code='0202'

NOTE: cannot fetch plan for SQL_ID: 4nuvmzgzyb8b5, 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)

--可以发现如果sql语句仅仅包含dblink的链接是无法看执行计划的.

SCOTT@test> set autotrace traceonly
SCOTT@test> select /*+ full(a) */ * from dept_dict@test1.com a where dept_code='0202';
Execution Plan
----------------------------------------------------------
Plan hash value: 4107200483
---------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|           |     1 |    50 |     4   (0)| 00:00:01 |        |
|*  1 |  TABLE ACCESS FULL     | DEPT_DICT |     1 |    50 |     4   (0)| 00:00:01 |   ORCL |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A1"."DEPT_CODE"='0202')

Note
-----
   - fully remote statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1403  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--注意:提示写成 /*+ full(dept_dict@test1.com) */ 不行.必须使用别名.
--可以发现远端执行的是全表扫描,但是记录的逻辑读是0,

--如果查询远端实际上执行的是
JSJS@209> @dpc 21p6sd8a4pz7m ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  21p6sd8a4pz7m, child number 0
-------------------------------------
SELECT /*+ FULL ("A1") */ "A1"."SERIAL_NO","A1"."DEPT_CODE","A1"."DEPT_N
AME","A1"."DEPT_ALIAS","A1"."CLINIC_ATTR","A1"."OUTP_OR_INP","A1"."INTER
NAL_OR_SERGERY","A1"."INPUT_CODE","A1"."RECKON_UNIT_OUTP","A1"."RECKON_U
NIT_INP","A1"."DEPT_INTRODUCTION","A1"."DEPT_ADDRESS" FROM "DEPT_DICT"
"A1" WHERE "A1"."DEPT_CODE"='0202'
Plan hash value: 4107200483
-------------------------------------------------------------
| Id  | Operation         | Name      | E-Rows | Cost (%CPU)|
-------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| DEPT_DICT |      1 |     4   (0)|
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A1"."DEPT_CODE"='0202')

3.测试2:

--加入一张本地表:
SCOTT@test> select * from t where dept_code in (select /*+ full(a) */ dept_code from dept_dict@test1.com a where dept_code='010101');
DEPT_COD
--------
010101

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  a2cd5qp3dfk4j, child number 0
-------------------------------------
select * from t where dept_code in (select /*+ full(a) */ dept_code
from dept_dict@test1.com a where dept_code='010101')

Plan hash value: 2256468146

--------------------------------------------------------------
| Id  | Operation          | Name      | E-Rows | Cost (%CPU)|
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |        |     7 (100)|
|   1 |  NESTED LOOPS      |           |      1 |     7   (0)|
|   2 |   REMOTE           | DEPT_DICT |      1 |     4   (0)|
|*  3 |   TABLE ACCESS FULL| T         |      1 |     3   (0)|
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPT_CODE"='010101')

SCOTT@test> set autotrace traceonly
SCOTT@test> select * from t where dept_code in (select /*+ full(a) */ dept_code from dept_dict@test1.com a where dept_code='010101');
Execution Plan
----------------------------------------------------------
Plan hash value: 2256468146
------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    12 |     7   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS      |           |     1 |    12 |     7   (0)| 00:00:01 |        |      |
|   2 |   REMOTE           | DEPT_DICT |     1 |     6 |     4   (0)| 00:00:01 |  TEST1 | R->S |
|*  3 |   TABLE ACCESS FULL| T         |     1 |     6 |     3   (0)| 00:00:01 |        |      |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DEPT_CODE"='010101')
Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - SELECT /*+ FULL ("A") */ "DEPT_CODE" FROM "DEPT_DICT" "A" WHERE
       "DEPT_CODE"='010101' (accessing 'TEST1.COM' )
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--逻辑读仅仅为3,这并不包括远程访问的逻辑读.不过set autotrace traceonly看有一个好处,显示了远端的执行语句实际是:
   2 - SELECT /*+ FULL ("A") */ "DEPT_CODE" FROM "DEPT_DICT" "A" WHERE
       "DEPT_CODE"='010101' (accessing 'TEST1.COM' )
--单独访问远端表是没有这个内容的.

--实际上这个信息记录在v$sql_plan视图的other字段.这样调用dbms_xplan.display_cursor应该可以看到远端的执行计划:

SCOTT@test> column other format a100
SCOTT@test> select other,other_tag from v$sql_plan where sql_id='a2cd5qp3dfk4j' and object_type='REMOTE';
OTHER                                                                                                OTHER_TAG
---------------------------------------------------------------------------------------------------- --------------------
SELECT /*+ FULL ("A") */ "DEPT_CODE" FROM "DEPT_DICT" "A" WHERE "DEPT_CODE"='010101'                 SERIAL_FROM_REMOTE

我的dpc.sql脚本如下:
$ cat dpc.sql
set verify off
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost'));

--看了一下文档,看来要在加入all参数.
---        'ALL':     Maximum user level, like typical with additional
---                   informations (PROJECTION, ALIAS and information about
---                   REMOTE SQL if the operation is distributed).

SCOTT@test> @dpc '' all
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a2cd5qp3dfk4j, child number 0
-------------------------------------
select * from t where dept_code in (select /*+ full(a) */ dept_code
from dept_dict@test1.com a where dept_code='010101')

Plan hash value: 2256468146

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |        |       |     7 (100)|          |        |      |
|   1 |  NESTED LOOPS      |           |      1 |    12 |     7   (0)| 00:00:01 |        |      |
|   2 |   REMOTE           | DEPT_DICT |      1 |     6 |     4   (0)| 00:00:01 |  TEST1 | R->S |
|*  3 |   TABLE ACCESS FULL| T         |      1 |     6 |     3   (0)| 00:00:01 |        |      |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / A@SEL$2
   3 - SEL$5DA710D3 / T@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPT_CODE"='010101')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DEPT_CODE"[VARCHAR2,8]
   2 - "DEPT_CODE"[VARCHAR2,8]
   3 - "DEPT_CODE"[VARCHAR2,8]

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT /*+ FULL ("A") */ "DEPT_CODE" FROM "DEPT_DICT" "A" WHERE
       "DEPT_CODE"='010101' (accessing 'TEST1.COM' )

--这样就可以看到远端执行的sql语句,优化变得很容易.
--我遇到的问题实际上隐式转换的问题,问题语句使用绑定变量,看语句很难发现问题.修改上面的例子看看.
SCOTT@test> select * from t where dept_code in (select /*+ full(a) */ dept_code from dept_dict@test1.com a where dept_code=010101);
DEPT_COD
--------
010101

SCOTT@test> @dpc '' all
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6k6aybav5pgnr, child number 0
-------------------------------------
select * from t where dept_code in (select /*+ full(a) */ dept_code
from dept_dict@test1.com a where dept_code=010101)

Plan hash value: 2085754

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |        |       |     8 (100)|          |        |      |       |       |          |
|*  1 |  HASH JOIN         |           |      1 |    12 |     8  (13)| 00:00:01 |        |      |  1306K|  1306K|  420K (0)|
|   2 |   REMOTE           | DEPT_DICT |      1 |     6 |     4   (0)| 00:00:01 |  TEST1 | R->S |       |       |          |
|   3 |   TABLE ACCESS FULL| T         |     10 |    60 |     3   (0)| 00:00:01 |        |      |       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / A@SEL$2
   3 - SEL$5DA710D3 / T@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPT_CODE"="DEPT_CODE")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "DEPT_CODE"[VARCHAR2,8]
   2 - "DEPT_CODE"[VARCHAR2,8]
   3 - "DEPT_CODE"[VARCHAR2,8]

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT /*+ FULL ("A") */ "DEPT_CODE" FROM "DEPT_DICT" "A" WHERE TO_NUMBER("DEPT_CODE")=010101 (accessing
       'TEST1.COM' )

--在远端执行的sql语句如下:
   2 - SELECT /*+ FULL ("A") */ "DEPT_CODE" FROM "DEPT_DICT" "A" WHERE TO_NUMBER("DEPT_CODE")=010101 (accessing 'TEST1.COM' )

--很明显,连接的表建立一个函数索引就解决了或者修改sql代码.

时间： 2024-09-28 23:36:32

[20140221]执行计划以及dblink.txt的相关文章

[20140109]显示执行计划的问题.txt

昨天看别人调优,我跟对方讲使用SET Autotrace看执行计划可能不真实的. 自己做一个简单的例子来说明: 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production cr

[20161216]toad下显示真实的执行计划.txt

[20161216]toad下显示真实的执行计划.txt --大家都应该知道使用explain plan看执行计划,有时候显示的执行计划不是真实的执行计划.现在我虽然使用它看,仅仅作为参考. --昨天看链接: http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/12/13/toad-explain-plan-tip-returning-actual-sql-execution-explain-plan --才知道toad

[20131121]奇怪的执行计划变化.txt

[20131121]奇怪的执行计划变化.txt SCOTT@test> @verBANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production create table t pctfree 99 as select rownum id,l

[20130909]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED.txt

[20130909]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED.txt 在写[20130809]12c Clustering Factor.txt,链接 提到执行计划里面存在TABLE ACCESS BY INDEX ROWID BATCHED,这里的BATCHED表示什么? 自己不是很清楚. 既然多了一个batched,一定与原来的不同,具体含义是什么呢?做一些简单的探究: 1.建立测试环境: SCOTT@test01p> @ver BANNER  

[20120104]稳定一条sql语句的执行计划.txt

[20120104]稳定一条sql语句的执行计划.txt http://www.itpub.net/thread-1495845-1-1.htmlhttp://space.itpub.net/267265/viewspace-723066 ORACLE8I升级11G R2后,查询系统视图特别慢 我的测试版本:SQL> select * from v$version where rownumBANNER------------------------------------------------

[20120214]异常数据导致执行计划改变.txt

今天上午,用户反应一条sql执行有点慢.我检查发现,原来使用索引的语句现在变成了全表扫描,而且昨晚oracle数据库自动分析过这个表. 语句很复杂,抽取有问题的部分: SELECT *  FROM med_operation_schedule a WHERE (       scheduled_date_time >= TO_DATE ('2012-02-15 00:00', 'yyyy-mm-dd hh24:mi')           AND scheduled_date_time     

[20130910]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED(补充).txt

[20130910]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED(补充).txt 链接http://space.itpub.net/267265/viewspace-772371 写了12c下在范围扫描时可能出现的TABLE ACCESS BY INDEX ROWID BATCHED,这是一种新的执行方式,能够提高执行效率,特别在数据聚集很好的情况下. 既然是12c的一个特性应该有一个参数关闭这个特性.重复前面的例子: 1.建立测试环境: SCOTT@t

[20120915]10046事件与执行计划改变.txt

    使用10046事件来跟踪解决oracle的许多问题,是非常常用的手段,但是实际上可能出现跟踪的sql执行计划与原来不同的情况,自己应该引起注意. 测试如下: 1.测试环境建立: SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition

[20120112]谨慎使用set autotrace traceonly查看执行计划.txt

自己经常在优化sql语句是经常使用set autotrace traceonly来优化sql语句.这样的好处是如果输出记录很多,可以不受干扰,直接查看执行计划以及执行计划的统计信息.但是在一些特殊情况要注意也许执行计划是不真实的. SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g