进行用户数据分析,可是一个分析就跑了将近1个小时,这样下去是不行的,有什么办法优化吗?
如果读过我们这个系列的前两篇文章,相信你的反应应该是
1.这个SQL需要被执行吗?
2.如果上一个问题的回答是“是”的话,那么SQL Monitor Report拿来看看
这个SQL的SQL Monitor Report的获取方法在文末,我想请你现在就去拿到SQL Monitor Report,然后自己到处点点看看,问题在哪里?
首先有个自己的思考很重要,你自己思考完了之后,我们一块来分析一下
一眼望过去,SQL执行时间56.5分钟。
如果读过我们这个系列的前两篇文章,相信你马上就会瞄向最右边,Wait Activity%那一列,表示的是执行每一步的时候,对应的数据库里面的活动(Activity)的样本数,颜色条最长的步骤,意味着执行那一步花的时间最长,就是入手点。
接下来,要检查入手点的具体情况。入手点是HASH JOIN BUFFERED,使用了831GB的TEMP。入手点那一行是JOIN,通常需要进一步查看那个JOIN里面的具体情况。
HASH JOIN BUFFERED的一边是CUST_IDENTIFIER_DIM,另一边是DATE_DIM与 TRANSACTION_ITEM_FCT的HASH JOIN的结果。
CUST_IDENTIFIER_DIM的Estimated Rows(估计行数)是203M,Actual Rows(实际行数)是222M。数量级相同,只有很小的差异,这肯定可以算很不错的估计了。
DATE_DIM与 TRANSACTION_ITEM_FCT的HASH JOIN的结果,Estimated Rows(估计行数)是19G,Actual Rows(实际行数)是19G。这个行数的估计也是相当不错。
在我们这个系列的前面两个例子里面,都提到的是估计行数有严重偏差,导致执行计划不够优化的问题。可是在这个例子里面,估计行数非常准确,那么下一步的思考方向应该是什么呢?
聪明的你,一定会想到,估计行数影响的是执行计划的生成,如果生成这个步骤没有问题,那么下一个步骤就是执行。所以就是,执行的过程中是否存在执行效率差的问题?
点开SQL Monitor Report里面的Activity Tab,可以看到只有下图红圈中两个较短的时段整体Activity较多。这条SQL的并行度可是176,可是下图中的Active sessions,前半部分基本不到20,后半部分不到5,只有两个红圈部分才到了100左右。这个执行,就是典型的,你以为是并行,实际是,呵呵。
在上图里,一眼就可以看到,Database Time存在倾斜(skew),instance 6上的PX server的时间最长,5.9小时。可以点开instance 6前面的 + 号,查看每个PX server的具体执行时间(Database Time),如下图所示。
Parallel Server 20(p073),Database Time是53.6分钟,IO Requests是2606K。这个页面里面看到的其他的PX server的Database Time都是不到6分钟,IO Requests都是150K多点。
这个SQL执行的并行度是176,执行时间是56.5分钟,可是实际上看起来,执行时间根本就是要看Parallel Server 20(p073)的脸色!
所以现在问题就是,为什么会有一个Parallel Server 20(p073)做了比其他PX server多十几倍的IO?
回到执行计划里面,可以看到,HASH JOIN BUFFERED的两方,是通过HASH的方法进行并行进程间通信的,就是下图里面的PX SEND HASH。
简单来说,执行过程是,红色的那组并行进程,扫描CUST_IDENTIFIER_DIM表,然后通过HASH算法将结果发送给蓝色的那组并行进程(上图中的第一个PX SEND HASH),蓝色的那组并行进程收到这些数据(PX RECEIVE),因为是HASH JOIN,所以蓝色都这组进程同时build hash table。
接下来,红色的那组并行进程,扫描TRANSACTION_ITEM_FCT表并与 DATE_DIM 表进行HASH JOIN,然后将JOIN的结果按照同样的HASH算法发送给蓝色的那组并行进程(上图中的第二个PX SEND HASH),蓝色的那组并行进程收到这些数据(PX RECEIVE),并完成HASH JOIN。
因为红色的那组并行进程在分发数据的时候,是通过HASH算法进行的,所以接下来的问题就是,数据本身是否存在分布不均衡的问题?比如某个值特别多,那个值的HASH值也是相同的,就都被发送到同一个PX Server处理了,Parallel Server 20(p073)刚好就是这个不幸的PX Server。
你可以在SQL Monitor Report里面看到这条SQL的文本。这条SQL的目的是为了支持高级会员卡服务,需要按ID计算出所有的持卡人的消费总金额,消费次数,以及访问网站的频度。
问题就出在,他们的网站是允许匿名访问的,他们使用 -1 做为每个匿名访问的用户的ID,而所有登录的用户的ID都是一个正的数字。实际的用户数据,是匿名访问的情况占了很大一部分。做HASH算法时,ID=-1的值肯定会落到同一个hash bucket里面,被同一个PX server 处理。
这样的设计,在功能上当然没有什么问题。但是在性能上么,你是不是马上就可以脑补出,在其他PX Server早就干完活的时候,只有Parallel Server 20(p073)吭哧吭哧处理ID=-1的那些数据的惨样儿了。
SQL优化,最重要的是搞清楚问题在哪里。现在问题清楚了,解决方案就容易了。
也可以使用UNION-ALL将上述两条语句合并成一个。
上面的办法是改SQL。如果不改SQL呢,也可以改数据,比如将匿名用户的ID设为负的随机数,而不是 -1 。
嗯,感谢读到这里,告诉你个秘密,这个例子里面的SQL Monitor Report,是11g时代的了。在11g时代,解决方案么,大概就是上面说的办法了。
从12c开始,优化器会自动探测 SKEW 并对SKEW的数据 使用HYBRID 的执行计划,而且在执行过程中用broadcast分发那些行数多的值,而对于行数少的值采用HASH的并行分发方式。这个能力,也就是12c之后的众多酷炫能力之一吧。什么,你也还在用11g?就说你呢,升级赶紧考虑起来吧~~
总结一下
问题SQL,执行时间56.5分钟
拿到问题SQL的SQL Monitor Report (数据库自带)
检查最右边的Activity%列,找出颜色条最长的那一行,做为入手点
检查入手点那一行的具体情况
如果入手点那一行是JOIN,通常需要进一步查看那个JOIN里面的具体情况
Estimated Rows(估计行数)和Actual Rows(实际行数)的对比是很重要的信息,如果估计有数量级级别的错误,大概率会导致不好的执行计划。如果估计的准确,那么就要进一步看具体执行的情况,我们这里先查看了Activity tab,再查看了Parallel tab里的Database Time信息,来检查具体执行的情况
我们定位出问题根源是数据倾斜
针对问题根源,实施将ID=-1的数据分拆到另一条SQL里面执行,或者将将匿名用户的ID由-1的数据改为负的随机数的解决方案
再看这个SQL Monitor Report的时候,已经感受到了岁月的痕迹,找不到客户实施解决方案后的执行时间了。
方法最重要,授人以鱼不如授人以渔,希望你喜欢我们的渔~~ SQL Monitor Report,从11g出世开始,就是神器,不管你是11g,12c,18c,还是19c,尽情的用起来吧~~
更多SQL性能优化实例解析,欢迎在哔哩哔哩(app或微信小程序)上搜索“Oracle公益课堂”,就可以看到视频啦 -“RWP大开眼界系列-SQL Monitor Report分析”。
扫描下方QR Code即刻预约ADW演示
编辑:Dr.Henry