Impact of rollback&truncate operations on high watermark in Oracle: Performance optimization

Test

1. Create a user leonarding and grant DBA authority

Sys@leo> create user leonarding identified by leonarding default tablespace users;

Sys@leo> Grant DBA to leonarding;

2. Create a T-table, as long as the structure of information

Sys@leo> Conn Leonarding/leonarding

Leonarding@leo> CREATE TABLE T as select * from All_objects where 1=0;

Table created.

3. Start execution plan, view statistic report

Leonarding@leo> set autotrace on;

Leonarding@leo> select * from T;

No rows selected

Execution Plan Implementation Plans

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

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 | Full table Scan

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

Note

-----

-Dynamic sampling used for this statement

Statistics Statistical Report

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

264 recursive calls

0 db Block gets

Consistent gets consistent read, I/O quantity

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. Closing the execution plan

Leonarding@leo> set Autotrace off;

5. Insert records to table T but do not submit

leonarding@leo> INSERT INTO T-select * from All_objects; 9681row already plugged in

9681 rows created.

Leonarding@leo> Select COUNT (*) from T; There are already 9681 rows of data in this table.

COUNT (*)

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

9681

6. Rollback ROLLBACK operation

leonarding@leo> rollback;

Rollback complete.

Leonarding@leo> Select COUNT (*) from T; There are 0 rows in this table

COUNT (*)

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

0

7. Second Enquiry form T statistic report

Leonarding@leo> set Autotrace traceonly statistics;

Leonarding@leo> select * from T;

No rows selected

Statistics

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

0 Recursive calls

0 db Block gets

Consistent gets consistent read, I/O number significantly increased

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