Oracle 固定执行计划-使用SPM（Sql Plan Management）固定执行计划

固定执行计划-使用SPM（Sql Plan Management）固定执行计划

转载自：http://www.lunar2013.com/2016/01/固定执行计划-使用spm%EF%BC%88sql-plan-management%EF%BC%89固定执行计划.html

.
固定执行计划-使用SQL Tuning Advisor
固定执行计划-手工指定PLAN OUTLINE
固定执行计划-手工指定索引名称的方式
固定执行计划-使用coe_xfr_sql_profile固定执行计划
固定执行计划-使用SPM（Sql Plan Management）固定执行计划
.

在Oracle 11g前，我们可以借助存储大纲(Stored Outline)和SQL Profile来帮助我们固定某个SQL语句的执行计划。
11g中，Oracle 提供了SPM（Sql Plan Management）。
通过这个特性，可以考虑让Oracle自动去判断某个SQL的新的执行计划是否更加合理（成本更低），只有在新的执行计划比原来的执行计划更好才会被使用，从而保护了执行计划的稳定性和SQL语句的执行效率。
可以考虑手工捕获和自动捕获两种方式，这里我们采用手工捕获（11.2的缺省设置是非自动捕获）。
首先查看当前为禁止自动捕获的状态：

SYS@lunardb>show parameter optimizer_capture_sql_plan_baselines   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean     FALSE SYS@lunardb>

当前SPM中没有内容：

LUNAR@lunardb> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;   no rows selected   Elapsed: 00:00:00.00 LUNAR@lunardb>

手工加载一个SQL到SPM中：

LUNAR@lunardb> declare    2    l_plans_loaded  PLS_INTEGER;    3  begin    4    l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '&sql_id' );    5    DBMS_OUTPUT.put_line( 'Plans Loaded: ' || l_plans_loaded);    6  END;    7  / Enter value for sql_id: bjgduva68mbqm old   4:   l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '&sql_id' ); new   4:   l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'bjgduva68mbqm' ); Plans Loaded: 1   PL /SQL procedure successfully completed.   Elapsed: 00:00:00.21 LUNAR@lunardb> LUNAR@lunardb> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;           SIGNATURE SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC AUT ---------------- ------------------------------ ------------------------------ -------------- --- --- --- 6.5941520220E+17 SQL_0926b6a1f69f6f5c           SQL_PLAN_0k9pqn7v9yvuw02b73393 MANUAL-LOAD    YES YES YES   Elapsed: 00:00:00.01 LUNAR@lunardb>

由于上一个测试，我们已经有了正确的执行计划，即：

LUNAR@lunardb> select * from table(dbms_xplan.display_cursor(sql_id=> 'bjgduva68mbqm' )) where plan_table_output  like ( 'Plan hash value%' );   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3241900148   Elapsed: 00:00:00.02 LUNAR@lunardb> LUNAR@lunardb> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT    2  from DBA_SQL_PLAN_BASELINES    3  where ACCEPTED = 'YES'    4  order by LAST_MODIFIED;   SQL_HANDLE                     PLAN_NAME                      ENA ACC SQL_TEXT ------------------------------ ------------------------------ --- --- -------------------------------------------------------------------------------- SQL_0926b6a1f69f6f5c           SQL_PLAN_0k9pqn7v9yvuw02b73393 YES YES select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1   Elapsed: 00:00:00.01 LUNAR@lunardb>

下面，我们装载指定的执行计划：

LUNAR@lunardb>variable cnt number ; LUNAR@lunardb> exec :cnt :=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '&SQL_ID' ,PLAN_HASH_VALUE => &plan_hash_value, SQL_HANDLE => '&SQL_HANDLE' ) ; Enter value for sql_id: bjgduva68mbqm Enter value for plan_hash_value: 1172089107 Enter value for sql_handle: SQL_0926b6a1f69f6f5c   PL /SQL procedure successfully completed.   Elapsed: 00:00:00.02 LUNAR@lunardb> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge    2  from dba_sql_plan_baselines where CREATED>sysdate-1 /48 order by created;                 SIGNATURE SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC AUT ---------------------- ------------------------------ ------------------------------ -------------- --- --- ---      659415202199990108 SQL_0926b6a1f69f6f5c           SQL_PLAN_0k9pqn7v9yvuw02b73393 MANUAL-LOAD    YES YES YES   Elapsed: 00:00:00.00 LUNAR@lunardb>

确认该执行计划的OUTLINE：

LUNAR@lunardb>SELECT extractValue(value(h), '.' ) AS hint    2  FROM sys.sqlobj$data od, sys.sqlobj$ so,    3  table(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint' ))) h    4  WHERE so.name = 'SQL_PLAN_0k9pqn7v9yvuw02b73393'    5  AND so.signature = od.signature    6  AND so.category = od.category    7  AND so.obj_type = od.obj_type    8  AND so.plan_id = od.plan_id;   HINT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE( '11.2.0.4' ) DB_VERSION( '11.2.0.4' ) ALL_ROWS OUTLINE_LEAF(@ "SEL$1" ) INDEX(@ "SEL$1" "LUNARTEST1" @ "SEL$1" ( "LUNARTEST1" . "N" ))   6 rows selected.   Elapsed: 00:00:00.08 LUNAR@lunardb>

这里看到是我们需要的走索引的outline，详细的OUTLINE信息如下:

LUNAR@lunardb>SELECT extractValue(value(h), '.' ) AS hint    2  FROM sys.sqlobj$data od, sys.sqlobj$ so,    3  table(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint' ))) h    4  WHERE so.signature = '659415202199990108'    5  AND so.signature = od.signature    6  AND so.category = od.category    7  AND so.obj_type = od.obj_type    8  AND so.plan_id = od.plan_id;   HINT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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" ) INDEX(@ "SEL$1" "LUNARTEST1" @ "SEL$1" ( "LUNARTEST1" . "N" )) END_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" ) INDEX(@ "SEL$1" "LUNARTEST1" @ "SEL$1" ( "LUNARTEST1" . "N" ))   14 rows selected.   Elapsed: 00:00:00.10 LUNAR@lunardb>

现在我们删除profile以前用coe绑定的sql profile：

LUNAR@lunardb>SELECT * FROM DBA_SQL_PROFILES;   NAME                           CATEGORY                                    SIGNATURE SQL_TEXT ------------------------------ ------------------------------ ---------------------- -------------------------------------------------------------------------------- CREATED                                                                     LAST_MODIFIED --------------------------------------------------------------------------- --------------------------------------------------------------------------- DESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- TYPE    STATUS   FOR          TASK_ID TASK_EXEC_NAME                      TASK_OBJ_ID      TASK_FND_ID      TASK_REC_ID ------- -------- --- ---------------- ------------------------------ ---------------- ---------------- ---------------- coe_bjgduva68mbqm_3241900148   DEFAULT                            659415202199990108 select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1 12-JAN-16 11.24.18.000000 AM                                                12-JAN-16 11.40.52.000000 AM coe bjgduva68mbqm 3241900148 659415202199990108 9900816299026594015 MANUAL  ENABLED  NO     Elapsed: 00:00:00.01 LUNAR@lunardb> LUNAR@lunardb> exec dbms_sqltune.drop_sql_profile( 'coe_bjgduva68mbqm_3241900148' );   PL /SQL procedure successfully completed.   Elapsed: 00:00:00.01 LUNAR@lunardb>SELECT * FROM DBA_SQL_PROFILES;   no rows selected   Elapsed: 00:00:00.00 LUNAR@lunardb>

再次验证SPM的执行计划：

LUNAR@lunardb> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE( '&sql_handle' , '&PLAN_NAME' )); Enter value for sql_handle: SQL_0926b6a1f69f6f5c Enter value for plan_name: SQL_PLAN_0k9pqn7v9yvuw02b73393 old   1: select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE( '&sql_handle' , '&PLAN_NAME' )) new   1: select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE( 'SQL_0926b6a1f69f6f5c' , 'SQL_PLAN_0k9pqn7v9yvuw02b73393' ))   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -------------------------------------------------------------------------------- SQL handle: SQL_0926b6a1f69f6f5c SQL text: select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1            where n=1 --------------------------------------------------------------------------------   -------------------------------------------------------------------------------- Plan name: SQL_PLAN_0k9pqn7v9yvuw02b73393         Plan id : 45560723 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD --------------------------------------------------------------------------------   Plan hash value: 3241900148   ------------------------------------------------------------------------------------- | Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |                  |     1 |     4 |     1   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_LUNARTEST1_N |     1 |     4 |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------   Predicate Information (identified by operation id ): ---------------------------------------------------       1 - access( "N" =1)   25 rows selected.   Elapsed: 00:00:00.16 LUNAR@lunardb>

执行SQL，发现SPM可以固定执行计划，使用了我们期待的：

LUNAR@lunardb> set autotrace traceo exp stat LUNAR@lunardb> select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1;   Elapsed: 00:00:00.13   Execution Plan ---------------------------------------------------------- Plan hash value: 3241900148   ------------------------------------------------------------------------------------- | Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |                  |     1 |     4 |     1   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_LUNARTEST1_N |     1 |     4 |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------   Predicate Information (identified by operation id ): ---------------------------------------------------       1 - access( "N" =1)   Note -----     - SQL plan baseline "SQL_PLAN_0k9pqn7v9yvuw02b73393" used for this statement     Statistics ----------------------------------------------------------           59  recursive calls           52  db block gets           36  consistent gets            1  physical reads        15312  redo size          519  bytes sent via SQL*Net to client          523  bytes received via SQL*Net from client            2  SQL*Net roundtrips to /from client            0  sorts (memory)            0  sorts (disk)            1  rows processed   LUNAR@lunardb>

这里看到已经使用了SPM中的SQL Profile：SQL_PLAN_0k9pqn7v9yvuw02b73393
总结：
这里已经使用了我们的SPM(SQL_PLAN_0k9pqn7v9yvuw02b73393)固定了执行计划，sql使用了索引
说明SPM绑定执行计划的方式比hint的优先级高

转载于:https://www.cnblogs.com/xibuhaohao/p/10271503.html