环境描述：rac双节点主库加一个单节点的物理备库（作查询库使用），白天开启到只读模式，晚上应用2小时的日志并再开启到只读模式。

问题描述：开发人员反应点击前端应用查询某个单子时半天不出结果，询问开发人员获取该模块对应的sql语句后在主库上运行结果10秒内返回结果，但是在查询库上却要20分钟之久，接下来进行问题诊断。

诊断过程：

1.分别在主备库上运行sql并获取其执行计划，此时发现两个执行计划确实不同，主要表现在同名表却是用不同的索引

2.初步怀疑是表的数据量或统计信息等因素导致执行计划的不同，通过比对两个库的数据字典信息后发现不是这个原因

3.按照主库的执行计划通过添加hint(/*+ index(t idx_t) */)然后再备库上运行此sql，当修改到第二个表时性能突然一下子提高了，可以在10秒内返回结果。原因为好的执行计划使用的是选择性高的索引，而坏的则走的选择性低的索引。到目前为止还没有找到问题的根本原因，为什么cbo会在备库上选择一个选择性低的索引？

4.通过google发现对于主备库上运行相同sql却产生不同执行计划很有可能是初始化参数不同引起的，于是在主备库上通过show parameter optimizer命令发现主备库上的optimizer_index_caching设置的值不同，主库为0（默认值），备库为100，于是在备库上修改此参数后再次运行原始sql(没添加hint)发现性能问题解决，此sql可以在10秒内返回结果

5.查询官方文档关于optimizer_index_caching参数的解释发现此参数设置的值越大，cbo越倾向于选择in-list的索引和nl，再次比对执行计划发现确实都选择的是nl操作且备库上还多了一个INLIST ITERATOR的操作，至此根本原因也知道了就是因为optimizer_index_caching参数值设置过大导致导致倾向于选择in-list的索引而最终导致执行计划选择错误

6.至此也看出了基本概念的理解对于调优也是很有帮助的，如果对optimizer_index_caching参数了解够多可能会更早定位这个问题。

ITPub博客

记录一个sql优化的全过程.txt

最新文章