星期二 三月 06, 2012

Oracle 11g 针对SQL性能的新特性(三)- SQL Plan Management

SQL Plan Management (SPM)


历史


SQL的执行效率,取决于它的执行计划是否高效。 优化器的算法是一个平衡,需要收集尽量少的信息,用尽量快的速度试图去得到一个最优的执行计划,这也决定了它不是万能的。 所以Oracle提供了一些辅助手段来“修复”优化器可能产生的错误,并不断改进这些方法。 

Oracle 8: hint
Oracle 8i&9: stored outline
Oracle 10: sql profile
Oracle 11: sql plan manangement




简介


在Oracle 11g之前,执行计划一直是作为“运行时”生成的对象存在。虽然oracle提供了一些方法去指导它的生成,但Oracle一直没有试图去保存完整的执行计划。 从11g开始,执行计划就可以作为一类资源被保存下来,允许特定SQL语句只能选择“已知”的执行计划。 


同其他方法相比,SPM更加的灵活。如我们所熟知的,一条带有绑定变量的SQL语句,最好的执行计划会根据绑定变量的值而不同,11g以前的方法都无法解决这个问题。在11g中,与adaptive cursor sharing配合,SPM允许你同时接受多个执行计划。执行时,根据不同的变量值,SPM会花费很少的运算从中选择一条最合适的。 


概念


SQL Plan Management SPM:oracle 11g 中提供的新特性,用来更好地控制执行计划。 
Plan History: 优化器生成的所有执行计划的总称
SQL Plan Baseline: Plan History里那些被标记为“ACCEPTED”的执行计划的总称
Plan Evolution: 把一条执行计划从Plan History里标记为“ACCEPTED”的过程
SQL Management Base SMB: 字典表里保存的执行计划的总称,包括Plan History,SQL Plan Baseline和SQL profile。


SPM的特点


o 与profile和outline相比,更加灵活的控制手段
  + 可以有很多的计划被保存下来,只有"ENABLED"并且"ACCEPTED"的执行计划才可以被选择。 
  + 允许有多个"ACCEPTED"的执行计划,根据实际情况进行选择。 
  + 可以用手工或者自动的方式,把执行计划演化(evolve)为"ACCEPTED"。 还可以控制只让性能更好的计划被接受。
  + 允许设置"FIXED"的计划。这样其他的计划将不会被选择。


o SPM使计划真正的稳定。 outline的缺点是太过死板,当数据量大幅度变化时无法做出相应的改变。 SQL proifle的缺点是,当数据量变化时,STA(SQL Tuning Advisor)会不可预知地去更改执行计划。 而SPM则会提供几个完整的plan供选择。 


SPM的控制方式


SPM通过几个标记来实现对执行计划的控制:


o Enabled (控制活动)
  + YES (活动的,但不一定会被使用)
  + NO (可以理解为被标记删除)
o Accepted (控制使用)
  + YES (只有 “Enabled” 并且 “Accepted” 的计划才会被选择使用)
  + NO (如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执行)
o Fixed (控制优先级)
  + YES (如果是“Enabled”并且“Accepted”,会优先选择这个计划,这个计划会被视为不需要改变的)
  + NO (普通的计划,无需优先)

另有一个被动的标记:
o Reproduced (有效性)
  + YES (优化器可以使用这个计划)
  + NO (计划无效,比如索引被删除)


SPM如何捕捉执行计划


o 自动捕捉
  1. 首先把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置成TRUE
  2. 从这个时刻开始,所有执行两次以上的SQL语句会被观测,执行计划会进入Plan History。有个别例外的,参见note 788853.1
  3. 生成的第一个执行计划被标记为ENABLED并且是ACCEPTED,后续的执行计划会被标记为ENABLED但不是ACCEPTED。
  4. 这时把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置会FALSE,新的语句将不会创建Baseline。
  5. 需要注意的是,即使关闭了自动捕捉,针对存在baseline的SQL,由于ACS的作用,仍旧会有新的PLAN生成,新的Plan仍会进入Plan History,标记为ENABLED但不是ACCEPTED。参见“执行计划的选择”。 


o 批量导入 (这些导入的baseline都会被自动标记为ACCEPTED) 
   Oralce提供四种方式把计划导入到sql plan baseline中。 
   + 从 SQL Tuning Set STS 导入
      DBMS_SPM.LOAD_PLANS_FROM_SQLSET
   + 从Stored Outlines 中导入 
      DBMS_SPM.MIGRATE_STORED_OUTLINE
   + 从内存中存在的计划中导入
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;
   + 通过staging table从另外一个系统中移植
      DBMS_SPM.CREATE_STGTAB_BASELINE
      DBMS_SPM.PACK_STGTAB_BASELINE
      DBMS_SPM.UNPACK_STGTAB_BASELINE




执行计划的选择过程


在OPTIMIZER_USE_SQL_PLAN_BASELINES被设置成默认值TRUE,SQl Plan Baseline就会起作用。 


1. 首先,无论是否存在baseline,oracle都会正常进行硬解析或者软解析,为SQL生成一个执行计划。 由于ACS和bind peeking的作用,存在baseline的SQL有可能在这时生成一个不同于baseline的执行计划。
2. 如果baseline不存在,就按生成的计划执行。如果baseline存在,那么要查看history里是否有这个计划,如果没有,就将这个计划插入,并标记为ENABLED,NON-ACCEPTED. 
3. 在baseline中查看是否有FIXED的计划存在,如果存在,执行FIXED的计划,如果存在多个FIXED的计划,根据统计信息重新计算cost,选择cost小的那个。
4. 如果FIXED的计划不存在,就选择ACCEPTED的计划执行。 如果存在多个ACCEPTED的计划,根据统计信息重新计算cost,选择cost小的那个。


* 注意这里每次重新计算cost的代价不大,因为执行计划是已知的,优化器不必遍历所有的可能,只需根据算法计算出已知计划的cost便可


执行计划的演化(evolution)


执行计划的演化指Plan History里的执行计划从NON-ACCEPTED,变成ACCEPTED的过程。 如果上所述,由于ACS和Bind Peeking的作用,存在baseline的SQL有可能生成新的执行计划,被保存到Plan History中。 Oracle提供了API,通过自动或手工的方式,将一个计划标记为ACCEPTED,这个计划就会被后续的执行所选择。 


使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE这个API来控制执行计划的演化。语法:
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
  sql_handle IN VARCHAR2 := NULL, --> NULL 表示针对所有SQL
  plan_name  IN VARCHAR2 := NULL,
  time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
  verify     IN VARCHAR2 := 'YES',
  commit     IN VARCHAR2 := 'YES' )
RETURN CLOB;


这里由两个标记控制:
o Verify 
  + YES (只有性能更好的计划才会被演化)
  + NO (演化所有的计划)
o Commit
  + YES (直接演化)
  + NO (只生成报告)


这里可以通过不同的排列组合,达到不同的效果:
o 自动接收所有性能更好的执行计划 (Verify->YES, Commit->YES)
o 自动接收所有新的执行计划 (Verify->NO, Commit->YES)
o 比较性能,生成报告,人工确认是否演化 (Verify->NO, Commit->NO)


* 对于性能的验证的方式,oracle会去实际执行来比较buffer gets


修改已有的Baseline


通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE来完成。 


DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
  sql_handle      IN VARCHAR2 := NULL,
  plan_name       IN VARCHAR2 := NULL,
  attribute_name  IN VARCHAR2,
  attribute_value IN VARCHAR2 )
RETURN PLS_INTEGER;


比如,把某个baseline 标记为FIXED,更多属性请参见官方文档


SET SERVEROUT ON;
DECLARE
  x NUMBER;
BEGIN
  x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
    sql_handle      => '&&sql_handle',
    plan_name       => '&&plan_name',
    attribute_name  => 'FIXED',
    attribute_value => 'YES' );
END;
/


常见应用


o 我们常见的一个场景是,一条SQL在使用hint时会生成一个好的计划,我们需要以此在原SQL上创建一个baseline。 具体方法请参加note 787692.1


注意


o 当您使用多种方式控制执行计划时:
  + Stored Outline存在时,它具有最高的优先级。
  + 已经实施的SQL profile会被自动加入到SQL plan baseline中
  + STA(SQL Tuning Advisor) 会自动接收新的profile,意味着它会生成新的baseline
o 如果可能话,尽量移植到SPM,混合多种方式会变得复杂


相关参数


optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
create_stored_outline
use_stored_outlines


参考文档


White paper: SQL Plan Management in Oracle Database 11g


How to Use SQL Plan Management (SPM) - Example Usage (Doc ID 456518.1)
Plan Stability Features (Including SPM) Start Point (Doc ID 1359841.1)
HOW TO LOAD SQL PLANS INTO SPM FROM AWR (Doc ID 789888.1)
Sql Plan Baseline Not always created (Doc ID 788853.1)
Transporting SQL PLAN Baselines from one database to another. (Doc ID 880485.1)








星期一 十月 03, 2011

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官方文档


星期一 九月 26, 2011

SQL调优工具SQLT简介(一)

背景

    SQLTXPLAIN (简称SQLT) Oracle提供的一种用来诊断SQL语句调优问题的工具。通常,当用户遇到一个SQL语句调优问题时,Oracle Support会要求提供很多的信息,比如SQL语句,10046/10053 trace,对象统计信息,optimizer信息等等。这些信息的收集是非常繁琐的事情,而且需要用户具有深入的产品知识。由于信息收集不全,或者客户不知道如何收集,导致一些问题最终无法解决。Oracle提供了SQLT这个工具来解决这个问题。

功能

  • 收集单条SQL语句的信息,包括:
  • SQL文本
  • 执行计划(explain)
  • 真实执行计划(row source operation)
  • 运行时的信息(时间,记录数等等)
  • 10046/10053 trace
  • 收集影响optimizer的统计信息(包括SYSTEM statisticsObject statistics)
  • 收集所有可能会影响optimizer计算的参数:
  • 平台
  • 版本
  • NLS信息
  • 初始化参数
  • fix_control
  • gather_statistics_job
  • 如果Oracle Tuning Packs是可以使用的话,自动调用SQL Tuning Advisor来生成优化报告。
  • 自动导出相关数据以方便客户/Oracle Support/Oracle Development创建test case来重现客户的问题,加速问题的处理。
  • 自动生成SQL Profile的脚本,来固定SQL的执行计划。

特点

  • SQLT是用SQLPL/SQL编写的,可以运行在各种不同版本操作系统的数据库。
  • 代码都是非加密的,可以清楚的看到操作的内容。
  • 不会泄露用户敏感数据。比如Column的最大/最小值还有histogram可以选择隐藏起来。
  • 安装在自己的schema下,不会影响用户数据。
  • 免费下载和使用,只需有一个可用的My Oracle Support license

下载及使用

  • 下载:下载文档 ID:215187.1中的附件需登陆My Oracle Support)。目前SQLT有两个不同的版本,一个是基于9.2/10.1的,另一个是基于10.2以后的。
  • 使用说明:解压缩文件夹中的sqlt_instructions.html或者SQLTXPLAIN.pdf。在解压缩文件夹下的doc目录下也可以找到相同的信息(PPT)

    在绝大多数的情况下,SQLT包含了诊断SQL语句调优问题的所有必要信息。因此推荐客户尽可能安装并用它来收集信息,以便于问题更有效率的解决。

         如果您在使用SQLT的过程中遇到问题,或者是有任何改进的建议或意见,可以在文档ID:215187.1下添加英文评论,您的评论会直接反馈给作者。

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
   
       
今天