sql 按小时备份的文件都很大_SQL性能优化实例解析(3)-CSDN博客

进行用户数据分析，可是一个分析就跑了将近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演示