当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和额外的一致性读,
测试如下:
第一次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/