Oracle 11g 针对SQL性能的新特性(一)- Adaptive Cursor Sharing

    Oracle 11g对SQL执行计划的生成过程做了很多改变,我们经常看到有客户抱怨,数据库在升级到11g以后,执行计划变得很不稳定,甚至难以预测。实际上,Oracle在最新版本中致力于让优化器变得更加“智能”,通过自我学习的方式,来改进目前体系上所存在的缺陷。

    我们将分章节为您粗略介绍下面几个新特性,这些很可能是造成您执行计划改变的原因。

    · Adaptive Cursor Sharing (ACS)
    · Cardinality Feedback (CFB)
    · SQL Plan Management (SPM)


Adaptive Cursor Sharing (ACS)


背景


众所周知,Oracle一直鼓励使用绑定变量,以帮助SQL去共享,减少硬解析带来的开销。 共享SQL的好处显而易见:

    · 减少共享池(Shared Pool)的使用
    · 减少SQL的解析(Parse)时间

共享SQL坏处呢? 是的,无论绑定变量如何变化,执行计划在第一次生成之后(bind peeking),就不再改变。所以,针对同一条SQL文本,我们无法针对每一组绑定变量,使用最合适的计划。这对于条件里包括范围查询的语句来说(大于, 小于, between等),影响很大。 Oracle曾试图用CURSOR_SHARING=SIMILAR来解决,但带来了更多的问题,“similar”也在11.1中被废弃。

在这种情况下,Adaptive Cursor Sharing 的引入,就是试图用统计的方法,提供一种介于“共享”和“非共享”之间的解决方案。


适用的SQL语句



考虑到开销,Oracle只针对下面情况使用ACS,这类CURSOR叫做bind sensitive

    · 相应列上有直方图,操作符: =:B,!=:B
    · 列上没有直方图,操作符: > :B,<:B,>=:B ,<= :B,like(11.2.0.2新增)




过程描述

1. 对于Bind Sensitive的SQL,从第一次执行开始,Oracle会额外做如下工作:
    · 根据实际执行所操作的总行数(Row Source Processing),生成直方图。直方图有3个bucket: Bucket 0: 小于1K行 ,Bucket 1:大于1K小于1M,Bucket 2:大于1M。 每次执行,就会在相应的bucket上加一。

2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0,也就是说,目前计划所造成的行操作变化很大,那么就开始实施ACS。所以SQL必须要执行两次以上才有可能。

3. ACS被触发以后,优化器会重新生成执行计划,相应的,会有新的Child生成。新的Child标记为bind aware,意思是这个Child是根据绑定变量生成的。所以,Oracle又会额外做一件事:
    · 针对每个Child,记下所执行的每组绑定变量的选择率(Selectivity),并用设计好的算法生成一个范围(high value 和 low value)。

4. 针对后续的每一次执行,Oracle会做如下处理:
    · 先查看本次绑定变量的选择率是不是落在已知的范围内。
    · 如果是,那么就使用之前的plan,并在直方图中相应的位置+1。
    · 如果不是,就会生成新的执行计划。再查看这个计划是不是已经存在的:
        · 如果计划不存在,生成新的Child。
        · 如果计划存在,同样会生成新的Child,并把之前生成相同执行计划的选择率移动到新的Child上,最后再把之前的Child标记为不可重用(is_sharable=no)


通过这种方式,Oracle试着实现更智能的共享。

监控视图


    · V$SQL
        ·is_bind_sensitive  是否适用于ACS
        ·is_bind_aware 是否针对变量的值来选择计划
        ·is_shareable  是否可用
    · V$SQL_CS_HISTOGRAM   根据所操作的行数,记录每个Child执行的次数
    · V$SQL_CS_SELECTIVITY  记录每个Child的每组变量的选择率范围
    · V$SQL_CS_STATISTICS   每个Child的执行情况,类似v$sqlarea


缺点



    · 更多的硬解析带来额外的开销。
    · 更多的Child会对共享池产生压力。
    · 偶尔,更准确的统计信息没有生成最好的plan

从上面过程我们可以看到,ACS是有比较大的开销,所以我们只针对一部分SQL进行监控(bind sensitive)。 这部分SQL中,只有一部分会启动ACS(Child0的两个bucket高度相同)。在启用ACS的SQL中,只有一部分会生成多于一个的计划。 以此,来降低ACS带来的额外开销。


注意


     上述描述是基于11.2.0.2,没有任何补丁的情况。ACS的运行效率还在不停改进中 。


参考

    · Adaptive Cursor Sharing Overview (Doc ID 740052.1)
    · Introduction to Adaptive Cursor Sharing concepts in 11G and mutimedia demo [Video] (Doc ID 1115994.1)
    · Adaptive Cursor Sharing in 11G (Doc ID 836256.1)
    · 11.2官方文档


评论:

good,it is very useful.

发表于 guest 在 2012年05月30日, 01:29 下午 CST #

2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0,也就是说,目前计划所造成的行操作变化很大,那么就开始实施ACS。所以SQL必须要执行两次以上才有可能。
________________________________________________________________

这样设计是为什么呢?
假设条件a、b、c、d的记录是100条以下,条件x的记录数是二十万条。如果前面执行一千条sql语句都是a、b、c、d这些条件,那条件为x的sql语句也必须执行一千次以上才能生成新的执行计划。如果这这期间如果a、b、c、d的条件的sql也在执行,那条件x的sql可能永远都不会生成新的执行计划了。这样是否太低效了?
我感觉:只要发现有两个Bucket的高度都大于0,就完全可说明目前计划造成的行操作变化会很大,就可以实施ACS了。

发表于 付强 在 2014年09月22日, 06:10 下午 CST #

2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0,也就是说,目前计划所造成的行操作变化很大,那么就开始实施ACS。所以SQL必须要执行两次以上才有可能。
________________________________________________________________

这样设计是为什么呢?
假设条件a、b、c、d的记录是100条以下,条件x的记录数是二十万条。如果前面执行一千条sql语句都是a、b、c、d这些条件,那条件为x的sql语句也必须执行一千次以上才能生成新的执行计划。如果这这期间如果a、b、c、d的条件的sql也在执行,那条件x的sql可能永远都不会生成新的执行计划了。这样是否太低效了?
我感觉:只要发现有两个Bucket的高度都大于0,就完全可说明目前计划造成的行操作变化会很大,就可以实施ACS了。

发表于 付强 在 2014年09月23日, 08:28 上午 CST #

2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0,也就是说,目前计划所造成的行操作变化很大,那么就开始实施ACS。所以SQL必须要执行两次以上才有可能。
________________________________________________________________

这样设计是为什么呢?
假设条件a、b、c、d的记录是100条以下,条件x的记录数是二十万条。如果前面执行一千条sql语句都是a、b、c、d这些条件,那条件为x的sql语句也必须执行一千次以上才能生成新的执行计划。如果这这期间如果a、b、c、d的条件的sql也在执行,那条件x的sql可能永远都不会生成新的执行计划了。这样是否太低效了?
我感觉:只要发现有两个Bucket的高度都大于0,就完全可说明目前计划造成的行操作变化会很大,就可以实施ACS了。

发表于 付强 在 2014年09月23日, 08:30 上午 CST #

这样设计是为什么呢?
假设条件a、b、c、d的记录是100条以下,条件x的记录数是二十万条。如果前面执行一千条sql语句都是a、b、c、d这些条件,那条件为x的sql语句也必须执行一千次以上才能生成新的执行计划。如果这这期间如果a、b、c、d的条件的sql也在执行,那条件x的sql可能永远都不会生成新的执行计划了。这样是否太低效了?
我感觉:只要发现有两个Bucket的高度都大于0,就完全可说明目前计划造成的行操作变化会很大,就可以实施ACS了。
------------------------------------------------

这是一个权衡。 太多的cursor变成bind aware会有额外的开销。 比如导致不断有新的child生成,shared pool的压力会随之增大,进而引发ORA-04031。 所以,在设计上,我们希望只在十分必要的情况下,才把cursor变成bind aware。 目前的模型离完美还差很远,保守起见,我们还是希望把数据库的稳定放在前提。

发表于 Roger Song 在 2014年09月24日, 09:20 上午 CST #

多谢 Roger Song的回复!
我还有个问题:【2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0....】,那是不是说Child1、Child2、Child3...的Bucket的信息仅仅表示该child计划执行过的次数,但不作为生成执行新的ACS的条件了?我测试了一下,感觉从Child1开始,ACS是否执行是根据v$sql_cs_selectivity中的low、high是否匹配条件变量的选择率。
还有,12c在ACS方面是否有所改进?

再次感谢。

发表于 付强 在 2014年09月26日, 06:14 下午 CST #

2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0,也就是说,目前计划所造成的行操作变化很大,那么就开始实施ACS。所以SQL必须要执行两次以上才有可能。
————————————————————————————————
我在11.2.0.3下进行了测试。
发现即使出现“两个Bucket的高度相同且大于0”也不一定生产新的child1计划。
例如,我的测试例子中先执行了20次Bucket0的SQL,然后执行了5次Bucket1的SQL,然后再执行5次bucket2的SQL,仍然没有生成child1。(v$sql_cs_histogram中,bucket_id为0的count值为20,bucket_id为1的count为5,bucket_id为2的count微5)。
所以,我认为:Oracle应该是根据目前执行计划中行操作变化大的比例达到一个阈值后才决定开始实施ACS的。
例如我的另一次测试,bucket0的SQL先执行5次,bucket1的SQL执行了2次,而bucket2的SQL执行到第二次的时候child1就生成了。这个时候v$sql_cs_histogram中没有出现“两个Bucket的高度相同”情况。

发表于 付强 在 2014年10月14日, 11:31 上午 CST #

2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0,也就是说,目前计划所造成的行操作变化很大,那么就开始实施ACS。所以SQL必须要执行两次以上才有可能。
________________________________________________________________

我在11.2.0.3上的测试情况来看,Oracle是根据任意两个Bucket高度相加等于另一个的高度的时候就开始实施ACS。也就是说执行次数少的Bucket的高度和等于最高的Bucket时,Oracle就知道当前的执行计划已经造成行操作变化很大。
例如,bucket0的高度为4,bucket1的高度为3,bucket2的高度为1,再次执行SQL就会触发ACS。

发表于 付强 在 2014年10月14日, 11:43 上午 CST #

1. 如我之前所说的,目前的设计,是要在执行计划和parse次数上达到一个平衡。

2. 如果ACS决定生成Child1,即表示cursor变成bind aware,后续执行时根据“v$sql_cs_selectivity中的low、high”, 这个是没错的。 就是文章提到的下面这部分:

“4. 针对后续的每一次执行,Oracle会做如下处理:
· 先查看本次绑定变量的选择率是不是落在已知的范围内。”

3. “两个Bucket的高度相同且大于0”。 抱歉这部分我说的不是很准确,应该是两个Bucket同时最高且大于零。 这里,希望您可以理解acs设计的意图。 oracle只有在意识到后续大规模的执行都有问题时,才启用ACS, 而不是发现几次问题就开启。毕竟ACS是有额外开销的,每次执行都要比对seelctivity

4. 至于您最后说的问题,我没有十分理解。 也许在新版本中有改变。 您需要先看一下v$sql_shared_cursor,确定child1的生成是ACS引起的(BIND_EQUIV_FAILURE)

发表于 Roger Song 在 2014年10月14日, 11:56 上午 CST #

2. 当Oracle 发现Child0有两个Bucket的高度相同且大于0,也就是说,目前计划所造成的行操作变化很大,那么就开始实施ACS。所以SQL必须要执行两次以上才有可能。
________________________________________________________________

我在11.2.0.3上的测试情况来看,Oracle是根据任意两个Bucket高度相加等于另一个的高度的时候就开始实施ACS。也就是说执行次数少的Bucket的高度和等于最高的Bucket时,Oracle就知道当前的执行计划已经造成行操作变化很大。
例如,bucket0的高度为4,bucket1的高度为3,bucket2的高度为1,再次执行SQL就会触发ACS。

发表于 付强 在 2014年10月14日, 11:57 上午 CST #

我在11.2.0.3上的测试情况来看,Oracle是根据任意两个Bucket高度相加等于另一个的高度的时候就开始实施ACS。也就是说执行次数少的Bucket的高
度和等于最高的Bucket时,Oracle就知道当前的执行计划已经造成行操作变化很大。
例如,bucket0的高度为4,bucket1的高度为3,bucket2的高度为1,再次执行SQL就会触发ACS。
~~~~~~~~~~~~~~~~~~~~~~~~~~

如果最后一次落在bucket1上,那么就是和之前提到的是一样的。

发表于 guest 在 2014年10月14日, 12:00 下午 CST #

我在11.2.0.3上的测试情况来看,Oracle是根据任意两个Bucket高度相加等于另一个的高度的时候就开始实施ACS。也就是说执行次数少的Bucket的高
度和等于最高的Bucket时,Oracle就知道当前的执行计划已经造成行操作变化很大。
例如,bucket0的高度为4,bucket1的高度为3,bucket2的高度为1,再次执行SQL就会触发ACS。
~~~~~~~~~~~~~~~~~~~~~~~~~~

如果最后一次落在bucket1上,那么就是和之前提到的是一样的。
——————————————————————————————————————
——————————————————————————————————————
我的测试是:bucket0、bucket1、bucket2高度分别为4、3、1时,即使最后一次的SQL落在bucket0上,照样会触发ACS生成child plan。

发表于 付强 在 2014年10月14日, 02:05 下午 CST #

3. “两个Bucket的高度相同且大于0”。 抱歉这部分我说的不是很准确,应该是两个Bucket同时最高且大于零。 这里,希望您可以理解acs设计的意图。 oracle只有在意识到后续大规模的执行都有问题时,才启用ACS, 而不是发现几次问题就开启。毕竟ACS是有额外开销的,每次执行都要比对seelctivity

4. 至于您最后说的问题,我没有十分理解。 也许在新版本中有改变。 您需要先看一下v$sql_shared_cursor,确定child1的生成是ACS引起的(BIND_EQUIV_FAILURE)

————————————————————————————————————————
3、“应该是两个Bucket同时最高且大于零”,从我的测试来看,应该说:任意两个bucket的高度相加大于等于另一个(或者,两个较小的bucket的高度相加大于等于最高的bucket)时,ACS就会启用。
4、我查看了一下v$sql_shared_cursor,child1的BIND_EQUIV_FAILURE值为“Y”。

发表于 付强 在 2014年10月14日, 02:13 下午 CST #

请教各位一个问题:
我今天在一个11.2.0.3的RAC数据库上发现一个奇怪的现象:v$sql中竟然还有一些sql的child_number=0,而is_bind_sensitive、is_bind_aware、is_shareable都等于'Y'。

按说第一次child_number等于0意味着是第一个生成的执行计划,is_bind_aware等于'Y'意味着这条SQL的执行是“针对变量的值来选择计划”。这是怎么回事呢?

还有,我在这个系统中发现child_number最大值为99,这是巧合吗?oracle对child curesor的生成有限制吗?

最后还有一个问题:child_number大于0、is_bind_sensitive='Y',这是什么情况呢?

希望能得到oracle官方的回答。谢谢。

发表于 付强 在 2014年11月19日, 11:27 上午 CST #

最后一个问题写错了,是child_number大于0、is_bind_sensitive='N'是什么情况?

发表于 付强 在 2014年11月19日, 11:29 上午 CST #

发表一条评论:
  • HTML语法: 禁用
About

本博客由Oracle全球技术支持中国区的工程师维护。为中文用户提供数据库相关的技术支持信息,包括常用的诊断工具、诊断方法、产品新特性、案例分析等。此外,MOS也陆续推出各类中文内容:技术通讯统一发布在Note 1529795.1 中,中文文档列表更新在Note 1533057.1 中,网上讲座请查看MOS文档 1456176.1,在"Archived"中可以下载历史的录音和文档。

Search

Archives
« 五月 2015
星期日星期一星期二星期三星期四星期五星期六
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
22
23
24
25
26
27
28
29
30
31
      
今天