SPM手动绑定执行计划一例

--从v$sql 视图查询 
select sql_id,plan_hash_value,sql_text,parse_calls,executions 
from v$sql 
where sql_text like 'select /*+ no_index(t1 idx_01)*%';
--或sqlplus中，执行完sql后执行
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));  

declare
tmp number;
begin
tmp := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'g3auf3vcmtr6z',plan_hash_value => 3910739905,enabled => 'NO'); 
end;
/

select sql_handle,plan_name,origin,enabled,accepted,sql_text 
from dba_sql_plan_baselines 
where sql_text like 'select /*+ no_index(t1 idx_01)*/%';

执行多次如下SQL
select /*+ index(t1 idx_01)*/ from t1 where object_id=5;
获取SQL B的sql id 和plan_hash value，同上如下两种方式都可以
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
select sql_id,plan_hash_value,sql_text,parse_calls,executions 
from v$sql 
where sql_text like 'select /*+ index(t1 idx_01)*%';

declare
tmp number;
begin
tmp := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'fsfyjhcrv6kwb', -- new sql_id ，sql B
plan_hash_value => 34099177, --new plan_hash_value, sql B
sql_handle => 'SQL_4fd8b0b98686fd73' --原sql的sql_handle, sql A
);
end;
/
或：
exec :tmp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fsfyjhcrv6kwb',plan_hash_value=>34099177,sql_handle=>'SQL_4fd8b0b98686fd73');

--查看原SQL handle的执行计划
select sql_handle,plan_name,origin,enabled,accepted,sql_text 
from dba_sql_plan_baselines 
where sql_handle='SQL_4fd8b0b98686fd73';
--查看baseline sql_handle执行计划内容
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_4fd8b0b98686fd73',plan_name=>'SQL_PLAN_4zq5hr638dzbmd147f332'));
--删除 
exec :tmp := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_4fd8b0b98686fd73',plan_name=>'SQL_PLAN_82y5y7jctbuxrb2fbea4c');