本文为自适应游标共享(Adaptive Cursor Sharing)功能的第二部分,主要介绍ACS有效时的状况例子,以及ACS处理流程。
关于ACS无效时的状况例子,以及ACS概述请参考前篇文章:
自适应游标共享(Adaptive Cursor Sharing)(一)
下面让我们看看,在11g后的版本上自适应游标共享(Adaptive Cursor Sharing 以后简称ACS)功能有效时,是如何改善以前由于绑定变量导致的性能问题的。
在以后执行过程中,我们可以通过观察相关的动态视图,来看看在这个过程中都发生了什么。 首先,我们介绍下在以后过程中用到的主要4个视图,下面是相关内容的简单介绍。
关于以上视图的定义和内容详细可以参考在线文档。
Oracle Database Online Documentation 11g Release 2 (11.2) / Database Reference
V$SQL V$SQL_SHARED_CURSOR V$SQL_CS_SELECTIVITY V$SQL_CS_HISTOGRAM V$SQL_CS_STATISTICS
下面开始我们的测试 (基于11.2.0.4版本测试):
0.准备测试用表和数据
Test1:指定变量值为3,首次执行
首先我们指定变量值为3,首次执行下面的SQL文
1.因为绑定变量窥视(Bind Peeking)功能的影响,所以硬解析选择执行计划时,会把绑定变量值3代入到SQL文中计算基数,SQL文在10000条数据中选择了30条数据,所以,选择了索引IND1进行INDEX RANGE SCAN。
当SQL文第一次被执行的时候,因为绑定变量窥视(Bind Peeking)功能的影响,所以硬解析选择执行计划时,会把绑定变量值3代入到SQL文中计算选择率。 SQL文在10000条数据中选择了30条数据,选择率为0.03,所以优化器选择了通过索引IND1进行访问数据(INDEX RANGE SCAN)。
查看相关的视图:
根据上面的相关信息,我们可以知道,当包含绑定变量信息的SQL文第一次执行的时候,会发生下面的一些动作:
Test2:指定变量值为9,执行SQL文
下面我们传递一个变量9,再次执行SQL文。
虽然SQL文在10000条数据中选择了9000条数据,选择率应该为0.9, 但是因为该SQL文已经硬解析过了,所以这次是软解析,使用以前的执行计划,通过索引IND1进行访问数据(INDEX RANGE SCAN)。
再次观察相关动态视图的变化:
根据上面的相关信息,我们可以知道,当SQL文第二次执行, 并且绑定变量值和之前的绑定变量值的选择率很大时,会发生下面的一些动作:
下面我们传递一个变量9,再次执行SQL文:
由于上次执行时发现,由于变量值的影响SQL文的选择率预估值和实际执行时的差别很大(Bind Aware),所以这次执行SQL文重新编译,执行计划发生了变化,通过全表扫描执行。
再次观察相关动态视图的变化:
根据上面的相关信息,我们可以知道,当SQL文第三次执行时(和第二次相同的变量),会发生下面的一些动作:
指定变量值为3 (和最初执行时一样),再次执行SQL文
这次执行计划发生了变化,SQL文重新解析,通过索引扫描执行。 再次观察相关动态视图的变化:
根据上面的相关信息,我们可以知道,当SQL文第四次执行时(和第一次相同的变量), 由于第一次生成的游标变成IS_SHAREABLE为N,会发生下面的一些动作:
指定变量值为5,再次执行SQL文. 我们知道对于本条SQL文变量值为5时符合条件的选择率是50/10000=0.005,并不在以前执行过游标的选择率范围内(变量值为3的选择率范围为0.002702~0.003302;变量值为9的选择率范围为0.810810~0.990990), 所以SQL文 依然是进行了硬解析。
SQL文重新解析,通过索引扫描执行。再次观察相关动态视图的变化:
根据上面的相关信息,我们可以知道,当SQL文第五次执行时(不在之前的变量值的选择率范围内),会发生下面的一些动作:
ACS通过这种游标合并的操作,可以尽量减少子游标的数量,避免生成过多的子游标,减少内存等资源的浪费。
补充
我们看到,在V$SQL_CS_HISTOGRAM视图中,每个子游标都有3行直方图数据,分别为Bucket 0、Bucket 1和Bucket 2,针对SQL文的每次执行,Oracle都会在相应的Bucket 行中记录子游标的执行次数。
子游标的执行次数到底记录在哪个Bucket 呢? 通常Oracle是根据实际执行所操作的总行数(Row Source Processing)来生成直方图的。
一般条数较多时,记录的方式如下:(
而这个直方图最大的作用在于,比较SQL文的Child#0的Bucket 的高度(COUNT),如果其中Bucket的高度满足一定条件时,就会启用ACS功能(Bind Aware)。 ※注意: 其判断逻辑在这里暂时不做介绍。
总结
通过上面的2个例子,我们基本了解了ACS的基本处理流程和ACS的好处: 解决由于绑定变量窥视功能导致的性能问题。
下面引用一下Oracle ACE Mohamed Houri做的一个ACS流程图,来总体的回顾一下ACS的处理过程。
Oracle Database Online Documentation 12c Release 1 (12.1) / Database SQL Tuning Guide >Adaptive Cursor Sharing
Mohamed Houri’s Oracle Notes