oracle延迟块清除

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

实验如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140

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