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
- 首先把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置成TRUE
- 从这个时刻开始，所有执行两次以上的SQL语句会被观测，执行计划会进入Plan History。
- 生成的第一个执行计划被标记为ENABLED并且是ACCEPTED，后续的执行计划会被标记为ENABLED但不是ACCEPTED。
- 这时把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置会FALSE，新的语句将不会创建Baseline。
- 需要注意的是，即使关闭了自动捕捉，针对存在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;
/