Update 影响 Select 效率示例

1、背景

  DML操作不会影响SELECT操作，从接触数据库一直到今天我一直坚信着，今天以后再就不坚信了。

2、测试语句

create table t (id number,name varchar(20));
-- 插入100万条记录
begin
for i in 1..1000000 loop
insert into t values (i,'aaa');
end loop;
commit;
end;
/
-- 测试update 语句 
 update t set name = 'ccc' where id > 1 and id < 100000;
-- 测试select 语句
 select id from t where name = 'aaa';

3、测试操作

• 未执行update ,select 查询效率：Elapsed: 00:00:06.34，consistent gets：68600
  

• sid:58 执行update 未提交
  

• sid:69 执行select 查看效率：Elapsed: 00:00:16.25，consistent gets：173801
  

• sid:58 对update 操作进行commit，再看sid:69的select 效率：Elapsed: 00:00:05.80，consistent gets 61670
  
  

• 结论：从执行计划来看消耗都在consistent gets，前后对比执行时间差的近3倍左右。所以update在此种场景（特定的场景）会严重影响select效率。

3、如何避免

• 这里简述一下逻辑读的概念：
  Logical read：逻辑读，就是从内存中读取数据块，包含Current read与Consistent read。
  Current read：属于Oracle版本读取方式的一种机制，就是说当进行DML操作时，我们需要获取数据块的最新状态，只对最新状态的块进行操作，操作期间锁定数据行。
  Consistent read：当进行select查询时，发现这个数据块的版本比我们要查找的要新，那么我们只能从UNDO中去查找这个数据的前映像（PRE IMAGE），在回滚段中找到这个数据的前映像后，把前映像和CURRENT的数据块合并，就形成一个CR BLOCK，这样，通过查询CR BLOCK就可以得到一致性的数据了，这就是Consistent read。（上面的测试，性能的影响基本都在这一块）

公式：logical read=db block gets(current read) + consistent gets(consistent read)。

• 如何减少consistent gets？
  减少CR BLOCK的形成就能解决此类问题，前面的例子是update 完成所有的数据最后做的commit，所以后面的会话再做select 时需要形成非常多的CR BLOCK,从而影响到了查询效率。避免就是缩短update 这个会话的事务长度。

• 有兴趣可以实验一下下面2个过程对select的影响。

-- 1. 长事务
begin
for i in 1..100000 loop
update t set name = 'ddd'||i where id = i;
end loop;
commit;
end;
/
-- 2. 短事务
begin
for i in 1..100000 loop
update t set name = 'ddd'||i where id = i;
commit;
end loop;
end;
/

4、总结

  事务的长度与处理数据多少还是很影响查询的效率的。一定不要被自己的浅薄的认识迷惑了自己。