首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >迷惑性SQL性能问题排查与优化

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

作者头像
jeanron100
发布2019-10-14 16:31:10
5150
发布2019-10-14 16:31:10
举报

背景:

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

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

SELECT * FROM (SELECT XX.*, ROWNUM AS RN FROM (select count(*) from PARTY_CERT P inner join CUSTOMER C on P.PARTY_ID = C.PARTY_ID and C.STATUS_CD = '1100' where P.PARTY_ID in (:1 ) and P.STATUS_CD in (:2 ) and P.IS_DEFAULT = '1') XX WHERE ROWNUM <= 1000 ) XXX WHERE RN > 0

分析:

查询出绑定变量的值带入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。导入全部数据,建立相关索引,收集直方图 ) 执行SQL,SQL效率更差,15万逻辑读/次

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

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

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

SELECT * FROM (SELECT XX.*, ROWNUM AS RN  FROM (select count(1)
from CUST_YC_APP.PARTY_CERT P where P.PARTY_ID in (15151723602037)
And P.PARTY_ID in( select C.PARTY_ID from  CUSTOMER_test  C
where C.STATUS_CD = '1100' )  and P.STATUS_CD in ('1000')  
and P.IS_DEFAULT = '1') XX WHERE ROWNUM <= 1000) XXX WHERE RN > 0;

改成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倾向选择走半连接即可。

DBMS_STATS.set_column_stats(colname =>'PARTY_ID',distcnt => 1645919);
1645919 大约数据总量的30%,

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

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

总结:

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

  • 笛卡尔积关联,并不是性能瓶颈。
  • 数据特殊分布,数据集中在某个值,这个值带来严重的索引再回表。
  • 结合数据分布把SQL改成半连接形式,成本明显减少。
  • 由于特殊分布,收集直方图当测试特殊分布的值时候会带来大表全表扫描,不收集直方图会带来hash join 不是我们想要得到的 nested_loop seml。
  • 设置统计信息既能固定走索引扫描,(无论此表中数据情况都是索引扫描效率最高),又能满足最好的关联方式nested_loop seml。
  • 最终的实施优化方案采用最简单直接的方案,而不是我们文中探究的改SQL,设置统计信息等。而且最终效果还不错。
本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-10-11,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档