案例:index range scan真的不会多块读吗?

此次案例来自西安某客户的一次sql优化,对于优化本身并不复杂,但是发现了一个比较有趣的问题,就是索引范围扫描以及回表都有使用多块读的方式。下面来看看具体案例。

sql文本:

执行计划:

这个执行计划是关联了ash的SQL_PLAN_LINE_ID的结果,能清晰的指出sql性能瓶颈在执行计划的id=20和id=21。sql统计信息可以看到性能主要消耗在io上,对于平均每次12w的逻辑读,6w的物理读确实有点高,查看相关对象统计信息可以非常快速的给出解决方案,在ARC_E_KWH_AMT上创建组合全局索引(PRC_AMT_ID,ORG_NO)即可,local也行但是没有全局好。sql优化不是本篇文章的重点,所以就不详细描述了。

当我们回过头去看此案例”神奇“的地方:

两行db file scattered read格外刺眼,为什么INDEX RANGE SCAN和TABLE ACCESS BY LOCAL INDEX ROWID会有db file scattered read等待事件?这是physical reads prefetch warmup的特性,意思是当实例重启或者db cache明显增大的情况下,oracle在读取一些块的时候,”顺便”把附近的块也读到db cache中,起到预热的作用,尽可能的使用db cache,减少后续的物理io,该特性并不会影响一个运行稳定的系统。

通过v$sysstat可以查看实例启动以来physical reads prefetch的相关统计:

该特性由参数_db_cache_pre_warm控制,不知道是什么版本的特性。最多只能占据db cache的10%,由_db_block_prefetch_quota控制。

每次预读取的block上限由参数_db_file_noncontig_mblock_read_count控制:

禁用该功能有三种方式,不过该功能是一个非常好的功能,不建议关闭。

  • _db_file_noncontig_mblock_read_count修改为0或1
  • _db_cache_pre_warm改为false
  • _db_block_prefetch_quota改为0

对于索引还有更细致的参数控制,默认为开启,_index_prefetch_factor为索引预取因子,默认为100,如果变小则更倾向于index prefetching。

 

此条目发表在Oracle, Oralce performance分类目录,贴了标签。将固定链接加入收藏夹。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注