昨天的一篇文章复杂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点总结:
最后还是开头所说的那句话:优化是一个持续的改进,我们碰到的问题,最终的原因可能五花八门,但是正如柯南所说,真相只有一个。