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

最新推荐文章于 2021-04-12 03:32:31 发布

ckawt40802

最新推荐文章于 2021-04-12 03:32:31 发布

阅读量146

点赞数

文章标签：数据库

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

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

转载于:http://blog.itpub.net/4227/viewspace-709817/