oracle11g new feature SPM 有助于保持sql的语句特性，仅仅同意运行性能提高的运行计划。 它不同于stored outlines， spm在于稳定sql性能，而store outlines在于冻结sql运行计划 事列 1.启用optimizer_cature_sql_plan_baselines 捕获sql语句 SQL> alter session set optimizer_capture_sql_plan_baselines = true; Session altered. SQL> select * from objs where object_id = 2; no rows selected SQL> select * from objs where object_id = 2; no rows selected SQL> alter session set optimizer_capture_sql_plan_baselines = false; Session altered. 2.查询dba_sql_plan_baselines，确定sql的spm状态 SQL> select plan_name, sql_handle, enabled, accepted, fixed, 2 module, sql_text 3 from dba_sql_plan_baselines; PLAN_NAME SQL_HANDLE ENABLED ACCEPTED FIXED MODULE SQL_TEXT ------------------------------ -------------------- --------- --------- --------- -------------------- -------------------------------------------------- SQL_PLAN_4pzq3z6xcqkpwd0984253 SQL_4afec3f9bacb4abc YES YES NO SQL*Plus select * from objs where object_id = 2 3.添加索引改变环境再执行sql SQL> alter session set optimizer_capture_sql_plan_baselines = true; Session altered. SQL> select * from objs where object_id = 2; no rows selected SQL> select * from objs where object_id = 2; no rows selected SQL> alter session set optimizer_capture_sql_plan_baselines = false; Session altered. 4.查询dba_sql_plan_baselines SQL> select plan_name, sql_handle, enabled, accepted, fixed, module, sql_text from dba_sql_plan_baselines; PLAN_NAME SQL_HANDLE ENABLED ACCEPTED FIXED MODULE SQL_TEXT ------------------------------ -------------------- --------- --------- --------- -------------------- -------------------------------------------------- SQL_PLAN_4pzq3z6xcqkpwbe4c314c SQL_4afec3f9bacb4abc YES NO NO SQL*Plus select * from objs where object_id = 2 SQL_PLAN_4pzq3z6xcqkpwd0984253 SQL_4afec3f9bacb4abc YES YES NO SQL*Plus select * from objs where object_id = 2 SQL> 5.測试 SQL> SQL> set autotrace on SQL> select * from objs where object_id = 2; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 954894094 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 247 (1)| 00:00:03 | |* 1 | TABLE ACCESS FULL| OBJS | 1 | 98 | 247 (1)| 00:00:03 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=2) Note ----- - SQL plan baseline "SQL_PLAN_4pzq3z6xcqkpwd0984253" used for this statement Statistics ---------------------------------------------------------- 255 recursive calls 0 db block gets 1044 consistent gets 879 physical reads 0 redo size 1343 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 28 sorts (memory) 0 sorts (disk) 0 rows processed SQL> alter session set optimizer_use_sql_plan_baselines = false; Session altered. SQL> select * from objs where object_id = 2; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 613004408 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| OBJS | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OBJSID_OBJS | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=2) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 4 physical reads 0 redo size 1343 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
能够看到当启动optimizer_user_sql_plan_baselines时走索引应该是最优的却没有使用 plan_name accecpt 为 no 影响了走索引计划