这个SQl优化一波三折

作者简介:戴秋龙,拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化,优化经验,具备丰富的行业服务背景。对Oracle数据库有深刻的理解,擅长数据库故障诊断,数据库性能调优。

背景

某税务系统业务高峰期逻辑读过高,需要优化。

分析

第一波:不适合收集直方图的列收集直方图

业务高峰期AWR报告显示。

sql_id:g5uvywrtfbmc0 ,执行成本:1300次/小时,77万逻辑读/次

sql_text:

执行计划:

DZDZ_DHX_FPXX是大表32G, 以GFSBH作为HASH分区字段,10个分区,每个分区2G到 4G。GFSBH这个字段数据很不均衡。 最多的值60万,最少才10多个。其基数很高。测试GFSBH字段数据量最多的值发现走索引的效率远远高于全表扫描。

全表扫描(默认的执行计划) 52万逻辑读/次

索引扫描(SQL中添加hint)4万逻辑读/次

查明原因是由于该字段上面有直方图导致。很明显这样的字段是不适合收集直方图的。删除直方图后,不管GFSBH等于什么样的值都走索引扫描。

第二波: 基数反馈影响执行计划

删除该直方图后期待执行计划能固定走GFSBH的索引扫描。但是事与愿违。拿到SQL后无论写多少绑定变量的值,都是索引扫描。但是执行效率却比较差。这时想到了 explain plan for 的执行计划可能与真实的执行计划不同。
查看真实执行计划后发现

是由于基数反馈导致CBO不用预估好的执行计划,又走了全表扫描。找到原因就好办了。通过和现场工程师,客户,业务交流后实施,禁止基数反馈。防止执行计划改变。

参考 MOS Statistics (Cardinality) Feedback – Frequently Asked Questions (文档 ID 1344937.1)

本来以为就能固定走索引扫描了,但还是出现第三波。

第三波: 动态采样级别影响执行计划

第三波是隔天后出现,再现这个表的全表扫描。 是新SQL导致,新SQl和上文的SQl一样。也是DZDZ_DHX_FPXX表, GSSBH=XXX 条件。执行计划走GSSBH索引最好。但为什么又出现了全表扫描呢?

分析: SQL比较长,输出字段省略部分。

因为这个SQl是真实的执行计划和预估的执行计划不一致。在做这种问题排查时注意这个陷阱。登陆Sqlplus 声明绑定变量,给绑定变量赋值(由于篇幅问题这里省略这部分操作)

Auto trace 出来的执行计划:(索引名称不同,但都是字段GFSBH的索引)

如果在这个执行计划上面优化,方向就错了。这边就是注意的陷阱。 找到真实执行计划,(找真实执行计划方法有很多,这里不详细说明)却是全表扫描。

找对了方向优化就好办了, 逻辑读70万,是因为出现了全表扫描。 这边也有个陷INTERNAL_FUNCTION(“GFSBH”) 可能认为不能使用索引。

通过排查测试,实验。 最终确认到 动态采样级别为6的原因,这个细节在反复尝试后才知道。

多次开新的session 测试确定设置动态采样级别3时, 4逻辑读/S. 默认动态采样的级别是6时,77万逻辑读/次。

总结:

不适合收集直方图的字段收集直方图,基数反馈,动态采样级别太大。这些原因导致本该走索引的执行计划,走了全表扫描。 导致逻辑读比较高。

未经允许不得转载:Oracle一体机用户组 » 这个SQl优化一波三折

相关推荐