迷惑性SQL性能问题排查与优化

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

背景:

客户某SQL,逻辑读比较高。需要优化。也给出了AWR报告,AWR报告中主要几个SQL都是类似的问题。

SQL_ID: g4nbv7twn23fw, 成本: 3000逻辑读/ 40万次/h

分析:

查询出绑定变量的值带入SQl,发现只有9个逻辑读。与AWR报告不符合

可能有读者认为性能问题在ID=5笛卡尔积问题,但从事后看问题不在这里。此时陷入僵局。但ASH视图中或许能给出线索。

通过ash分析,更多的性能消耗在执行计划的第9步。也就在C表(CUSTOMER)的回表上。

SQL中得出C表用到两个字段 C.PARTY_ID,C.STATUS_CD PARTY_ID上建有索引,回表就是为了访问STATUS_CD字段。

因此建议建立索引index C ( PARTY_ID, STATUS_CD ); 这样可以避免回表。

针对该SQL的优化建议是建立索引。

实施组建立索引后,从后期多份AWR报告中,该SQl平均330逻辑读/次。

思考能否继续优化

未优化之前带入绑定变量9逻辑读但AWR报告中平均3000逻辑读。结合起来看是否是数据分布不均衡导致呢?

排查中发现C PARTY_ID字段的选择性 98%,结合绑定变量继续排查。

如图:就是一个值在表中有10万,其他值在表中只有1条。

PARTY_ID = 15151723602037回表需要10万次。把该值带入SQL中。逻辑读7770/次。是它把平均逻辑读拉到3000.针对该问题上文已经有相关建议。那能否进一步优化?

探讨:以下探讨在没有建立新索引的基础上。

既然数据分布不均衡,是否可以通过收集直方图来改善性能?答案是否定的。

做好测试环境。

( 建立测试表:CUSTOMER_test导入全部数据,建立相关索引,收集直方图)执行SQLSQL效率更差,15万逻辑读/

回到SQl中。分析SQLSQl只是需要count(1),统计类型的,可以考虑用半连接

需要和业务确认是否可以改成半连接。 ( 此处不讨论业务,只讨论这种数据分布情况下如何优化 )因为针对数据分布不均衡半连接效果比较好。

改写SQL( 带入数据最多的值 )

改成in不添加hints 就会走全表,1286逻辑读/S

添加hint /*+ nl_sj index(c) */ 9逻辑读/

SQL无法自动走最佳的执行计划,
需要绑定hints才走。
如何自动用最佳执行计划呢?

  • 删除直方图。

删除直方图后P.PARTY_ID in (15151723602037)的数据量虽然很多但CBO评估该数据量1条,直接走了 hash join ( 有时候也会结合 C.PARTY_ID = P.PARTY_ID评估出 C.PARTY_ID =15151723602037 也是1条,直接走笛卡尔积关联,类似开头的问题)

而不是最好的执行计划。

收集直方图,会走索引,删除直方图会走hash/笛卡尔积关联.就是得不到半连接

似乎陷入了困境。

  • 设置数据选择性。

帮助CBO评估 P表返回的数据,其对精确度要求也不高,甚至只要多评估几条,让CBO倾向选择走半连接即可。

测试SQlSQL是不是直接选择最好的执行计划。

执行计划果然是nested_loop seml 关联并且走索引。就是目前探讨的最好的执行计划。9逻辑读/

总结:

分析并且优化该SQl,有注意的地方有6

  • 笛卡尔积关联,并不是性能瓶颈。
  • 数据特殊分布,数据集中在某个值,这个值带来严重的索引再回表。
  • 结合数据分布把SQl改成半连接形式,成本明显减少。
  • 由于特殊分布,不收集直方图当测试特殊分布的值时候会带来大表全表扫描,收集直方图会带来hash join 不是我们想要得到的 nested_loop seml。
  • 设置统计信息既能固定走索引扫描,(无论此表中数据情况都是索引扫描效率最高),又能满足最好的关联方式 nested_loop seml。
  • 最终的实施优化方案采用最简单直接的方案,而不是我们文中探究的改SQL,设置统计信息等。 而且最终效果还不错。

未经允许不得转载:Oracle一体机用户组 » 迷惑性SQL性能问题排查与优化

相关推荐