SPM-AUTO

来源：互联网发布：画树状图软件编辑：程序博客网时间：2024/05/17 23:30

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 baselineNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------optimizer_capture_sql_plan_baselines boolean     TRUEoptimizer_use_sql_plan_baselines     boolean     FALSE执行如下sqlSYS@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 105Plan 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$1Outline 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 YESselect 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                               20000000SYS@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 YESselect object_id,object_name from t1 where object_id between 100 and 105SYS_SQL_c25eef3f0b8a5721       SQL_PLAN_c4rrg7w5snpt1dbd90e8e AUTO-CAPTURE   YES NOselect 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还是20000000SYS@fyl>select index_name,clustering_factor from dba_indexes where index_name='IDX_T1_ID';INDEX_NAME                     CLUSTERING_FACTOR------------------------------ -----------------IDX_T1_ID                               20000000SYS@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 105Plan 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 105SYS_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 specifiedORA-06512: at "SYS.DBMS_SPM", line 2469ORA-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设为YESSYS@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 105SYS_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设置为NOSYS@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 105SYS_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 105Plan 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$1Outline 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能够主动稳定执行计划，又保留继续使用新执行计划的机会，并且我们很容易启用新的执行计划。

0 0