分析查询表时统计信息过多的逻辑读

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编程艺术深入理解数据库体系结构（第三版）》