oracle延迟块清除机制,oracle延迟块清除

oracle在执行一些DML操作时，会在oracle的block上都有活动事务的标志，如果一个事务commit后，由于某些block在commit之前已经写回datafile, 或者事务影响到的block数过多，则commit的时候只会清理undo segment header中的事务表信息，data block上的事务标志不会清除，否则代价过高。那么在一些读取这些block时，需要将这些事务标志进行清除，就是延迟块清除

实验如下：

SQL> drop table t cascade constraints;

Table dropped.

--创建一张表t

SQL> create table t as select * from dba_objects where 1=2;

Table created.

SQL> select count(*) from t;

COUNT(*)

----------

0

--采用直接路径插入的方式，即不经过buffer cache

SQL> insert /*+ append */ into t select * from dba_objects;

87023 rows created.

SQL> set autotrace on;

SQL> commit; --提交，此时新插入数据的block上的事务标志并没有清除

Commit complete.

SQL> select count(*) from t;

COUNT(*)

----------

87023

Execution Plan

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

Plan hash value: 2966233522

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

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

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

| 0 | SELECT STATEMENT | | 1 | 339 (1)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 67743 | 339 (1)| 00:00:05 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0 recursive calls

1 db block gets

1249 consistent gets

1241 physical reads

168 redo size --查询即产生了redo，查询导致了data block上进行事务清除

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> /

COUNT(*)

----------

87023

Execution Plan

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

Plan hash value: 2966233522

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

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

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

| 0 | SELECT STATEMENT | | 1 | 339 (1)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 67743 | 339 (1)| 00:00:05 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0 recursive calls

0 db block gets

1246 consistent gets

0 physical reads

0 redo size --再次查询不产生redo，事务已清除完毕

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> truncate table t;

Table truncated.

SQL> select count(*) from t;

COUNT(*)

----------

0

Execution Plan

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

Plan hash value: 2966233522

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

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

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

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

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 1 | 2 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

6 recursive calls

1 db block gets

13 consistent gets

0 physical reads

96 redo size --truncate表之后查询，同样也产生了延迟块清除

525 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

原文：http://www.cnblogs.com/zx3212/p/7644824.html