ORACLE SPM固定hint执行计划

1.创建实验表并创建索引

create table zhangyun.t2 as select * from dba_objects;
create index zhangyun.idx_2 on zhangyun.t2(owner);

2.使用实验表进行普通查询和使用hint全表扫描的查询

select * from zhangyun.t2 where owner='ZHANGYUN';
select /*+full(T2)*/* from zhangyun.t2 where owner='ZHANGYUN';

3.在v$sql视图中，查询到sql_id和plan_hash_value

SYS@PRODCDB >select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from zhangyun.t2 where %';

SQL_ID	      PLAN_HASH_VALUE
------------- ---------------
SQL_TEXT
--------------------------------------------------------------------------------
70ug5xw2r5b2q	   1306670842
select * from zhangyun.t2 where owner='ZHANGYUN'

6gq9620swtqcj	    903671040
select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from zha
ngyun.t2 where owner=%'

f1qyjarfvrs4u	   1513984157
select /*+full(T2)*/* from zhangyun.t2 where owner='ZHANGYUN'

SQL_ID	      PLAN_HASH_VALUE
------------- ---------------
SQL_TEXT
--------------------------------------------------------------------------------

g5rtq9gx2bxhu	    903671040
select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from zha
ngyun.t2 where %'

4.使用SPM进行绑定

DECLARE
  sql_clob clob;
  SPM_L NUMBER;
begin
  select sql_fulltext
    into sql_clob
    from v$sql
   where sql_id = '70ug5xw2r5b2q' and rownum=1;--普通查询（需要绑定hint的sql）
  SPM:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id          => 'f1qyjarfvrs4u',
                                                             plan_hash_value => 1513984157,
                                                             sql_text        => sql_clob,
                                                             fixed           => 'YES',
                                                             enabled         => 'YES');--绑定hint的sql语句的sql_id,plan_hash_value
END;
/

5.查询是否正常绑定

SYS@PRODCDB >select sql_text,sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;

SQL_TEXT
--------------------------------------------------------------------------------
SQL_HANDLE
--------------------------------------------------------------------------------
PLAN_NAME
--------------------------------------------------------------------------------
ACC FIX OPTIMIZER_COST
--- --- --------------
select * from zhangyun.t2 where owner='ZHANGYUN'
SQL_fd92b3d62c494c70
SQL_PLAN_gv4pmusq4km3hb860bcf2
YES YES 	   427

6.查询进行验证

SYS@PRODCDB >set autotrace traceonly
SYS@PRODCDB >select * from zhangyun.t2 where owner='ZHANGYUN';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     8 |   920 |   427   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2	 |     8 |   920 |   427   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='ZHANGYUN')

Note
-----
   - SQL plan baseline "SQL_PLAN_gv4pmusq4km3hb860bcf2" used for this statement


Statistics
----------------------------------------------------------
	 40  recursive calls
	 18  db block gets
       1543  consistent gets
       1527  physical reads
       3456  redo size
       2284  bytes sent via SQL*Net to client
	552  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  8  rows processed

7.删除baseline

declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_b97eecad1293bf1e',plan_name=>null);
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_85372e07e425b213',plan_name=>null);
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_115024ccba5e158c',plan_name=>null);
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_89090362ceee70d9',plan_name=>null);
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_fd92b3d62c494c70',plan_name=>null);
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_f60acf5349c84a6f',plan_name=>null);
END;
/