Oracle SPM（SQL Plan Management）介绍及演示SQL

Oracle优化器辅助手段的发展

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

优化器可能选择到很差的Plan。

RBO->CBO 由规则序列生成执行计划，向着智能灵活化发展，根据数据对象的统计信息进行执行计划生成，根据cost选择最优。

但是CBO依赖统计信息，统计量又不能和数据表数据完全同步，当统计信息与实际数据差距大就会导致错误执行计划生成。

(1)没有定期收集统计量的方案，使得统计量过旧；

(2)当系统升级移植，版本不一致使得导入的对应统计量错误；

(3)在夜间刚刚收集完统计量之后调用大作业，导入海量数据直接改变了原有的数据分布情况。第二天直接生成错误的执行计划。

Oracle SPM 主要组件

• Plan Capture 捕获

存储SQL对应Plan的相关信息。这些信息可以唯一确定一个plan。

• Plan selection 选择

在系统的运行时，Oracle要保证每次执行SQL的执行计划都是使用SQL Baseline中的确定执行计划。同时，跟踪所有该statement执行中生成的新执行计划，作为Plan Histroy信息保存下来。

• Plan evolution 进化

添加新的plan到SQL baselines中，自动或手动。

通过使用baseline，减少性能回退。可以为新应用生成baselines，逐步演化更好的plan。

Plan Capture

自动捕捉 Automatic Initial Plan Capture

1. 首先把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置成TRUE

2. 从这个时刻开始，所有执行两次以上的SQL语句会被观测，执行计划会进入Plan History。

3. 生成的第一个执行计划被标记为ENABLED并且是ACCEPTED，后续的执行计划会被标记为ENABLED但不是ACCEPTED。

4. 这时把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置会FALSE，新的语句将不会创建Baseline。

5. 需要注意的是，即使关闭了自动捕捉，针对存在baseline的SQL，仍旧会有新的PLAN生成，新的Plan仍会进入Plan History，标记为ENABLED但不是ACCEPTED。

手动导入 Manual Plan Capture

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

(这些导入的baseline都会被自动标记为ACCEPTED)

Plan Selection

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

过程：

• 首先，无论是否存在baseline，oracle都会正常进行硬解析或者软解析，为SQL生成一个执行计划。 由于ACS和bind peeking的作用，存在baseline的SQL有可能在这时生成一个不同于baseline的执行计划。

• 如果baseline不存在，就按生成的计划执行。如果baseline存在，那么要查看history里是否有这个计划，如果没有，就将这个计划插入，并标记为ENABLED,NON-ACCEPTED。

• 在baseline中查看是否有FIXED的计划存在，如果存在，执行FIXED的计划，如果存在多个FIXED的计划，根据统计信息重新计算cost，选择cost小的那个。

• 如果FIXED的计划不存在，就选择ACCEPTED的计划执行。 如果存在多个ACCEPTED的计划，根据统计信息重新计算cost，选择cost小的那个。

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

Plan Evolution

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

演示

Table Prepare

create table yz as select rownum as c1, trunc(dbms_random.value(0, 1000)) as c2 from dual connect by level <= 5000;

Automatic Initial Plan Capture

show parameter capture_sql_plan;

1、alter session set optimizer_capture_sql_plan_baselines=TRUE;  （or alter system set optimizer_capture_sql_plan_baselines=TRUE;）
打开自动capture sql plan
2、select * from yz where c1= ？; (改一下值) select * from yz where c1 = 1;
执行SQL语句
3、select signature, sql_handle, sql_text,PLAN_NAME,ORIGIN,version, ACCEPTED from dba_sql_plan_baselines where sql_text like ‘select * from yz where c1 = ?’;
查看dba_sql_plan_baselines
 4、select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SYS_SQL_c748bdb0d9540dcb', plan_name => 'SQL_PLAN_cfk5xq3cp83fb97bbe3d0'));
查看baseline中的plan。

Manual Plan Capture

从library cache拿
1、alter session set optimizer_capture_sql_plan_baselines=false;
先关掉自动捕获。
2、select * from yz where c2 = ?;
执行一条语句
3、select sql_handle, sql_text,PLAN_NAME,ORIGIN,version, ACCEPTED from dba_sql_plan_baselines where sql_text like 'select * from yz%';
可以看到dba_sql_plan_baselines中不存在
4、  select sql_text, sql_id, plan_hash_value from v$sql where sql_text like 'select * from yz where c2 = ?';

找到sql_id和plan_hash_value

5、SQL>  var i number;
SQL> exec :i := dbms_spm.load_plans_from_cursor_cache (sql_id=>'bg9cppxy2a7fx', plan_hash_value=>'1357081020');
将cursor cache中的plan加入到spm中

6、select sql_handle, sql_text,PLAN_NAME,ORIGIN,version, ACCEPTED from dba_sql_plan_baselines where sql_text like 'select * from yz%';
新的plan 加入baselines中

Plan Selection
1、select name,ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE  from v$parameter where name like 'optimizer_use_sql_plan%';
查看是否使用baseline
2、select * from yz where c1 = ?;
执行已经加入baseline的sql.

3、select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'advanced'));
或者
 select sql_id, sql_text, PLAN_HASH_VALUE, executions, child_number from v$sql where sql_text like '';
select * from table(dbms_xplan.display_cursor('sql_id',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

4、select sql_handle, sql_text,PLAN_NAME, ACCEPTED, enabled, executions from dba_sql_plan_baselines where sql_text like 'select * from yz%';
查看executions是不是显示被执行过

Plan Evolution
create index yz_c1 on yz(c1);
select * from yz where c1 = ?;
select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'advanced'));
自动
select DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => NULL,  plan_name  => NULL, verify => 'YES',  commit => 'YES' ) from dual;
这里由两个标记控制：
o Verify 
  + YES (只有性能更好的计划才会被演化)
  + NO (演化所有的计划)
o Commit
  + YES (直接演化)
  + NO (只生成报告)
select sql_handle, sql_text,PLAN_NAME, ACCEPTED, enabled, executions from dba_sql_plan_baselines where sql_text like 'select * from yz%';

手动
DECLARE

 v_text  PLS_INTEGER;

BEGIN

 v_text  := DBMS_SPM.alter_sql_plan_baseline(sql_handle  => 'SYS_SQL_648e097c2d8e05a6',plan_name => 'SQL_PLAN_693h9ghqsw1d63158d001',

  attribute_name  => 'enabled',attribute_value => 'yes');

END;
/