autotrace 和explain plan for可能导致执行计划错误

[i=s] 本帖最后由 wei-xh 于 2013-9-27 15:26 编辑

使用autotrace工具和explain plan for来获得SQL的执行计划，有可能会出现与实际的执行计划不符的情况。autotrace本身其实也是调用了explain plan for来获取的执行计划。
本文总结了在使用这两种工具过程中，最常见的导致执行计划与实际情况不符的两个场景。
第一种情况：数据类型隐式转换
使用explain plan for工具时，要意识到ORACLE会把你传入的绑定变量作为varchar2类型来处理。
create table wxh_tbd(id varchar2(100));
create index wxh_ind on wxh_tbd(id);
exec dbms_stats.gather_table_stats(user,'wxh_tbd');

SQL> var c number;
SQL> var d varchar2
SQL>
SQL> exec :c :=1

PL/SQL procedure successfully completed.

SQL> exec :d :='1'

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL>
SQL> alter session set events '10046 trace name context forever ,level 12' ;  

Session altered.

上面创建了一张表，表里只有一个字符类型的字段。声明了两个变量，一个为字符型，一个为数字型。
SQL> select count(*) from wxh_tbd where id= :d;

  COUNT(*)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 3024061193

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| WXH_IND |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("ID"=:D)


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



SQL> select count(*) from wxh_tbd where id= :c;

  COUNT(*)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 3024061193

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| WXH_IND |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("ID"=:C)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

我们看到两次查询走的执行计划是一样的，都走了索引扫描，但是产生的逻辑读差异是很大的，这里面有猫腻！
虽然我们定义的c变量为number，但是explain plan for忽略了这个事实，仅仅把它作为varchar2来处理，因此执行计划显示的，还是走了索引扫描。
我们看看10046 trace的结果：
从跟踪文件中很容易看到set autotrace 本身调用了explain plan for来产生执行计划。

EXPLAIN PLAN SET STATEMENT_ID='PLUS456426' FOR select count(*) from wxh_tbd
  where id= :d


EXPLAIN PLAN SET STATEMENT_ID='PLUS456426' FOR select count(*) from wxh_tbd
  where id= :c

跟踪文件里清楚的记录了，SQL的真实执行计划为全表扫描，发生了数据类型的隐式转换：
select count(*)
from
wxh_tbd where id= :c


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         60          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.01          0         60          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 45
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=30 pr=0 pw=0 time=2886 us)
         1          1          1   TABLE ACCESS FULL WXH_TBD (cr=30 pr=0 pw=0 time=2870 us cost=7 size=5 card=1)

第二种情况：绑定变量窥探，用explain plan for并不会受绑定变量窥探的影响，因此在列存在直方图的情况下，有可能explain plan for产生的执行计划于实际的执行计划不符。
SQL> drop table a;

Table dropped.

SQL> create table a as select object_id,object_type from dba_objects;

Table created.

SQL> insert into a select  object_id,'TABLE' object_type from dba_objects;

17537 rows created.

SQL> commit;

Commit complete.

SQL> create index a_i on a(object_type);

Index created.

SQL> begin
  2    dbms_stats.gather_table_stats(ownname          =>'dlsp',
  3                                  tabname          => 'a',
  4                                  no_invalidate    => FALSE,
  5                                  estimate_percent => 100,
  6                                  force            => true,
  7                                  degree         => 5,
  8                                  method_opt       => 'for  columns object_type size 10',
  9                                  cascade          => false);
10  end;
11  /


SQL> @tabstat
Please enter Name of Table Owner: dlsp
Please enter Table Name : a

**********************************************************
Table Level
**********************************************************


Table                                  Number                        Empty    Chain Average Global         Sample Date
Name                                  of Rows          Blocks       Blocks    Count Row Len Stats            Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
A                                      35,074            0,85            0        0      10 YES            35,074 09-27-2013

Column                             Distinct              Number       Number         Sample Date
Name                                 Values     Density Buckets        Nulls           Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
OBJECT_ID                            17,536   .00005703       1            2         35,072 09-27-2013
OBJECT_TYPE                              38   .07672567      10            0         35,074 09-27-2013

Index                                     Leaf       Distinct         Number      AV       Av      Cluster Date
Name                           BL         Blks           Keys        of Rows     LEA     Data       Factor MM-DD-YYYY
------------------------------ -- ------------ -------------- -------------- ------- -------- ------------ ----------
A_I                             1           86             38         35,074       2       10          398 09-27-2013

Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
A_I                            OBJECT_TYPE                       1 VARCHAR2(19)



SQL> var c varchar2(100)
SQL>  exec :c := 'TABLE'

PL/SQL procedure successfully completed.

经过上面的代码，我们在object_type上产生了一个直方图，并且object_type存在数据倾斜，表中存在大量的object_type为TABLE的值。

SQL> select count(*) from a where object_type=:c;

  COUNT(*)
----------
     19555

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3009055403

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| A_I  |   923 |  6461 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"=:C)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         83  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

获得SQL_ID后，查看真实的执行计划。发现走的实际为全表扫描的执行计划，还可以通过Peeked Binds部分看到此执行计划窥探了实际的变量值

SQL> select * from table(dbms_xplan.display_cursor('7q143s0aqfm1q',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7q143s0aqfm1q, child number 0
-------------------------------------
select count(*) from a where object_type=:c

Plan hash value: 2223038180

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    17 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| A    | 21044 |   143K|    17   (6)| 00:00:01 |
---------------------------------------------------------------------------



Outline Data

Peeked Binds (identified by position):
--------------------------------------

   1 - :C (VARCHAR2(30), CSID=871): 'TABLE'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-773482/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-773482/