生产环境sql语句调优实战第二篇(r2第38天)

在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时,得到的sql monitor报告如下: Global Information: EXECUTING

--》对应的sql语句如下: select document.period_key, document.cycle_seq_no, document.ba_no, document.customer_no, bill_statement.pay_channel_no from document, --千万数据量 12671016 rows cycle_control, --数据字典表,2118 rows bill_statement, --千万数据量 12671016 rows cyc_payer_pop --百万数据量 5400326 rows where cycle_control.cycle_code = 2 and cycle_control.cycle_instance = 7 and cycle_control.cycle_year = 2014 and cyc_payer_pop.cycle_seq_no = cycle_control.cycle_seq_no and cyc_payer_pop.db_status = 'BL' and document.ba_no = cyc_payer_pop.ba_no and document.cycle_seq_no = cyc_payer_pop.cycle_seq_no and document.cycle_seq_run = cyc_payer_pop.cycle_seq_run and document.period_key = cyc_payer_pop.period_key and document.customer_key = cyc_payer_pop.customer_key and document.doc_produce_ind in ('Y ', ' E ') and document.document_status != ' N' and bill_statement.ba_no = cyc_payer_pop.ba_no and bill_statement.cycle_seq_no = document.cycle_seq_no and bill_statement.cycle_seq_run = document.cycle_seq_run and bill_statement.period_key = cyc_payer_pop.period_key and bill_statement.customer_key = cyc_payer_pop.customer_key and bill_statement.document_seq_no = document.doc_seq_no 可以通过执行计划看到,性能的瓶颈主要在两个地方,一个是做了全表扫描的部分 表CYC_PAYER_POP,另外一个就是CPU资源的过度消耗,表DOCUMENT SQL Plan Monitoring Details (Plan Hash Value=1606258714)

可以看到CYC_PAYER_POP 做了全表扫描,估算出的数据条数是56条。 而绝大多数的IO都是在DOCUMENT,IO请求达1700万次,大约是128G的数据量,而且还不停的走索引做数据查取。 奇怪的是估算的数据返回量和实际执行的数据返回差距太大,返回结果大约有27万条。

但是第8步的数据得到。 一次56条数据返回* 执行171次=9.5k 但是实际的返回结果得到了270K,差距实在太大。这是执行计划不正确导致的。 明确了上面一步,下面DOCUMENT表做了1700万次的io查询就可以理解了,这些都是连带的问题。 从千万的数据中得到27万的数据,还是很小的数据范围。 首先排查 得到从数据字典表cycle_control中根据如下的条件,得到的数据只有一行。这和执行计划是一致的。 cycle_control.cycle_code = 2 and cycle_control.cycle_instance = 7 and cycle_control.cycle_year = 2014 因为document和bill_statement都是千万数据量的大表。所以在和它们两个表做数据关联的时候应该尽可能的过滤掉大部分数据。因为数据最终的返回只有27万,相对千万的数据还是很小的一部分。从百万的数据中走全表扫描还是不小的消耗,看看能不能从索引的角度入手。 索引信息如下: INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL ------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- CYC_PAYER_POP_1IX NORMAL NONUNIQUE YES CUSTOMER_NO TABLE N/A 5320775 16-JUL-14 N CYC_PAYER_POP_2IX NORMAL NONUNIQUE YES CONFIRM_GROUP,CYCLE_SEQ_NO TABLE N/A 5642000 16-JUL-14 N CYC_PAYER_POP_3IX NORMAL NONUNIQUE YES FORMAT_EXT_GROUP,CYCLE_SEQ_NO, TABLE N/A 5623545 16-JUL-14 N DB_STATUS CYC_PAYER_POP_4IX NORMAL NONUNIQUE YES GROUP_ID,CYCLE_SEQ_NO TABLE N/A 5142606 16-JUL-14 N CYC_PAYER_POP_5IX NORMAL NONUNIQUE YES QA_GROUP,CYCLE_SEQ_NO TABLE N/A 5776258 16-JUL-14 N CYC_PAYER_POP_PK NORMAL UNIQUE YES BA_NO,CYCLE_SEQ_NO,PERIOD_KEY, TABLE N/A 5368484 16-JUL-14 N

碰巧的是在数据的连接条件和输出列中,都是和主键相关的一些列。这样就可以考虑通过hint来启用索引了。当然启用索引也有一定的标准,在这个查询中。 通过索引和过滤条件查到的数据有不到30万,数据量是500多万,占到的数据比例不到10%,是可以考虑启用索引的。如果数据结果集较大,启用索引反而不利于数据的查询速度。 明确了这一点,我尝试把CYC_PAYER_POP的查询和数据字典表结合起来,过滤掉绝大部分数据。形成一个子查询。 在子查询中,启用了hint来强制查询按照计划的顺序和索引来执行。 (select /*+ leading (c p) index(p CYC_PAYER_POP_PK)*/ p.ba_no,p.cycle_seq_no,p.cycle_seq_run,p.period_key,p.customer_key from cyc_payer_pop p, cycle_control c where c.cycle_code = 2 and c.cycle_instance = 7 and c.cycle_year = 2014 and p.cycle_seq_no = c.cycle_seq_no and p.db_status = 'BL' ) cyc_payer_pop 然后在这个基础上,再和两个大表做关联, 优化后的sql语句如下: select /*+ leading( cyc_payer_pop bill_statement document)*/ document.period_key, document.cycle_seq_no, document.ba_no, document.customer_no, bill_statement.pay_channel_no from document, --千万数据量 12671016 rows bill_statement ,--千万数据量 12671016 rows (select /*+ leading (c p) index(p CYC_PAYER_POP_PK)*/ p.ba_no,p.cycle_seq_no,p.cycle_seq_run,p.period_key,p.customer_key from cyc_payer_pop p, cycle_control c where c.cycle_code = 2 and c.cycle_instance = 7 and c.cycle_year = 2014 and p.cycle_seq_no = c.cycle_seq_no and p.db_status = 'BL' ) cyc_payer_pop where and document.ba_no = cyc_payer_pop.ba_no and document.cycle_seq_no = cyc_payer_pop.cycle_seq_no and document.cycle_seq_run = cyc_payer_pop.cycle_seq_run and document.period_key = cyc_payer_pop.period_key and document.customer_key = cyc_payer_pop.customer_key and document.doc_produce_ind in ('Y ', ' E ') and document.document_status != ' N' and bill_statement.ba_no = cyc_payer_pop.ba_no and bill_statement.cycle_seq_no = document.cycle_seq_no and bill_statement.cycle_seq_run = document.cycle_seq_run and bill_statement.period_key = cyc_payer_pop.period_key and bill_statement.customer_key = cyc_payer_pop.customer_key and bill_statement.document_seq_no = document.doc_seq_no 优化后的执行计划如下。document表的io请求数从1700万次,降低到了将近8万次。解决了性能瓶颈。 SQL Plan Monitoring Details (Plan Hash Value=1573871804)

当然了关键的还是查取速度。 查询速度也从原本的5个小时降低到了4-5分钟。 在测试和生产环境中实际执行的速度和预期是一致的。 290896 rows selected. Elapsed: 00:04:08.04 291001 rows selected. Elapsed: 00:05:08.66

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-07-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

海量数据切分抽取的实践场景(r11笔记第43天)

如果一个大表要抽取数据导出成csv文件,我们有什么策略,如何改进。 一、问题背景 今天开发的同学找到我,他们需要做一个数据统计分析,需要我提供一些支持,把一...

3446
来自专栏杨建荣的学习笔记

一次数据库响应慢的问题诊断(r6笔记第39天)

今天接到开发一个同事的电话,说前端系统那边反馈有一个查询很慢,初步怀疑是有一些并发或者锁之类的问题导致的。 接到问题之后,自己还是带着一些的紧迫感来处理的。 首...

2765
来自专栏杨建荣的学习笔记

每秒执行6000的简单SQL优化(二) (r10笔记第65天)

继续前几天的一次性能调优,这次调优难度不小,而且空间很小,看起来简直就是绝处逢生的感觉。下面的两条SQL语句执行频率极高,每秒达到6000次,希望能够优化。 s...

2898
来自专栏Jerry的SAP技术分享

SAP CRM Survey调查问卷的存储模型

数据库表CRM_SVY_DB_SVS,通过如下的函数CRM_SVY_DB_SVS_CREATE插入:

1603
来自专栏java达人

MySQL的limit查询优化

我们大家都知道MySQL数据库的优化是相当重要的。其他最为常用也是最为需要优化的就是limit。MySQL的limit给分页带来了极大的方便,但数据量一大的时候...

2038
来自专栏杨建荣的学习笔记

复杂SQL性能优化的剖析(一)(r11笔记第36天)

今天本来是处理一个简单的故障,但是发现是一环套一环,花了我快一天的时间。 开始是早上收到一条报警: 报警内容: CPUutilization is too hi...

36112
来自专栏进击的程序猿

The Clean Architecture in PHP 读书笔记(十)

这是clean architecture的第十篇,也是具体案例的第二篇,本篇会通过使用laravel框架,来开发我们的应用。

1043
来自专栏数据和云

【安全警告】Oracle 12c 多租户的SQL注入高危风险防范

在使用Oracle多租户选件时,由于Container容器和PDB融合共存,则权限控制必将更加重要,在之前的文章中我们提到,Oracle 12.2 的 loc...

3586
来自专栏数据和云

SQL审核:OR展开与子查询优化案例详解

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

3664
来自专栏好好学java的技术栈

微信支付和支付宝支付到springmvc+spring+mybatis环境全过程(支付宝和微信支付)

2162

扫码关注云+社区