oracle延遲塊清除

標籤：產生   一個   datafile   process   trace   lin   mem   影響   exec   

　　oracle在執行一些DML操作時，會在oracle的block上都有活動事務的標誌，如果一個事務commit後，由於某些block在commit之前已經寫回datafile, 或者事務影響到的block數過多，則commit的時候只會清理undo segment header中的事務表資訊，data block上的事務標誌不會清除，否則代價過高。那麼在一些讀取這些block時，需要將這些事務標誌進行清除，就是延遲塊清除

實驗如下：

SQL> drop table t cascade constraints;Table dropped.--建立一張表tSQL> create table t as select * from dba_objects where 1=2;Table created.SQL> select count(*) from t;  COUNT(*)----------         0--採用直接路徑插入的方式，即不經過buffer cacheSQL> 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(*)----------     87023Execution 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 processedSQL> /  COUNT(*)----------     87023Execution 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 processedSQL> truncate table t;Table truncated.SQL> select count(*) from t;  COUNT(*)----------         0Execution 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

　　

oracle延遲塊清除