Oracle 数据库绑定执行计划:SPM

原创有问题吗？ 2022-05-31

5837

前提：绑定执行计划一定不能脱离业务的实际场景，否则将一直在执行计划走偏的路上。

优秀的执行计划在内存中，可以采用DBMS_SPM方法进行执行计划绑定

找出SQL_ID 对应的优秀执行计划和优秀执行计划对应的plan_hash_value；

select * from table(dbms_xplan.display_cursor('&sql_id','&cursor_child_no','outline'));

调用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 将第一步找出的SQL_ID 和 plan_hash_value 进行绑定；

DECLARE
  k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'&sql_id',
plan_hash_value=>'&plan',
fixed=>'YES',
enabled=>'YES');
end;
/

-- DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 部分参数解释
-- fixed： 参数默认值为'NO',当设置为NO时,该SQL的执行计划不固定是plan_hash_value对应的执行计划，当设置为YES时，该SQL的执行计划将永远采用该plan_hash_value 对应的执行计划。
-- enabled：参数默认值为'YES', 表示启用加载的计划以供优化器使用。

TIPS ：

在官方文档中，DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE绑定执行计划的写法共有4种，连接如下： https://docs.oracle.com/database/121/ARPLS/d_spm.htm#ARPLS68168

将缓存中该SQL_ID 对应不好的执行计划刷出内存，加快执行计划的生效；
```
select address,hash_value from v$sql_plan where sql_id='&sql_id';
exec sys.dbms_shared_pool.purge('$address,$hash_value','C');
```
特别注意：

a. 在业务繁忙的系统上，对应SQL正在执行的时候，游标是purge不出内存的; 在极端情况下，此时可能就需要升级的操作(比如：Kill 业务 Session后再执行purge操作， Flush shared pool)，使绑定的执行计划加速生效，但具有一定的风险，影响业务。

b. 也可以不purge 游标，让游标失效后，自动刷出内存。
综上：还是建议在绑定执行计划后，手动让执行计划加速生效

绑定成功通过dba_sql_plan_baselines 查看相关信息；

select * from dba_sql_plan_baselines where creator='&username' and sql_text like '&sql_text'; 

-- 参数解释：
-- autopurge: 若为YES， 该sql_plan_baseline 将在一段时候后被自动清除， 若为NO 会无限期保留。
-- enabled: 若为NO，Oracle 将禁用该计划，一个SQL计划必须同时标记为ENABLED和ACCEPTED，才会被优化器使用，否则优化器将忽略它。
-- fixed: 设置为YES，那个计划将是优化器唯一的选择，注意当一个新计划被添加到被标记为FIXED的SQL计划基库，该新计划不能被利用除非它申明为FIXED状态。

若采用上述方法绑定的执行计划，得出的enabled, ACCEPTED,fixed, autopruge 将都为YES；

修改AUTOPURGE为NO，关闭自动清理机制；

DECLARE
 l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle   => '&SQL_HANDLE',
plan_name    => '&PLAN_NAME',
attribute_name => 'AUTOPURGE',
attribute_value => 'NO');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;

结果：

运行一段时间后，发现该执行计划非最优执行计划，想更改，可以有如下方法：

采用fixed=>‘YES’ 的方法，在同一SQL_ID 上继续绑定优秀的执行计划。操作和第一步一样。

使该sql_plan_baseline 不生效即修改属性enable 值为NO。

DECLARE
 l_plans_altered  PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle      => '&SQL_HANDLE',
plan_name       => '&PLAN_NAME',
attribute_name  => 'ENABLED',
attribute_value => 'NO');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

结果：

删除该sql_plan_baseline。

DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle => '&SQL_HANDLE'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/