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

转载

mob60475705f1df 2019-01-15 13:57:00

文章标签 sql 执行计划 sed v9 f5 文章分类 代码人生

固定执行计划-使用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 SQLNet to client` `523 bytes received via SQLNet from client` `2 SQL*Net roundtrips to``/from` `client` `0 sorts (memory)` `0 sorts (disk)` `1 rows processed` `LUNAR@lunardb>`