ITPub博客

首页 > Linux操作系统 > Linux操作系统 > autotrace 和explain plan for可能导致执行计划错误

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

原创 Linux操作系统 作者:wei-xh 时间:2013-09-27 15:27:56 0 删除 编辑
[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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 漫谈log file sync
请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2302109