SPM-AUTO 喜欢ヅ旅行 2022-08-08 06:14 139阅读 0赞 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能够主动稳定执行计划，又保留继续使用新执行计划的机会，并且我们很容易启用新的执行计划。
还没有评论，来说两句吧...