SQL Profile 实际上只是一种亡羊补牢、被动的技术手段,应用在那些执行计划已经发生了不好的变更的SQL上。SPM是一种主动的稳定执行计划的手段,能够保证只有被验证过的执行计划才会被启动。
当启用SPM,每一个sql都会存在对应的SQL Plan Baseline,这个SQL Plan Baseline存储的就是该SQL的执行计划,如果一个sql有多个执行计划,那么该sql就会有多个SQL Plan Baseline,可以从DBA_SQL_PLAN_BASELINES中查看目标sql所有的SQL Plan Baseline。
只有DBA_SQL_PLAN_BASELINES列ENABLED、ACCEPTED值均为YES的sql执行计划才会被oracle启用,如果一个sql有超过1个以上sql plan baseline的列ENABLED、ACCEPTED值均为YES,oracle会选择成本值最小的那个执行计划。
产生SQL PLAN BASELINE有两种方法
1:自动捕获
2:手工生成、批量导入
SYS@fyl>show parameter baseline 数据库默认值
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
optimizer_capture_sql_plan_baselines控制是否开启自动捕获sql,可以在session和系统级别动态修改,当设置为TRUE后,则oracle会自动对上述参数影响范围内重复执行的sql自动捕获SQL Plan Baseline,并且对第一次捕获的SQL Plan Baseline的ENABLED、ACCEPTED值均为YES。随后如该sql执行计划发生变更,再次捕获的SQL Plan Baseline的ENABLED值为YES,ACCEPTED为NO,表示后续执行计划虽然捕获到了,但是oracle不会将其作为该sql的执行计划来执行,即此时oracle永远会沿用该sql第一次捕获的SQL Plan Baseline对应的执行计划(除非手动调整)。
optimizer_use_sql_plan_baselines控制是否启动SPM。
1准备测试环境
SYS@fyl>create table t1 as select * from dba_objects;
Table created.
SYS@fyl>create index idx_t1_id on t1(object_id);
Index created.
SYS@fyl>execute dbms_stats.gather_table_stats(ownname => 'sys',tabname => 't1' ,estimate_percent => 100 ,method_opt => 'for all columns size auto' ,cascade => true);
PL/SQL procedure successfully completed.
在当前session禁掉SPM,并开始sql_plan_baselines自动捕获
SYS@fyl>alter session set optimizer_use_sql_plan_baselines=false;
Session altered.
SYS@fyl>alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
SYS@fyl>show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean FALSE
执行如下sql
SYS@fyl>select object_id,object_name from t1 where object_id between 100 and 105;
SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID fvxkn08f3k74k, child number 0
-------------------------------------
select object_id,object_name from t1 where object_id between 100 and 105
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 7 | 210 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 7 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
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")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=105)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "T1".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
查看
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES;
no rows selected
再次执行sql并查看执行计划(INDEX RANGE SCAN)
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- -------------
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt174b15d2b AUTO-CAPTURE YES YES
select object_id,object_name from t1 where object_id between 100 and 105
由于sql重复执行,oracle已经捕获到sql_plan_baselines
我们将idx_t1_id的聚簇因子修改为2000W,让sql走全表扫描
SYS@fyl>exec dbms_stats.set_index_stats(ownname=>'sys',indname=>'idx_t1_id',clstfct=>20000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
SYS@fyl>select index_name,clustering_factor from dba_indexes where index_name='IDX_T1_ID';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T1_ID 20000000
SYS@fyl>select object_id,object_name from t1 where object_id between 100 and 105;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 210 | 256 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 7 | 210 | 256 (1)| 00:00:04 |
--------------------------------------------------------------------------
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- ---
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt174b15d2b AUTO-CAPTURE YES YES
select object_id,object_name from t1 where object_id between 100 and 105
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt1dbd90e8e AUTO-CAPTURE YES NO
select object_id,object_name from t1 where object_id between 100 and 105
从上述内容可以看出,现在该sql的执行计划已经为全表扫描,查看sql plan baseline 多一条
在session开始SPM,关闭自动捕获(恢复默认设置)
SYS@fyl>alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.
SYS@fyl>alter session set optimizer_use_sql_plan_baselines=true;
Session altered.
此时clustering_factor还是20000000
SYS@fyl>select index_name,clustering_factor from dba_indexes where index_name='IDX_T1_ID';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T1_ID 20000000
SYS@fyl>select object_id,object_name from t1 where object_id between 100 and 105;
OBJECT_ID OBJECT_NAME
---------- -----------------------------------------------------------------------------------
SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------
SQL_ID fvxkn08f3k74k, child number 2
-------------------------------------
select object_id,object_name from t1 where object_id between 100 and 105
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2073 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 7 | 210 | 2073 (1)| 00:00:25 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 7 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
- SQL plan baseline SQL_PLAN_c4rrg7w5snpt174b15d2b used for this statement
从上可以看到SPM开启的情况下,即使SQL产生新的执行计划,oracle依然只会依据该SQL的ENABLED、ACCEPTED值均为YES的SPB。
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%object_name%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt174b15d2b AUTO-CAPTURE YES YES select object_id,object_name from t1 where object_id between
100 and 105
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt1dbd90e8e AUTO-CAPTURE YES NO select object_id,object_name from t1 where object_id between
100 and 105
如果想启用目标SQL新的执行计划(即对t1表的全表扫描)该如何做呢?
var temp varchar2(1000);
exec :temp :=dbms_spm.alter_sql_plan_baseline( sql_handle=>'SYS_SQL_c25eef3f0b8a5721',plan_name=>'SQL_PLAN_c4rrg7w5snpt174b15d2b',attribute_name=>'ACCEPTED',attribute_value=>'NO');
但是在11gR2版本中会报错(11gR2中已经被ACCEPTED的SPB的值不能再被设为NO)
SYS@fyl>var temp varchar2(1000);
SYS@fyl>exec :temp :=dbms_spm.alter_sql_plan_baseline( sql_handle=>'SYS_SQL_c25eef3f0b8a5721',plan_name=>'SQL_PLAN_c4rrg7w5snpt174b15d2b',attribute_name=>'ACCEPTED',attribute_value=>'NO');
BEGIN :temp :=dbms_spm.alter_sql_plan_baseline( sql_handle=>'SYS_SQL_c25eef3f0b8a5721',plan_name=>'SQL_PLAN_c4rrg7w5snpt174b15d2b',attribute_name=>'ACCEPTED',attribute_value=>'NO'); END;
*
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
在11gR2中
11gR2 中依靠evolve_sql_plan_baseline/alter_sql_plan_baseline达到启用目标SQL新执行计划的目的。
先使用dbms_spm.evolve_sql_plan_baseline将目标sql新的执行计划(全表)对应的SQL_PLAN_c4rrg7w5snpt1dbd90e8e的SPB的ACCEPTED设为YES
SYS@fyl>exec :temp :=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_c25eef3f0b8a5721',plan_name=>'SQL_PLAN_c4rrg7w5snpt1dbd90e8e',verify=>'NO',commit=>'YES')
PL/SQL procedure successfully completed.
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%object_name%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt174b15d2b AUTO-CAPTURE YES YES select object_id,object_name from t1 where object_id between
100 and 105
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt1dbd90e8e AUTO-CAPTURE YES YES select object_id,object_name from t1 where object_id between
100 and 105
在使用alter_sql_plan_baseline将索引扫描的SPB的ENABLED设置为NO
SYS@fyl>exec :temp :=dbms_spm.alter_sql_plan_baseline( sql_handle=>'SYS_SQL_c25eef3f0b8a5721',plan_name=>'SQL_PLAN_c4rrg7w5snpt174b15d2b',attribute_name=>'ENABLED',attribute_value=>'NO');
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ------------------------------------
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt174b15d2b AUTO-CAPTURE NO YES select object_id,object_name from t1 where object_id between
100 and 105
SYS_SQL_c25eef3f0b8a5721 SQL_PLAN_c4rrg7w5snpt1dbd90e8e AUTO-CAPTURE YES YES select object_id,object_name from t1 where object_id between
100 and 105
再次执行目标SQL并查看执行计划
SYS@fyl>select object_id,object_name from t1 where object_id between 100 and 105;
SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID fvxkn08f3k74k, child number 1
-------------------------------------
select object_id,object_name from t1 where object_id between 100 and 105
Plan hash value: 3617692013
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 256 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 7 | 210 | 256 (1)| 00:00:04 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
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" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_ID"<=105 AND "OBJECT_ID">=100))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_c4rrg7w5snpt1dbd90e8e used for this statement
我们可以轻易的在sql的多个执行计划中切换,所以SPM能够主动稳定执行计划,又保留继续使用新执行计划的机会,并且我们很容易启用新的执行计划。