固定执行计划-使用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 FALSESYS@lunardb>
当前SPM中没有内容：
LUNAR@lunardb>selectsignature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines; no rows selected Elapsed: 00:00:00.00LUNAR@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 forsql_id: bjgduva68mbqmold 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/SQLprocedure successfully completed. Elapsed: 00:00:00.21LUNAR@lunardb>LUNAR@lunardb>selectsignature,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.01LUNAR@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 hashvalue: 3241900148 Elapsed: 00:00:00.02LUNAR@lunardb>LUNAR@lunardb>selectSQL_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.01LUNAR@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 forsql_id: bjgduva68mbqmEnter value forplan_hash_value: 1172089107Enter value forsql_handle: SQL_0926b6a1f69f6f5c PL/SQLprocedure successfully completed. Elapsed: 00:00:00.02LUNAR@lunardb>selectsignature,sql_handle,plan_name,origin,enabled,accepted,autopurge 2 from dba_sql_plan_baselines where CREATED>sysdate-1/48order 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.00LUNAR@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_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.4')DB_VERSION('11.2.0.4')ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX(@"SEL$1""LUNARTEST1"@"SEL$1"("LUNARTEST1"."N")) 6 rows selected. Elapsed: 00:00:00.08LUNAR@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_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.4')DB_VERSION('11.2.0.4')ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX(@"SEL$1""LUNARTEST1"@"SEL$1"("LUNARTEST1"."N"))END_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.4')DB_VERSION('11.2.0.4')ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX(@"SEL$1""LUNARTEST1"@"SEL$1"("LUNARTEST1"."N")) 14 rows selected. Elapsed: 00:00:00.10LUNAR@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=112-JAN-16 11.24.18.000000 AM 12-JAN-16 11.40.52.000000 AMcoe bjgduva68mbqm 3241900148 659415202199990108 9900816299026594015MANUAL ENABLED NO Elapsed: 00:00:00.01LUNAR@lunardb>LUNAR@lunardb>execdbms_sqltune.drop_sql_profile('coe_bjgduva68mbqm_3241900148'); PL/SQLprocedure successfully completed. Elapsed: 00:00:00.01LUNAR@lunardb>SELECT * FROM DBA_SQL_PROFILES; no rows selected Elapsed: 00:00:00.00LUNAR@lunardb>
再次验证SPM的执行计划：
LUNAR@lunardb>select* from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('&sql_handle','&PLAN_NAME'));Enter value forsql_handle: SQL_0926b6a1f69f6f5cEnter value forplan_name: SQL_PLAN_0k9pqn7v9yvuw02b73393old 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_0926b6a1f69f6f5cSQL text: select/*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1——————————————————————————– ——————————————————————————–Plan name: SQL_PLAN_0k9pqn7v9yvuw02b73393 Plan id: 45560723Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD——————————————————————————– Plan hashvalue: 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.16LUNAR@lunardb>
执行SQL，发现SPM可以固定执行计划，使用了我们期待的：
LUNAR@lunardb>setautotrace traceo exp statLUNAR@lunardb>select/*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1; Elapsed: 00:00:00.13 Execution Plan———————————————————-Plan hashvalue: 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 forthis 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/fromclient 0 sorts (memory) 0 sorts (disk) 1 rows processed LUNAR@lunardb>
这里看到已经使用了SPM中的SQL Profile：SQL_PLAN_0k9pqn7v9yvuw02b73393 总结： 这里已经使用了我们的SPM(SQL_PLAN_0k9pqn7v9yvuw02b73393)固定了执行计划，sql使用了索引 说明SPM绑定执行计划的方式比hint的优先级高
删除固定执行计划的方法BEGINdeclare xx PLS_INTEGER; BEGIN xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_PLAN_3d3v2839vkqqqa3c44420',plan_name=>null); END; /