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认识到了这一点,也在试图改变这种情形。

This entry was posted in ORACLE and tagged , , . Bookmark the permalink.

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

  1. Kamus says:

    很有意思。但是“显然Oracle更改了删除操作的处理机制”,那么具体是怎样更改了呢?

Leave a Reply

Your email address will not be published. Required fields are marked *