引题：受人之托，写了这篇文章，貌似这个问题也困扰了许多人，希望可以给大家一点启发

Test

1.    创建一个用户leonarding并授予dba权限

SYS@LEO> create user leonarding identified by leonarding default tablespace users;

SYS@LEO> grant dba to leonarding;

2.    创建一个t表，只要结构信息

SYS@LEO> conn leonarding/leonarding

LEONARDING@LEO> create table t as select * from all_objects where 1=0;

Table created.

3.    启动执行计划，查看统计报告

LEONARDING@LEO> set autotrace on;

LEONARDING@LEO> select * from t;

no rows selected

Execution Plan 执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------

|  0 | SELECT STATEMENT |     |    1 |  128 |    2  (0)| 00:00:01 |

|  1 | TABLE ACCESS FULL| T   |    1 |  128 |    2  (0)| 00:00:01 | 全表扫描

--------------------------------------------------------------------------

Note

-----

 - dynamic sampling used for this statement

Statistics     统计报告

----------------------------------------------------------

      264 recursive calls

        0 db block gets

       30 consistent gets   一致性读，I/O数量

        0 physical reads

        0 redo size

      995 bytes sent via SQL*Net to client

      370 bytes received via SQL*Net from client

        1 SQL*Net roundtrips to/from client

        0 sorts (memory)

        0 sorts (disk)

        0 rows processed

LEONARDING@LEO>

4.    关闭执行计划

LEONARDING@LEO> set autotrace off;

5.    向表t插入记录但不提交

LEONARDING@LEO> insert into t select * from all_objects;   已经插入9681row

9681 rows created.

LEONARDING@LEO> select count(*) from t;                此时表中已经有9681行数据了

COUNT(*)

----------------------------

    9681

6.    Rollback回滚操作

LEONARDING@LEO> rollback;

Rollback complete.

LEONARDING@LEO> select count(*) from t;               此时表中为0行

COUNT(*)

------------------

       0

7.    第二次查询表t统计报告

LEONARDING@LEO> set autotrace traceonly statistics;

LEONARDING@LEO> select * from t;

no rows selected

Statistics

----------------------------------------------------------

        0 recursive calls

        0 db block gets

      141 consistent gets      一致性读，I/O数量明显上升

        0 physical reads

        0 redo size

      995 bytes sent via SQL*Net to client

      370 bytes received via SQL*Net from client

        1 SQL*Net roundtrips to/from client

        0 sorts (memory)

        0 sorts (disk)

        0 rows processed

说明：

我们可以看出第二次一致性读I/O数量明显高过了第一次I/O数量，我说明一下流程大家就都明白了，当我们insert插入大量数据的时候oracle已经预先分配块空间，此时HWM已经上浮（注：我们检索HWM以下的块）。虽然我们又做了rollback操作，但rollback只是回滚undo快照并没有重设HWM功能，那么块空间还是存在的只不过为空。select扫描HWM以下的块，当然也包括了这些空块，增加了一致性读I/O次数，所以一致性读I/O从30上升到141，多出来的111就是扫描这些空块。下面我们来看看truncate操作对HWM影响

8.    truncate操作对HWM影响

LEONARDING@LEO> insert into t select * from all_objects;

9681 rows created.

Statistics

----------------------------------------------------------

        8 recursive calls

      923 db block gets

    16277 consistent gets         插入时就已经分配了块空间，HWM上浮

        0 physical reads

   976484 redo size

      674 bytes sent via SQL*Net to client

      571 bytes received via SQL*Net from client

        4 SQL*Net roundtrips to/from client

        1 sorts (memory)

        0 sorts (disk)

     9681 rows processed

LEONARDING@LEO> commit;      

Commit complete.

LEONARDING@LEO> truncate table t;

Table truncated.

LEONARDING@LEO> select * from t;

no rows selected

Statistics

----------------------------------------------------------

        1 recursive calls

        1 db block gets

        6 consistent gets       明显降低了一致性读I/O数量，说明truncate操作能够有效降低HWM并且不会生成undo信息

        0 physical reads

       96 redo size

      995 bytes sent via SQL*Net to client

      370 bytes received via SQL*Net from client

        1 SQL*Net roundtrips to/from client

        0 sorts (memory)

        0 sorts (disk)

        0 rows processed

小结：当我们在删除一个表中所有记录时，如果想有效降低I/O开销，那么可以直接使用truncate方式，但注意此方式不能生成undo信息恢复成本较高谨慎操作。


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html