ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131121]奇怪的执行计划变化.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-11-21 11:28:01 0 删除 编辑
[20131121]奇怪的执行计划变化.txt

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,lpad('x',1000,'x')  name from dual connect by level<=1e3;
create index i_t_id on t(id) ;
exec dbms_stats.gather_table_stats(user, 'T',  no_invalidate => false);

SCOTT@test> alter session set statistics_level=all;
Session altered.

SCOTT@test> select avg(id) from t;
   AVG(ID)
----------
     500.5

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1kadrxzj9ahk7, child number 0
-------------------------------------
select avg(id) from t
Plan hash value: 3548397654
-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |     3 (100)|      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE       |        |      1 |      1 |            |      1 |00:00:00.01 |       6 |
|   2 |   INDEX FAST FULL SCAN| I_T_ID |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------
14 rows selected.

--可以发现执行计划选择了INDEX FAST FULL SCAN.逻辑读=6.


但是如果语句修改为select avg(id)+1 from t; 加入了运算,结果如何呢?
SCOTT@test> select avg(id)+1 from t;
 AVG(ID)+1
----------
     501.5

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4d54cp5vqmzt0, child number 0
-------------------------------------
select avg(id)+1 from t
Plan hash value: 2966233522
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   279 (100)|      1 |00:00:00.01 |    1004 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |    1004 |
|   2 |   TABLE ACCESS FULL| T    |      1 |   1000 |   279   (0)|   1000 |00:00:00.01 |    1004 |
--------------------------------------------------------------------------------------------------
14 rows selected.

--仅仅改为avg(id)+1,执行计划就发生了变化,变为TABLE ACCESS FULL.逻辑读=1004.
--加入条件id is not null 才可以解决这个问题.

SCOTT@test> select avg(id)+1 from t where id is not null;
 AVG(ID)+1
----------
     501.5

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7bpga35zfgxb0, child number 0
-------------------------------------
select avg(id)+1 from t where id is not null
Plan hash value: 3548397654
-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |     3 (100)|      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE       |        |      1 |      1 |            |      1 |00:00:00.01 |       6 |
|*  2 |   INDEX FAST FULL SCAN| I_T_ID |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID" IS NOT NULL)
19 rows selected.


--修改id为not null.
SCOTT@test> alter table scott.t modify(id  not null);
Table altered.

SCOTT@test> select avg(id)+1 from t ;
 AVG(ID)+1
----------
     501.5

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4997mr261xaua, child number 0
-------------------------------------
select avg(id)+1 from t
Plan hash value: 3548397654
-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |     3 (100)|      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE       |        |      1 |      1 |            |      1 |00:00:00.01 |       6 |
|   2 |   INDEX FAST FULL SCAN| I_T_ID |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------

参考:http://alexanderanokhin.wordpress.com/2013/11/16/filter-is-not-null/
给出了更加有意思的测试.

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

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

注册时间:2008-01-03

  • 博文量
    2456
  • 访问量
    6259638