11g改变了DELETE语句的执行计划

 

在11.2中,如果DELETE的时候没有限制条件,且表上存在主键的话,执行计划会变为索引全扫。

 

 

 

 

 

在和600聊天的时候听说了这个现象,开始的时候还不是很相信。当时600特意验证了一下,事实确实如此。

 

于是特意自己也做了个简单的例子:

 

SQL> select * from v$version;

 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

 

SQL> create table t_del as select rownum id, a.* from dba_objects a, user_tables ;

 

Table created.

 

SQL> select count(*) from t_del;

 

  COUNT(*)
----------
    110360

 

SQL> alter table t_del add primary key (id);

 

Table altered.

 

SQL> explain plan for delete t_del;

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1780357700

 

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | DELETE STATEMENT |             | 89885 |  1141K|   245   (1)| 00:00:03 |
|   1 |  DELETE          | T_DEL       |       |       |            |          |
|   2 |   INDEX FULL SCAN| SYS_C006177 | 89885 |  1141K|   245   (1)| 00:00:03 |
--------------------------------------------------------------------------------

 

Note
-----
   - dynamic sampling used for this statement (level=2)

 

13 rows selected.

 

SQL> explain plan for delete /*+ full(t_del) */ t_del;

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2195693323

 

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |       | 89885 |  1141K|   426   (1)| 00:00:06 |
|   1 |  DELETE            | T_DEL |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_DEL | 89885 |  1141K|   426   (1)| 00:00:06 |
----------------------------------------------------------------------------

 

Note
-----
   - dynamic sampling used for this statement (level=2)

 

13 rows selected.

 

Oracle认为全索引扫描的代价接近全表扫描的一半,预估时间也只有全表扫描的一半。这中执行计划对于10g以前是不可想象的,既然所有的记录都要处理,通过全表扫描显然是最合适的方法,而通过索引去定位每条记录显然效率要低很多。

 

那么到底是Oracle改变了实现方式,还是11.2的CBO在这里犯了错误呢,真正执行一下看看效果:

 

SQL> set timing on
SQL> set autot trace
SQL> delete t_del;

 

110360 rows deleted.

 

Elapsed: 00:00:01.23

 

Execution Plan
----------------------------------------------------------
Plan hash value: 1780357700

 

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | DELETE STATEMENT |             |     1 |    13 |   245   (1)| 00:00:03 |
|   1 |  DELETE          | T_DEL       |       |       |            |          |
|   2 |   INDEX FULL SCAN| SYS_C006177 |     1 |    13 |   245   (1)| 00:00:03 |
--------------------------------------------------------------------------------

 

Note
-----
   - dynamic sampling used for this statement (level=2)

 


Statistics
----------------------------------------------------------
        247  recursive calls
     124999  db block gets
        395  consistent gets
        270  physical reads
   43099720  redo size
        678  bytes sent via SQL*Net to client
        585  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     110360  rows processed

 

SQL> rollback;

 

Rollback complete.

 

Elapsed: 00:00:00.92
SQL> delete /*+ full(t_del) */ t_del;

 

110360 rows deleted.

 

Elapsed: 00:00:04.63

 

Execution Plan
----------------------------------------------------------
Plan hash value: 2195693323

 

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |       |     1 |    13 |   425   (0)| 00:00:06 |
|   1 |  DELETE            | T_DEL |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_DEL |     1 |    13 |   425   (0)| 00:00:06 |
----------------------------------------------------------------------------

 

Note
-----
   - dynamic sampling used for this statement (level=2)

 


Statistics
----------------------------------------------------------
        322  recursive calls
     346841  db block gets
       1731  consistent gets
        593  physical reads
   65160536  redo size
        683  bytes sent via SQL*Net to client
        604  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     110360  rows processed

 

SQL> rollback;

 

Rollback complete.

 

Elapsed: 00:00:01.69

 

显然无论从运行时间,还是db block gets数量,或者是逻辑读或物理读的数量,11.2的全索引扫描执行路径都要远小于全表扫描的方式,甚至连产生的redo的数据量都只有全表扫描的2/3,显然Oracle更改了删除操作的处理机制,才使得全索引扫描这种看上去完全不合理的执行计划可以提高性能。

 

DELETE操作是Oracle所有DML中代价最大的,看来Oracle认识到了这一点,也在试图改变这种情形。

   

上一篇:【UI组件库】


下一篇:Docker快速搭建Oracle服务