sql substr函数_SQL性能优化实例解析（5）

不是很久很久以前，而是几年前，RWP培训，茶歇，一个零售业客户拿出来一个SQL Monitor Report，说需要进行即时分析，秒出结果，可是当时SQL执行时间是40多分钟。

RWP老大亲临了那一场培训，亲历了那一次茶歇，茶歇完了问题就找到了，SQL秒出了，客户花了三天半的时间参加培训，反馈，单单这一个SQL的优化，就值得了所有的时间和花费。

去文末拿到SQL Monitor Report看看吧，老大能够在一个茶歇时间里搞定的SQL问题，你，能搞定吗？要花多长时间搞定？赠送老大的原话给你：

“我都搞定了，你还没搞定，你不紧张自己的工作吗？”

呵呵～～

SQL语句很复杂：

这么复杂的SQL，想先搞清楚SQL语句是干什么的，肯定没法比老大速度快啦～～

还是从SQL Monitor Report下手吧，先看SQL执行的基本情况：

SQL执行时间41.2分钟，并行度是4，期望使用并行执行，实际是串行执行。

最右边，Wait Activity%那一列，表示的是执行每一步的时候，对应的数据库里面的活动(Activity)的样本数，颜色条最长的步骤，意味着执行那一步花的时间最长，就是入手点。

入手点的两行，都是对物化视图MV_LWSN_GLCOMPREL进行全表扫描。最长的那行，优化器估计返回的行数是4916，实际返回的行数是40M，Executions是5776，就是执行了5776次。

下一步应该是什么思路呢？

对物化视图MV_LWSN_GLCOMPREL收集统计信息吗？No no no。

把本系列文章第二篇的要点拷贝过来：

如果SQL Monitor Report里面花时间最多的步骤，是在Nested Loops Join里面，那么首先要检查Nested Loops Join的驱动表，对比驱动表的Estimated Rows(估计行数)和Actual Rows(实际行数)。如果有数量级的差异，那么检查驱动表的统计信息并修正。

还记得吗，我们提出上述要点的原因，是要上一个层次思考问题。上一个层次，就是Join的方法好不好？

可是，本系列文章第二篇是个简单的例子，两个表做Nested Loops Join。

而本文例子中，花时间最多的步骤，对物化视图MV_LWSN_GLCOMPREL进行全表扫描，是在Hash Join里。MV_LWSN_GLCOMPREL是Hash Join的Probe表，然后这个Hash Join又嵌套在好几层join里面，这好几层join里面包括Nested Loops Join和Merge Join。

那么问题是，要检查哪一层join呢？

对于本文的例子，实际上同理，而且要更上一个层次 - 嵌套那么多层，每一层的join方法好不好？如果外层的join方法不好，再怎么优化里层也是徒劳。

一路向上追溯，看优化器是从哪里开始把join方法搞错的。SQL Monitor Report默认是 展开 所有执行计划的步骤，对于复杂的执行计划，一路追溯的同时，也可以 收起 部分执行计划，以便于更清晰的看问题。收起来之后，是下图的样纸，看起来会清晰很多：

沿着Actual Rows列，一路向上追溯5776，最外层的Nested Loops Join的驱动部分，是一个Nested Loops Join，优化器估计返回1行，实际返回5776行。Nested Loops Join驱动部分返回5776行，意味着后面的VIEW的部分的执行次数是5776次。把VIEW的部分一路展开，就能看到花时间最多和第二多的执行步骤。

在 展开 的SQL Monitor Report里面，看起来是下面的样子：

那么接下来，优化器为什么对驱动部分的Nested Loops Join返回行数进行了错误的估计呢？

我们可以看到，驱动部分的Nested Loops Join里面是一系列的Nested Loops Join，最里面的Nested Loops Join的驱动表，是LWSN_GLNAMES表，也是整个这个部分的最终驱动表。

看下图，有意思的是，访问LWSN_GLNAMES表的方法是通过索引，优化器估计索引扫描返回的行数和实际非常接近，但是在对表做了过滤之后估计却只有一行。

那么接下来肯定是要看这里访问LWSN_GLNAMES表的predicate。这是Flash版本的SQLMonitor Report，在Plan tab里面，可以看到访问LWSN_GLNAMES表的predicate，是：

("GLN"."GLNSET3_SS_SW"='N'AND SUBSTR("GLN"."VAR_LEVEL_DISP",1,4)<>'0010')

如下图：

优化器认为过滤条件具有很高的选择度，但是实际上只过滤掉很少的记录。注意到过滤条件上有个SUBSTR() 函数。优化器对于过滤条件上使用了函数的情况，默认情况是使用预设的算法进行估计，而且会倾向于估计过滤掉很多数据(嗯，优化器似乎很懂得你对索引的偏爱)。

优化器对于带有函数的过滤条件所返回结果的错误估计，也正是本文例子中问题的根源所在。

做性能优化，最重要的是，知道问题是什么，答案总是容易的部分。在SUBSTR()函数上收集扩展统计信息，优化器就可以根据扩展统计信息进行更准确的估计，而不是盲目的估计一个偏低的值。

在SUBSTR()函数上收集扩展统计信息后，SQL执行时间6秒钟，即时分析，秒出结果啦～～同时，SQL的实际执行也用上了并行，如下图所示：

再看执行计划里面对于LWSN_GLNAMES表的估计，之前是1行，现在是141行，实际是8343行，之前是差了8343倍，现在是差了大概60倍。别忘了，LWSN_GLNAMES表还有其他的过滤条件。这不是一个完美的世界，虽然现在还是差了大概60倍，但是比之前差8343倍还是准确多了，准确到可以让优化器做出一个更好的判断，参见下图：

优化器估计LWSN_GLNAMES表141行后，采取了Hash Join，所以现在对物化视图MV_LWSN_GLCOMPREL也只是进行单次扫描就够了，而且进行的还是单次并行扫描。优化器这个远不完美但是更准确的判断，使得SQL的执行也可以并行起来了。

总结一下

问题SQL，执行时间41.2分钟，目标是即时分析，秒级完成

• 拿到问题SQL的SQL Monitor Report (数据库自带)

• 检查最右边的Wait Activity%列，找出颜色条最长的那一行，做为入手点

• 检查入手点那一行的具体情况

• 如果入手点那一行是在Nested Loops Join里面，那么首先要检查Nested Loops Join的驱动表，对比驱动表的EstimatedRows(估计行数)和Actual Rows(实际行数)。如果有数量级的差异，那么检查驱动表的统计信息并修正。

• 如果入手点那一行是在多层嵌套的Join里面，那么要根据情况一路向上追溯。本文例子中，是根据执行次数(Executions)5776一路向上追溯Actual Rows列，追溯到最外层的Nested Loops Join的驱动部分，是一个Nested Loops Join，优化器估计返回1行，实际返回5776行。

• SQL Monitor Report里面有好几个tab，信息很全，可以通过这些信息帮助进一步定位问题。我们这个例子里面使用了Plan tab里的Predicate信息来帮助进一步确认问题

• 我们定位出问题根源是，最外层的Nested Loops Join的最终驱动表(LWSN_GLNAMES)上的查询条件里面使用了SUBSTR()函数，导致优化器对于LWSN_GLNAMES表的返回行数严重低估

• 针对问题根源，实施对LWSN_GLNAMES表上的SUBSTR()函数收集扩展统计信息的解决方案

• SQL执行时间变为6秒钟，目标达成

怎么样，你花了多长时间找到本文例子的问题所在？ 要是刚才你没看的话，那现在有答案了，你要花多长时间能在SQL Monitor Report上指出本文例子的问题所在？ 人生苦短，一个茶歇的功夫够不够长，呵呵～～

Oracle数据库自带的SQL Monitor Report神器，可以给到你最理性的不带有任何情绪的数据，让你进行以数据为依据的分析和诊断，不用再靠猜，靠蒙，靠运气。SQL Monitor Report，你已经拥有，好好用起来吧

将这个系列文章的链接整理放到这里，希望能够对你有所启发和帮助，也希望你分享给你身边可能有需要的朋友，能够帮助到别人是多么的美好～～

本系列文章的链接如下：

• SQL性能优化实例解析(4)

• SQL性能优化实例解析(3)

• SQL性能优化实例解析(2)

• SQL性能优化实例解析(1)

更多SQL性能优化实例解析，欢迎在哔哩哔哩(app或微信小程序)上搜索“Oracle公益课堂”，就可以看到视频啦 -“RWP大开眼界系列-SQL MonitorReport分析”

编辑：萧宇