Oracle SQL执行计划基线总结（SQL Plan Baseline）

Oracle 11g开始，提供了一种新的固定执行计划的方法，即SQL plan baseline，中文名SQL执行计划基线（简称基线），可以认为是OUTL

一、基础概念
 
Oracle 11g开始，提供了一种新的固定执行计划的方法，即SQL plan baseline，中文名SQL执行计划基线（简称基线），可以认为是OUTLINE（大纲）或者SQL PROFILE的改进版本，基本上它的主要作用可以归纳为如下两个：
 
1、稳定给定SQL语句的执行计划，防止执行环境或对象统计信息等等因子的改变对SQL语句的执行计划产生影响！
 
2、减少数据库中出现SQL语句性能退化的概率，理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上！
 
注意：
 
1、从Oracle的发展角度来看，估计这种方法是Oracle发展和改进的方向，如今outline已经被废弃，sql profile估计在后续的发行版本中也难有改进，因此，对于从11g开始接触Oracle的朋友来说，一定要对sql计划基线有所了解，因为这是以后的主流！
 
2、SQL执行计划基线保存在数据字典中，查询优化器会自动判断使用他们。
 

Oracle教程：实例故障恢复

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

二、工作机制
 
从Oracle 11g开始，由于基线的存在，一条语句的解析过程大概如下：
 

1.SQL语句被硬解析后，CBO（优化器）会产生很多个的执行计划，CBO从中选择一个成本最低执行计划。
2.基于SQL语句的文本形成一个哈希值（signature)，通过这个哈希值来检查数据字典中是否存在同样的基线。
3.如果基线存在，优化器会对刚刚产生的执行计划和保存在SQL plan baseline中的执行计划进行比较。
4.如果基线中有与CBO刚产生的执行计划的匹配的SQL执行计划存在，并且被标记为可接受（‘accepted’），则这个CBO生成的执行计划被启用。
5.如果基线中没有匹配的SQ执行计划存在，CBO评估基线中被标记为‘accepted’的的多个执行计划，并选择其中cost最低的执行计划。（注意，一个语句的基线可以有多个执行计划被保存，这是与其他Outline和SQL profiel都不同的地方）
6.如果刚刚硬解析过程中CBO选择的执行计划比保存在基线中的执行计划COST都低，这个新生成的执行计划被标记为‘not-accepted’并保存在基线中。直到这个执行计划被演化且验证后才会被考虑使用，即标记为accepted(演化和验证，可以简单理解为Oracle确认这个执行计划可以带来更好的性能)。
 
Oracle 就是通过上面这种方式来确保SQL语句的性能不会退化（即第一部分中我归纳的第二个主要作用），称为“执行计划保守选择策略”
 

三、基线的一些特点
 
简单归纳如下几个
 
1.通过OPTIMIZER_USE_SQL_PLAN_BASELINE来控制Oracle是否使用基线，默认值为TRUE，即会自动使用基线。
2.11g中默认是不会自动创建基线
3.与OUTLINE和SQL Profile不同，基线中不存在分类的概念
4.与OUTLINE和SQL Profile不同，每个SQL语句可以有多个基线。Oracle根据制定的规则来判断具体是否哪个基线
5.基线针对RAC中所有的实例都生效
6.基线有两个表示，一个为sql_handle，可以理解为表示语句文本的唯一标识，一个为sql_plan_name可以理解为执行计划的唯一标识
7.不能像sql profile一样通过force_matching属性将字面值不一样的SQL语句使用一个基线应用多个语句。
 
三、创建基线的几种方式
 
1、自动捕获基线，通过将optimizer_cature_sql_plan_baselines设置为true，优化器为重复执行两次以上的SQL语句生成并保存基线（可以系统级或会话级修改）
 
2、从SQL调优集合中加载，通过使用包dbms_spm.load_plans_from_sqlset来从SQL调优集合中加载基线
 

DECLARE
 
  l_plans_loaded  PLS_INTEGER;
 
BEGIN
 
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
 
    sqlset_name => 'my_sqlset');
 
END;
 
/
 
3、从库缓存中加载，通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线
 

DECLARE
 
  l_plans_loaded  PLS_INTEGER;
 
BEGIN
 
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '1fkh93md0802n',plan_hash_value=>null);
 
END;
 
/
 
备注：可以有多种方式加载，例如可以根据sql文本进行模糊匹配、SQL语句解析的用户名等等方式，具体见文档
 

 

四、基线的几种状态
 
一个SQL语句对应的基线，我将它们归纳为三种状态
 
1.accepted（可接受），只有这种状态的基线，优化器才会考虑此基线中的执行计划
2.no-accepted（不可接受），这种状态的基线，优化器在SQL语句解析期间不会考虑。这种状态的基线必须通过演化和验证通过后，转变为accepted状态后，才会被优化器考虑使用
3.fixed为yes（固定），这种状态的基线固有最高优先级！比其他两类基线都要优先考虑
 
五、查看基线
 
1、基本视图：dba_sql_plan_baselines、dba_sql_management_config
 
2、底层视图：sqlobj$data 、 sqlobj$  （保存具体的hint），，如下查看基线中保存的执行计划语句：
 

select
 
extractvalue(value(d), '/hint') as outline_hints
 
from
 
xmltable('/outline_data/hint'
 
passing (
 
select
 
xmltype(comp_data) as xmlval
 
from
 
sqlobj$data sod, sqlobj$ so
 
where so.signature = sod.signature
 
and so.plan_id = sod.plan_id
 
and comp_data is not null
 
and name like '&baseline_plan_name'
 
)
 
) d;
 
3、通过函数来查看基线的详细信息：
 
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));