MySQL中xplan_使用SPM来稳定执行计划

SQL Profile是一个稳定执行计划的的手段，但是这实际上只一个被动的技术手段，应用在那些执行计划发生了不好的变更的SQL上，即便在我们创建SQL Profile解决了目标SQL执行计划变更的问题，依然不能够保证系统后续执行得SQl的执行计划就不再发生不好的变更。这种不确定性会给Oracle升级带来一系列的麻烦，因为不清楚升级之后原来系统之中哪些SQL的执行计划可能发生变化。因此有了SPM(SQL PLAN MANAGEMENT)这个工具，可以说SPM的推出彻底解决了执行计划的稳定性的问题，它既能够主动的稳定执行计划，又能保留继续使用新的执行效率可能更高的执行计划的机会。

下面我们来查两个参数

SQL> show parameter sql_plan

NAME                TYPE    VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines boolean    FALSE

optimizer_use_sql_plan_baselines boolean    TRUE

参数optimizer_capture_sql_plan_baselines 用于控制是否开启自动捕获SQL Plan Baseline,其默认的方式为false，表示在默认的情况下，Oracle并不会自动捕获SQL Plan Baseline.如果设置为true。oracle会在上面参数影响范围内所有重复执行的SQL自动捕获去SQL Plan Baseline。

参数optimizer_use_sql_plan_baselines boolean  用于控制是否启用SQL Plan Baseline 其默认值为true，表示在默认的情况下，oracle在生成执行计划的时候就会启用SPM，使用已有的SQL Plan Baseline。

在当前会话，禁掉SPM，并同时开启捕获SQL Plan Baseline；

SQL> alter session set optimizer_capture_sql_plan_baselines=true ;

Session altered.

SQL> alter session set optimizer_use_sql_plan_baselines =false;

Session altered.

创建测试表

SQL> create table t2 as select * from dba_objects;

Table created.

创建索引

SQL> create index idx_t2 on t2(object_id);

Index created.

收集统计信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> select object_id,object_name from t2 where object_id between 103 and 108;

OBJECT_ID

----------

OBJECT_NAME

--------------------------------------------------------------------------------

103

MIGRATE$

104

DEPENDENCY$

105

ACCESS$

OBJECT_ID

----------

OBJECT_NAME

--------------------------------------------------------------------------------

106

I_DEPENDENCY1

107

I_DEPENDENCY2

108

I_ACCESS1

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 8vtdn0kgytfxr, child number 0

-------------------------------------

select object_id,object_name from t2 where object_id between 103 and 108

Plan hash value: 2008370210

--------------------------------------------------------------------------------

| Id  | Operation    | Name  | Rows  | Bytes | Cost (%CPU)| Time

|

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

|  0 | SELECT STATEMENT    |    |    |    |  3 (100)|

|

|  1 |  TABLE ACCESS BY INDEX ROWID| T2    |  7 | 210 |  3  (0)| 00:0

0:01 |

|*  2 |  INDEX RANGE SCAN    | IDX_T2 |  7 |    |  2  (0)| 00:0

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

0:01 |

--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1 / T2@SEL$1

2 - SEL$1 / T2@SEL$1

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]

2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

45 rows selected.

我们可以发现，我们现在走的是索引，对索引IDX_T2的索引范围扫描，因为只执行过一次，所以不会自动捕获其SQL Plan Baseline

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

no rows selected

SQL> select object_id,object_name from t2 where object_id between 103 and 108;

OBJECT_ID

----------

OBJECT_NAME

--------------------------------------------------------------------------------

103

MIGRATE$

104

DEPENDENCY$

105

ACCESS$

OBJECT_ID

----------

OBJECT_NAME

--------------------------------------------------------------------------------

106

I_DEPENDENCY1

107

I_DEPENDENCY2

108

I_ACCESS1

6 row