spm手工创建sql_plan_baseline

本文参考崔华老师的书

1)创建一个测试表
create table t2 as select * from dba_objects;
create index idx_t2 on t2(object_id);
2)做一次查询
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;
查一下执行计划
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  76td2qkxcrn9v, child number 0
-------------------------------------
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where
object_id=4

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   345 (100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T2   |    14 |  1106 |   345   (1)| 00:00:05 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

3)查看一下是否有sql_plan_baseline
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where
sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';
no rows selected
我的环境
optimizer_capture_sql_plan_baselines boolean 参数值是FALSE
4)生成1个sql_plan_baseline
var temp number
exec  :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '76td2qkxcrn9v',plan_hash_value=>1513984157);
5)再查询一下
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where
sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';
有结果了
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_75b06ae056223f5f           SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD    YES
YES
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4
6)改写原来的sql,强制走索引hint
select /*+ index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
SQL_ID  795jg57h9tryx, child number 0
-------------------------------------
select /*+ index(t2 idx_t2) */object_name,object_id from t2 where
object_id=4

Plan hash value: 2008370210

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|
     |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    79 |     2   (0)| 00:0
0:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------

这个走索引明显效率高
7)再查询一下是否有新的sql_plan_baseline (应该没有的)
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where
  2  sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_75b06ae056223f5f           SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD    YES
YES
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4
8)偷梁换柱
exec  :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '795jg57h9tryx',plan_hash_value=>2008370210,sql_handle =>'SQL_75b06ae056223f5f');

查询新的基线有两个了
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where
  2  sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_75b06ae056223f5f           SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD    YES
YES
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4

SQL_75b06ae056223f5f           SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD    YES
YES
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4
9)删掉旧的一个

 exec  :temp := dbms_spm.drop_sql_plan_baseline(sql_handle =>'SQL_75b06ae056223f5f',plan_name=>'SQL_PLAN_7bc3aw1b24guzb860bcf2');
查看一下只剩下改写的一个
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where
  2  sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_75b06ae056223f5f           SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD    YES
YES
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4

10)验证
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  76td2qkxcrn9v, child number 1
-------------------------------------
select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where
object_id=4

Plan hash value: 2008370210

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        |       |       |     7 (100)|
     |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |  1031 | 81449 |     7   (0)| 00:0
0:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |   412 |       |     1   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------

语句中是不走索引，但执行计划已经走了索引。