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

来源：互联网发布：excel软件培训编辑：程序博客网时间：2024/06/09 15:07

EODA@PROD1> set echo onEODA@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---------- 1EODA@PROD1> alter session set isolation_level=serializable;Session altered.EODA@PROD1> set autotrace on statisticsEODA@PROD1> select * from t; X---------- 1Statistics----------------------------------------------------------  0  recursive calls  0  db block gets  6  consistent gets  --观察I/O次数  0  physical reads  0  redo size419  bytes sent via SQL*Net to client419  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---------- 1Statistics----------------------------------------------------------  0  recursive calls  0  db block gets      10017  consistent gets  --多达10017次I/O  0  physical reads  0  redo size419  bytes sent via SQL*Net to client419  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---------- 1Statistics----------------------------------------------------------  0  recursive calls  0  db block gets  6  consistent gets   --仅仅6次I/O  0  physical reads  0  redo size419  bytes sent via SQL*Net to client419  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编程艺术深入理解数据库体系结构（第三版）》

0 0