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

昨天的一篇文章复杂SQL性能优化的剖析(一)(r11笔记第36天) 分析了一个SQL语句导致的性能问题,问题也算暂时告一段落,因为这个语句的执行频率是10分钟左右,所以优化后(大概是2秒左右,需要下周再次确认)的提升很大。

对于优化是一个持续的改进,我们碰到的问题,最终的原因可能五花八门,但是正如柯南所说,真相只有一个。我把这个问题和前几天处理的一个问题结合起来,前几天处理了一个紧急问题,也是有一个SQL语句的执行计划发生改变,这个语句的业务比较关键,触发频率是每分钟一次,如果一旦出现延迟,就是一个连锁反应。目前语句的执行效率其实不大理想,每次平均要25秒左右。对于我来说,这个结果其实是不可接受的。这么说的一个原因是据我所知,在另外一个统计库中,执行同样的语句只需要1秒钟。所以对于这个问题我还是充满信息的。

语句其实也蛮长,但是还得列出来。

select to_char(t2.servertime,'yyyy-mm-dd hh24:mi:ss') as servertime,t2.deviceid,t2.gamechannel, t2.system,t2.device,t2.resolution,t2.dt,t2.appkey from ( select r1.servertime,r1.deviceid,r1.appkey from (select min(servertime) as servertime,deviceid,appkey from sdk_start where dt=:1 group by deviceid,appkey) r1 left join (select deviceid,appkey from h1_active_dev) r2 on(r1.deviceid=r2.deviceid and r1.appkey=r2.appkey) where r2.deviceid is null ) t1 left join (select servertime,deviceid,gamechannel,system,device,resolution,dt,appkey from sdk_start where dt=:2 ) t2 on(t1.appkey=t2.appkey and t1.deviceid=t2.deviceid and t1.servertime=t2.servertime)主要的思路就是在表sdk_start中进行初步的过滤,得到每个设备最早的信息记录,然后和另外一个总表h1_active_dev去匹配(左外连接),得到这些信息之后再来和sdk_start重新关联,得到尽可能详细的信息。两个表的数据量都是千万级。

有了参考的标准,优化也有了一定的方向和章法可依据。

按照目前的执行情况,是对h1_active_dev做了全表扫描,是优化器认为目前最高效的方式。我认真比对了两套环境,数据量相仿,索引信息也是相似的,执行计划却大大不同。

其中的一个不同之处是sdk_start在当前执行效率较差的环境中,尽管是分区区,但是只有一个默认分区pmax,但是我注意到一个情况,同样的环境,另外一个逻辑相似(表名不同)的语句,表也没有分区,执行效率也很高,也是1秒左右。所以目前我只能推断表分区规范后能够提升执行效率,但是具体情况还得测试一下才能论证。

所以目前我更倾向于理解是执行计划的差别,目前的情况如下:

统计库1的执行效率较差,统计库2的执行效率要高。

统计库1中的SQL执行效率如下:

Enter value for 1: 87m3rru3sy2jt
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3721561291      25.965

统计库2中的执行情况如下,存在两个子执行计划,但是效率都不错。

Enter value for 1: 87m3rru3sy2jt
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      690487836        .087
     3721561291        .989

我们就可以在执行计划的深入分析之外大胆做一个尝试,把统计库2 的执行计划抓取出来,替换统计库1的执行计划,也算是偷天换日。这个工作做起来其实也不麻烦,有了SQLT这个工具,其实就容易得多。SQLT可参考使用sqlt手工创建sql_profile(r4笔记第37天)

但是很快做了尝试发现问题远没自己想得那么简单,因为替换之后我可以明显看到SQL的执行效率下降了,原本需要25秒,现在需要至少2分钟左右。对于1分钟执行频率的语句来说影响会被放大,导致SQL执行效率越来越差,有点多米诺骨牌的味道。

所以在2分钟的尝试中,我得出了一个初步结论,单纯替换执行计划是粗放的。因为对SQL Profile有备份,所以马上进行了恢复,恢复为原来的执行计划。

那么可以有一个很直观的感觉,那就是表的分区的影响,会把语句的性能瓶颈问题放大。

这个表有4千多万的数据,目前只有一个默认分区,看来也是一个遗留问题,要把数据再次重新分布,真不是一件简单的事情,而且sdk_start这个表的逻辑比较特别,只需要保留近2周左右的数据即可(按照日期进行分区),所以就牵扯到一个数据清理的问题,目前来看申请维护时间也有些不太合适。所以在线重定义又派上用场了。

我使用如下的语句生成了批量的分区语句,把近1个月的分区先补充出来,剩下的统统都放到默认分区,最后直接truncate pmax分区即可完成数据的清理工作。

select 'PARTITION P_'||to_char((trunc(sysdate)-30+level),'yyyymmdd')||' VALUES LESS THAN (TO_DATE('||chr(39)||(trunc(sysdate)-30+level+1)||chr(39)||', '||chr(39)||'YYYY-MM-DD HH24:MI:SS'||chr(39)||')) ' ||'TABLESPACE CMBI_MIN_DATA ,' from dual connect by level<30;

这个语句生成的分区补充信息类似下面的形式:

PARTITION P_20161208 VALUES LESS THAN (TO_DATE('2016-12-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE CMBI_MIN_DATA , PARTITION P_20161209 VALUES LESS THAN (TO_DATE('2016-12-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE CMBI_MIN_DATA , PARTITION P_20161210 VALUES LESS THAN (TO_DATE('2016-12-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE CMBI_MIN_DATA , 我们创建了表SDK_START_BAK,索引和权限也保持和原来的一致。

因为sdk_start没有主键,所以我使用rowid的方式来完成在线重定义的过程。

判断是否可以做在线重定义。

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('MBI','SDK_START',2);

开始在线重定义的过程。

exec DBMS_REDEFINITION.START_REDEF_TABLE('MBI','SDK_START','SDK_START_BAK',NULL,2);

在线重定义的这个过程蛮有意思,本质上就是物化视图的prebuilt复制,给表sdk_start_bak加上了物化视图的壳,完成数据同步之后脱壳(删除物化视图)。可以看到在线重定义的过程中会创建一个sdk_start_bak的物化视图。

OWNER      MVIEW_NAME       QUERY_LEN UP RE REFRESH_MODE REFRESH_METHOD   BUILD_MODE 
---------- --------------- ---------- -- -- ------------ ---------------- ---------- 
MBI        SDK_START_BAK          551 N     DEMAND       FAST             PREBUILT   

整个过程也是有条不紊,先复制数据,然后创建索引。CREATE INDEX "MBI"."IDX_SDK_START_SDA_NEW" ON "MBI"."SDK_START_BAK" ("SERVERTIME", "DEVICEID", "APP KEY") LOCAL 。。。。表sdk_start的数据量有4千多万,整个数据复制持续了5分钟左右。

为了保持数据的同步过程,减少GAP,可以使用如下的方式尽可能减少数据的差别。这个过程就有些类似物化视图的快速刷新。

execute dbms_redefinition.sync_interim_table ('MBI','SDK_START','SDK_START_BAK');

最后是收尾阶段,完成数据字典信息的替换。

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('MBI','SDK_START','SDK_START_BAK');

最后完成在线重定义之后,清理默认分区即可。

整个过程其实熟练掌握,走下来还是比较流畅的。

但是再次查看执行计划,发现执行效率没有任何改变,这个时候我们不要气馁,因为这个问题经过之前的分析,其实有更好的执行计划,那就是统计库2中的执行计划,我们再次偷天换日,替换执行计划。

可以很明显看到语句的性能有了飞速的提高。2秒钟即可完成。

其实对于这个问题有3点总结:

  1. 首先一个好的执行计划是基于准确的数据统计信息的基础上。有些场景下Oracle分析的执行计划可能不是最优的,我们可以在这个基础上做一些改变。
  2. 我们使用了分区的方式其实可以大大提高数据筛查的范围,原来需要扫描100个分区的工作量,现在只相当于扫描了1个分区。这个提高,如果使用得当,比指数级还高。
  3. 对于执行计划的在线替换,SQLT是一个好工具,CoE提供的这个工具还是有很强大的功能,替换稳定执行计划只是SQLT功能的冰山一角。

最后还是开头所说的那句话:优化是一个持续的改进,我们碰到的问题,最终的原因可能五花八门,但是正如柯南所说,真相只有一个。

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

原文发表时间:2017-01-07

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏带你撸出一手好代码

sql连接查询中on筛选与where筛选的区别

sql查询这个东西, 要说它简单, 可以很简单, 通常情况下只需使用增删查改配合编程语言的逻辑表达能力,就能实现所有功能。 但是增删查改并不能代表sql语句的所...

3558
来自专栏微信公众号:Java团长

从程序员的角度深入理解MySQL

不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API,即实例。

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

数据库收缩数据文件的尝试(三)(r11笔记第22天)

不知道大家在数据库运维中是否会有这样的困扰,一个数据文件里没有多少数据,但是数据文件的大小却调不下来,尝试使用resize来调整屡屡失败。如果一个数据文件里...

34612
来自专栏Java学习网

MySQL大表优化方案

  当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化:   单表优化   除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分...

8136
来自专栏更流畅、简洁的软件开发方式

用node.js实现ORM的一种思路

  ORM是O和R的映射。O代表面向对象,R代表关系型数据库。二者有相似之处同时也各有特色。就是因为这种即是又非的情况,才需要做映射的。   理想情况是,根据关...

2549
来自专栏架构师之路

58同城mysql实战(纯干货)

《大数据量下,58同城mysql实践》 WOT(World Of Tech)2015,互联网运维与开发者大会将在北京举行,会上58同城将分享《大数据量下,58同...

5465
来自专栏Spark学习技巧

flink 有状态udf 引起血案一

最近在做一个画像的任务,sql实现的,其中有一个udf,会做很多事情,包括将从redis读出历史值加权,并将中间结果和加权后的结果更新到redis。

2805
来自专栏数据和云

那些年,我们处理过的SQL问题

作者 | 郑林松,朗新科技股份有限公司数据库技术专家,从业10多年,主要服务移动运营商客户,电力客户,证券客户,制造业客户。精通 Oracle 性能优化,故障诊...

1182
来自专栏程序猿

小米开源soar一款对SQL进行优化和改写的自动化工具

SOAR(SQL Optimizer And Rewriter)是一个对SQL进行优化和改写的自动化工具。 由小米人工智能与云平台的数据库团队开...

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

通过top命令抓取cpu高消耗的sql (44天)

top命令在linux环境维护中很实用,虽然功能缺失不够sar那么全面。今天和大家分享一个通过top命令来抓取性能sql的案例。 通过top命令抓取了如下的信息...

3906

扫码关注云+社区

领取腾讯云代金券