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

作者: 戴秋龙  转载自: Oracle一体机用户组 

640?wx_fmt=jpeg

作者简介

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

 

背景:

客户某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报告不符合

 

640?wx_fmt=png


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

640?wx_fmt=png

通过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%,结合绑定变量继续排查。

640?wx_fmt=png

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

 

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

 

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

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

 

做好测试环境。

 

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

640?wx_fmt=png

640?wx_fmt=png

 

回到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逻辑读/次

640?wx_fmt=png

总结:

 

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

 

  • 笛卡尔积关联,并不是性能瓶颈。

  • 数据特殊分布,数据集中在某个值,这个值带来严重的索引再回表。

  • 结合数据分布把SQL改成半连接形式,成本明显减少。

  • 由于特殊分布,收集直方图当测试特殊分布的值时候会带来大表全表扫描,不收集直方图会带来hash join 不是我们想要得到的 nested_loop seml。

  • 设置统计信息既能固定走索引扫描,(无论此表中数据情况都是索引扫描效率最高),又能满足最好的关联方式nested_loop seml。

  • 最终的实施优化方案采用最简单直接的方案,而不是我们文中探究的改SQL,设置统计信息等。而且最终效果还不错。

一体机用户组的二维码如下,感兴趣可以关注:

640?wx_fmt=jpeg

640?wx_fmt=png

展开阅读全文
  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

CruiseYoung提供的带有详细书签的电子书籍目录 http://blog.csdn.net/fksec/article/details/7888251 SQL Server求生秘籍(SQL Server故障排除圣经) 基本信息 原书名: SQL Server 2005 Practical Troubleshooting: The Database Engine 原出版社: Addison-Wesley 作者: (美)Ken Henderson    微软SQL Server开发小组和支持部门 译者: 若启 一辉 瞿杰 丛书名: 图灵程序设计丛书.数据库系列 出版社:人民邮电出版社 ISBN:9787115191113 上架时间:2009-2-5 出版日期:2009 年2月 开本:16开 页码:342 版次:1-1 编辑推荐    微软SQL Server内部技术资料大曝光.    来自SQL Server开发小组和支持部门的梦之队打造..    SQL Server故障排除圣经... 内容简介   本书帮助你解决众多数据库引擎方面的问题,每一章从关键的sql server 组件入手,然后探讨用户遇见的常见问题,并给出解决方案。本书的主要内容包括等待和阻塞、数据毁坏和恢复、内存、过程缓存、查询进程等。本书的作者都是来自微软公司sql server 开发团队和客户支持服务部门的支持专家。在你的sql server 系统遇到问题时,本书将变得不可或缺。   本书适合数据库管理员和数据库开发人员阅读。   作者简介:   ken henderson(1967-2008)sql sewer世界级权威。生前供职于微软sql sewer开发组。以guru's guide系列经典著作和sqldiag等工具享誉业界。   创作团队 来自sql server开发小组的7位开发人员和来自微软客户支持服务机构的3位支持专家,他们都有丰富的开发经验,熟悉sql sewer源代码。 作译者   本书的创作团队由来自SQL Server开发小组的开发人员以及来自微软的客户支持服务机构的支持专家组成。其中7位来自SQL Server开发小组,3位来自微软的客户支持服务部门。.   SQL Server开发小组   August Hill是一位具有30多年开发经验的开发人员。在过去的6年里,他一直是SQL Server Service Broker小组中的一员。他为该产品在可支持性方面做出了许多贡献。在业余时间里他喜欢弹吉他以及品味华盛顿葡萄酒。他的联系方式是august.hill@microsoft.com。   Cesar Galindo-Legaria是SQL Server查询优化器部门的主管.他于1992年获得哈佛大学计算机科学专业(数据库方向)的博士学位.在波士顿的一家图形公司工作过一段时间以后,他又返回到数据库领域,在欧洲研究中心进行博士后访问.他于1995年加入微软从事新型关系型查询处理器的工作,该处理器随SQL Serverc7.0一起首次发布,其中引入了一个完全基于开销的查询优化器、一套丰富的执行算法以及许多自动管理的特性.从那时起,他就一直从事SQL Server查询处理的工作.他在查询处理和优化领域拥有多项专利,并且在该领域内发表了许多研究论文.   Ken Henderson是一位具有25年以上开发经验的开发人员.他从1990年开始就从事SQL Server的工作并在其职业生涯内为多家公司开发软件,包括H&R Block、美国中央情报局、美国海军、美国空军、Borland国际公司、摩根大通(JP Morgan)等公司.他于2001年加入微软,目前是SQL Server开发小组中易管理性平台部门(Manageability Platform Group)的一位开发人员.他是SQL Server 2005中SQLDiag工具的开发者,并专注于SQL Server管理工具及相关技术的工作.他是8本涉及多种计算机主题的作者,包括出版自Addison-Wesley公司的SQL Server畅销图书Guru’s Guide系列.他和全家住在达拉斯,他的电子邮箱是khen@khen.com.   Sameer Tejani出生于坦桑尼亚阿鲁沙,在过去的10年里工作于微软的SQL Server部门.他的工作经历使他能够接触到SQL Server引擎的不同领域,包括T-SQL执行框架、开放数据服务(ODS)、连接管理、用户模式调度器(UMS)以及其他领域.他就是那个令支持专家们痛恨的臭名昭著的“non-yielding scheduler”错误消息的唯一负责人!他目前是SQL Server安全小组的软件开发主管.在业余时间内,Same
相关推荐
CruiseYoung提供的带有详细书签的电子书籍目录 http://blog.csdn.net/fksec/article/details/7888251 该资料是《SQL Server求生秘籍》的随书光盘源代码 对应的书籍资料见: SQL Server求生秘籍(SQL Server故障排除圣经) 基本信息 原书名: SQL Server 2005 Practical Troubleshooting: The Database Engine 原出版社: Addison-Wesley 作者: (美)Ken Henderson    微软SQL Server开发小组和支持部门 译者: 若启 一辉 瞿杰 丛书名: 图灵程序设计丛书.数据库系列 出版社:人民邮电出版社 ISBN:9787115191113 上架时间:2009-2-5 出版日期:2009 年2月 开本:16开 页码:342 版次:1-1 编辑推荐    微软SQL Server内部技术资料大曝光.    来自SQL Server开发小组和支持部门的梦之队打造..    SQL Server故障排除圣经... 内容简介   本书帮助你解决众多数据库引擎方面的问题,每一章从关键的sql server 组件入手,然后探讨用户遇见的常见问题,并给出解决方案。本书的主要内容包括等待和阻塞、数据毁坏和恢复、内存、过程缓存、查询进程等。本书的作者都是来自微软公司sql server 开发团队和客户支持服务部门的支持专家。在你的sql server 系统遇到问题时,本书将变得不可或缺。   本书适合数据库管理员和数据库开发人员阅读。   作者简介:   ken henderson(1967-2008)sql sewer世界级权威。生前供职于微软sql sewer开发组。以guru's guide系列经典著作和sqldiag等工具享誉业界。   创作团队 来自sql server开发小组的7位开发人员和来自微软客户支持服务机构的3位支持专家,他们都有丰富的开发经验,熟悉sql sewer源代码。 作译者   本书的创作团队由来自SQL Server开发小组的开发人员以及来自微软的客户支持服务机构的支持专家组成。其中7位来自SQL Server开发小组,3位来自微软的客户支持服务部门。.   SQL Server开发小组   August Hill是一位具有30多年开发经验的开发人员。在过去的6年里,他一直是SQL Server Service Broker小组中的一员。他为该产品在可支持性方面做出了许多贡献。在业余时间里他喜欢弹吉他以及品味华盛顿葡萄酒。他的联系方式是august.hill@microsoft.com。   Cesar Galindo-Legaria是SQL Server查询优化器部门的主管.他于1992年获得哈佛大学计算机科学专业(数据库方向)的博士学位.在波士顿的一家图形公司工作过一段时间以后,他又返回到数据库领域,在欧洲研究中心进行博士后访问.他于1995年加入微软从事新型关系型查询处理器的工作,该处理器随SQL Serverc7.0一起首次发布,其中引入了一个完全基于开销的查询优化器、一套丰富的执行算法以及许多自动管理的特性.从那时起,他就一直从事SQL Server查询处理的工作.他在查询处理和优化领域拥有多项专利,并且在该领域内发表了许多研究论文.   Ken Henderson是一位具有25年以上开发经验的开发人员.他从1990年开始就从事SQL Server的工作并在其职业生涯内为多家公司开发软件,包括H&R Block、美国中央情报局、美国海军、美国空军、Borland国际公司、摩根大通(JP Morgan)等公司.他于2001年加入微软,目前是SQL Server开发小组中易管理性平台部门(Manageability Platform Group)的一位开发人员.他是SQL Server 2005中SQLDiag工具的开发者,并专注于SQL Server管理工具及相关技术的工作.他是8本涉及多种计算机主题的作者,包括出版自Addison-Wesley公司的SQL Server畅销图书Guru’s Guide系列.他和全家住在达拉斯,他的电子邮箱是khen@khen.com.   Sameer Tejani出生于坦桑尼亚阿鲁沙,在过去的10年里工作于微软的SQL Server部门.他的工作经历使他能够接触到SQL Server引擎的不同领域,包括T-SQL执行框架、开放数据服务(ODS)、连接管理、用户模式调度器(UMS)以及其他领域.他就是那个令支持专家们痛恨的臭名昭著的“non-yielding scheduler”错误消息的唯一负
<p style="font-size:16px;color:#666666;"> <img src="https://img-bss.csdn.net/202001311426171105.png" alt="" /> </p> <p style="font-size:16px;color:#666666;"> <strong><span style="font-size:20px;">课程目标</span></strong> </p> <p style="font-size:16px;color:#666666;"> 《从零开始学Scrapy网络爬虫》从零开始,循序渐进地介绍了目前流行的网络爬虫框架Scrapy。即使你没有任何编程基础,学习起来也不会有压力,因为我们有针对性地介绍了Python编程技术。另外,《从零开始学Scrapy网络爬虫》在讲解过程中以案例为导向,通过对案例的不断迭代、优化,让读者加深对知识的理解,并通过14个项目案例,提高学习者解决实际问题的能力。 </p> <p style="font-size:16px;color:#666666;"> <br /> </p> <p style="font-size:16px;color:#666666;"> <strong><span style="font-size:20px;">适合对象</span></strong> </p> <p style="font-size:16px;color:#666666;"> 爬虫初学者、爬虫爱好者、高校相关专业的学生、数据爬虫工程师。 </p> <p style="font-size:16px;color:#666666;"> <br /> </p> <p style="font-size:16px;color:#666666;"> <span style="font-size:20px;"><strong>课程介绍</strong></span> </p> <p style="font-size:16px;color:#666666;"> 《从零开始学Scrapy网络爬虫》共13章。其中,第1~4章为基础篇,介绍了Python基础、网络爬虫基础、Scrapy框架及基本的爬虫功能。第5~10章为进阶篇,介绍了如何将爬虫数据存储于MySQL、MongoDB和Redis数据库中;如何实现异步AJAX数据的爬取;如何使用Selenium和Splash实现动态网站的爬取;如何实现模拟登录功能;如何突破反爬虫技术,以及如何实现文件和图片的下载。第11~13章为高级篇,介绍了使用Scrapy-Redis实现分布式爬虫;使用Scrapyd和Docker部署分布式爬虫;使用Gerapy管理分布式爬虫,并实现了一个抢票软件的综合项目。 </p> <p style="font-size:16px;color:#666666;"> <span style="color:#FF0000;">      由于目标网站可能会对页面进行改版或者升级反爬虫措施,如果发现视频中的方法无法成功爬取数据,敬请按照页面实际情况修改XPath的路径表达式。视频教程主要提供理论、方法支撑。我们也会在第一时间更新源代码,谢谢!</span> </p> <p style="font-size:16px;color:#666666;"> <img src="https://img-bss.csdn.net/202001311426306665.png" alt="" /> </p> <p style="font-size:16px;color:#666666;"> <strong><span style="font-size:20px;">课程特色</span></strong> </p> <p style="font-size:16px;"> <img src="https://img-bss.csdn.net/202001311426415123.png" alt="" /> </p> <div> <br /> </div>
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值