Rollback&Truncate操作對高水位線影響之效能優化篇

引題：受人之託，寫了這篇文章，貌似這個問題也困擾了許多人，希望可以給大家一點啟發

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資訊恢復成本較高謹慎操作。