ITPub博客

首页 > 数据库 > Oracle > [20140109]显示执行计划的问题.txt

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

原创 Oracle 作者:lfree 时间:2014-01-13 09:17:38 0 删除 编辑

昨天看别人调优,我跟对方讲使用SET Autotrace看执行计划可能不真实的.
自己做一个简单的例子来说明:

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

create table t pctfree 99 as select rownum id ,'test' name from dual connect by level<=1e3;
create index i_t_id on t(id);

exec dbms_stats.gather_table_stats(user, 'T',  method_opt=>'for all columns size 1 ',no_invalidate => false);

variable n number;
exec :n := 10;
select * from t where id>=:n;

2.测试:
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0ck6kww1pfuw8, child number 0
-------------------------------------
select * from t where id>=:n
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    71 (100)|
|*  1 |  TABLE ACCESS FULL| T    |    992 |    71   (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">=:N)

--因为n=10,很小,oracle 的绑定变量peek发现选择全表扫描更加合理.

SCOTT@test> set autotrace traceonly
SCOTT@test> select * from t where id>=:n;
991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    50 |   450 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    50 |   450 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |     9 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=TO_NUMBER(:N))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        250  consistent gets
          0  physical reads
          0  redo size
      10185  bytes sent via SQL*Net to client
        563  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        991  rows processed

3.突然想起以前遇到的问题,set autotrace traceonly explain问题,这个还可能导致执行计划的改变.
http://blog.itpub.net/267265/viewspace-716004/

SCOTT@test> alter system flush shared_pool;
System altered.

SCOTT@test> select * from v$sql where sql_id='0ck6kww1pfuw8';
no rows selected

SCOTT@test> set autotrace traceonly explain
SCOTT@test> select * from t where id>=:n;
Execution Plan
---------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    50 |   450 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    50 |   450 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |     9 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=TO_NUMBER(:N))

SCOTT@test> set autotrace off
SCOTT@test> select sql_id,child_number,sql_text from v$sql where sql_id='0ck6kww1pfuw8';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------
0ck6kww1pfuw8            0 select * from t where id>=:n
--可以发现已经生成了执行计划.

SCOTT@test> select * from t where id>=:n;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0ck6kww1pfuw8, child number 0
-------------------------------------
select * from t where id>=:n
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |     5 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     50 |     5   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      9 |     2   (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=:N)
  
--可以发现执行计划改变.


总结:
--可以发现看到的执行计划选择索引,而实际上根本不是这么回事.实际上如果你使用 explain plan for...,在使用
--select * from table(dbms_xplan.display());查询也是一样的问题.

--总之10g以后看执行计划最好使用dbms_xplan.display_cursor来看.或者不使用参数看执行计划,也许这样会准一点.

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

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2699
  • 访问量
    6484671