延迟块清除测试

当dml操作涉及到的数据块被写入到磁盘后，再做commit操作，此时oracle不会去修改数据块中的itl相关信息，等到下一次对数据块做select操作时，oracle会清除这些信息。

测试如下：
第一次update表t的数据，并提交，之后再做flush buffer_cache，
SQL> truncate table t;

Table truncated.

SQL> insert into t select rownum,lpad(rownum,4000,'x') from dual connect by level<=500;

500 rows created.

SQL> commit;

Commit complete.

SQL> update t set id=id;

500 rows updated.

SQL> commit;                         

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> set autot on
SQL> select * from t where id=501;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  4004 |   138   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |  4004 |   138   (0)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=501)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        536  consistent gets
        533  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


可见，这一次没有产生redo和额外的一致性读，再看先做flush buffer_cache，再做commit操作的情况：

SQL> truncate table t;

Table truncated.

SQL> set autot off
SQL> insert into t select rownum,lpad(rownum,4000,'x') from dual connect by level<=500;

500 rows created.

SQL> commit;

Commit complete.

SQL> update t set id=id;

500 rows updated.

SQL> alter system flush buffer_cache;

System altered.

SQL> commit;

Commit complete.

SQL> set autot on
SQL> select * from t where id=501;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  4004 |   138   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |  4004 |   138   (0)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=501)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1036  consistent gets
        533  physical reads
      36000  redo size
        325  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

这一次发生了延迟块清除，产生了redo和额外的一致性读，

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-1063615/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-1063615/