Oracle中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