EODA@PROD1> set echo on
EODA@PROD1> create table t ( x int );
Table created.
EODA@PROD1> insert into t values ( 1 );
1 row created.
EODA@PROD1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
EODA@PROD1> select * from t;
X
----------
1
EODA@PROD1> alter session set isolation_level=serializable;
Session altered.
EODA@PROD1> set autotrace on statistics
EODA@PROD1> select * from t;
X
----------
1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets --观察I/O次数
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
再另一个会话中执行10000次修改
begin
for i in 1 .. 10000
loop
update t set x = x+1;
commit;
end loop;
end;
/
再返回进行同样的查询
EODA@PROD1> select * from t;
X
----------
1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10017 consistent gets --多达10017次I/O
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
因为Oracle回滚了对该数据库块的修改,当再次查询的时候Oracle知道查询获取和处理这些块必须针对事务开始的那个时刻。当在缓存区找到这个数据库时,发现这个块已经被修改了10000次,所以开始查找undo信息进行了10000次回滚。
再次进行同样的查询
EODA@PROD1> select * from t;
X
----------
1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets --仅仅6次I/O
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
因为Oracle已经把同一个块的多个版本保存在了缓冲区,所以可以直接使用。
--参考来源《Oracle编程艺术深入理解数据库体系结构(第三版)》