执行计划不稳定，出现偶发性的恶化现象，一直是困扰CBO应用和运维人员的一个重要问题。一直以来，Oracle一直试图采用外部固定的方法来固化应用程序SQL语句。从最早的Hint，到11g的SPM（SQL Plan Management）。这些方法都在不同的层面上帮助我们解决Oracle性能问题。
SPM是Oracle在11g推出的执行计划固定工具。相对于较早版本中的SQL Profile和Hint（outline），SPM无论是从功能上还是使用上，有很多优势。在笔者之前的系列中，专门对SPM进行过介绍。
SPM实际上是从CBO的层面上影响最终的执行计划决策。在SPM的工作循环中，CBO优化器、内存shared cursor机制和SPM管理模块相互协作，实现执行计划的固定。本文重点在于研究SPM机制启动的时候，三个模块的工作协调方式。
1、环境准备
我们选择Oracle 11gR2作为实验环境。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
SPM相关参数保持在默认状态下。
SQL> show parameter baseline;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
创建实验数据表T，构建索引。
SQL> create table t as select * from dba_objects where wner='SCOTT';
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
实验SQL语句。
select /*+ SPM DEMO*/* from t where wner='SCOTT';
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 936 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 12 | 936 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT')
13 rows selected
注意，此时因为数据表中数据都是owner=’scott’，所以走FTS是可以接受的。
2、自动捕获执行计划作为Baseline
SPM创建Baseline两种方式，手工添加Baseline或者自动收集捕获。我们实现选择自动捕获方法。
--Session Level启动自动捕获
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
会话已更改。
--执行两次
SQL> select /*+ SPM DEMO*/* from t where wner='SCOTT';
（结果集合略）
注意，当启动自动SPM收集的时候，相同的SQL只有重复执行两次以上，才会生成Baseline。
SQL> select sql_handle, plan_name, ENABLED, ACCEPTED, FIXED, AUTOPURGE,executions from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED AUTOPURGE EXECUTIONS
------------------------------ ------------------------------ ------- -------- ----- --------- ----------
SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0
我们看到，Oracle已经生成了一个初始Baseline，是通过自动捕获方法生成的。Enable和Accepted的状态都是Yes，说明是一个生效的Baseline。
此时，我们检查下shared pool中的状态信息。
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
3kn82wdfqh9j3 2 1
SQL> select sql_id, child_number, sql_plan_baseline from v$sql where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE
------------- ------------ ------------------------------
3kn82wdfqh9j3 0
注意，Oracle要求执行两次之后才会生成Baseline。所以，此时Library Cache中包括一个父游标和对应的一个子游标。并且这个游标不是对应执行计划的。
我们从shared pool中抽取这个语句的执行计划，查看信息。
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '3kn82wdfqh9j3',format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3kn82wdfqh9j3, child number 0
-------------------------------------
select /*+ SPM DEMO*/* from t where wner='SCOTT'
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T | 12 | 936 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
（篇幅原因，有省略……）
49 rows selected
注意，我们抽取的执行计划中，并没有标注baseline的说明。这就说明，当自动生成baseline的时候，原始的执行计划没有被变化。
3、重复执行SQL实验
此时，如果我们重复执行这个SQL语句，会如何呢？Oracle是否会使用生成的Baseline执行计划，还是执行相同的shared cursor。
--重复执行，结果省略
SQL> select /*+ SPM DEMO*/* from t where wner='SCOTT';
--SQL PLAN Baseline情况
SQL> select sql_handle, plan_name, ENABLED, ACCEPTED, FIXED, AUTOPURGE,executions from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED AUTOPURGE EXECUTIONS
------------------------------ ------------------------------ ------- -------- ----- --------- ----------
SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0
---shared pool情况
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
3kn82wdfqh9j3 3 2
SQL> select sql_id, child_number, sql_plan_baseline,executions from v$sql where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS
------------- ------------ ------------------------------ ----------
3kn82wdfqh9j3 0 2
3kn82wdfqh9j3 1 SQL_PLAN_6datc7mtkmzvc94ecae5c 1
从结果看，当我们再次执行这个SQL的时候，Oracle发现已经构建了Baseline，就会按照Plan Baseline去工作。此时如果shared cursor中存在非Baseline的执行计划子游标，Oracle会去创建一个新的子游标。
此时，我们查看这个共享游标的执行计划。
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '3kn82wdfqh9j3',format => 'advanced',cursor_child_no => 1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3kn82wdfqh9j3, child number 1
-------------------------------------
select /*+ SPM DEMO*/* from t where wner='SCOTT'
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T | 12 | 936 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
（篇幅原因，有省略……）
Note
-----
- SQL plan baseline SQL_PLAN_6datc7mtkmzvc94ecae5c used for this statement
53 rows selected
注意，标识说明，Baseline执行计划正在被处理。此时，可以先关闭收集参数。
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
会话已更改。
4、全新清空shared pool下的实验状态
如果我们此时清空shared pool，全新进行baseline的处理。
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
重新执行SQL，理论是可以和现有的SQL Baseline相匹配。
--执行一次
SQL> select /*+ SPM DEMO*/* from t where wner='SCOTT';
--SPM过程
SQL> select sql_handle, plan_name, ENABLED, ACCEPTED, FIXED, AUTOPURGE,executions from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED AUTOPURGE EXECUTIONS
------------------------------ ------------------------------ ------- -------- ----- --------- ----------
SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0
--Shared Pool中情况
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
3kn82wdfqh9j3 1 1
SQL> select sql_id, child_number, sql_plan_baseline,executions from v$sql where sql_text like 'select /*+ SPM DEMO*/* from t%';
SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS
------------- ------------ ------------------------------ ----------
3kn82wdfqh9j3 0 SQL_PLAN_6datc7mtkmzvc94ecae5c 1
但我们重新生成shared pool对象的时候，Oracle选择出了和baseline一致的结果。所以，我们可以得知：当最优执行计划和SPM中的baseline相吻合的时候，会在shared pool中形成对应的父子游标。子游标中标注着SQL Plan Baseline信息。
Shared Cursor情况如下：
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '3kn82wdfqh9j3',format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3kn82wdfqh9j3, child number 0
-------------------------------------
select /*+ SPM DEMO*/* from t where wner='SCOTT'
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T | 12 | 936 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,128],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"T"."SUBOBJECT_NAME"[VARCHAR2,30], "T"."OBJECT_ID"[NUMBER,22],
"T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19],
"T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7],
"T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],
"T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1],
"T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22],
"T"."EDITION_NAME"[VARCHAR2,30]
Note
-----
- SQL plan baseline SQL_PLAN_6datc7mtkmzvc94ecae5c used for this statement
53 rows selected
那么，这个决策过程是怎么样的呢？下篇我们计划使用10053进行CBO决策跟踪过程。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-755198/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-755198/
315
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
