SPM-AUTO

最新推荐文章于 2024-08-15 11:43:27 发布

原创最新推荐文章于 2024-08-15 11:43:27 发布 · 655 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SPM

ORACLE--性能优化专栏收录该内容

20 篇文章

订阅专栏

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能够主动稳定执行计划，又保留继续使用新执行计划的机会，并且我们很容易启用新的执行计划。