SPM手动绑定执行计划一例

原创

laosu

修改于 2021-10-15 10:10:00

73500

代码可运行

文章被收录于专栏：db小强db小强

运行总次数：0

代码可运行

背景

oracle 11.2.0.4

原sql A不走索引, select /*+ no_index(t1 idx_01)*/ from t1 where object_id=5;

手动生成走索引的SQL B的执行计划，select /*+ index(t1 idx_01)*/ from t1 where object_id=5;

将B的执行计划绑定到A，使SQL A走索引。

详细步骤

查看sql A 的SQL_ID和PLAN_HASH_VALUE

--从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'));

创建sql A的baseline，生成sql_handle

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

查看baseline,A的sql_handle

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 B的执行计划

执行多次如下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)*%';

将sql A的sql_handle与B的执行计划关联

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,并删除原执行计划

--查看原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');