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 #

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

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

Search

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