Oracle - SPM固定执行计划(二)
一、前言
前面文章(https://www.cnblogs.com/ddzj01/p/11365541.html)给大家介绍了当一条sql有多个执行计划时，如何通过spm去绑定其中一条执行计划。本文将继续介绍，如何给一条sql注入一个新的执行计划，去替换原始的执行计划。
二、解决办法
1. 生成初始执行计划所对应的sql plan baseline
begin
:temp := dbms_spm.load_plans_from_cursor_cache(
sql_id => '原目标sql的sql_id',
plan_hash_value => 原目标sql的plan hash value);
end;
/
2. 查出该sql的sql_handle
select sql_handle, sql_text, plan_name, origin from dba_sql_plan_baselines where sql_text like '原目标sql的sql_text%';
3. 生成新的sql plan baseline
begin
:temp := dbms_spm.load_plans_from_cursor_cache(
sql_id => '加入合适hint后改写的sql的sql_id',
plan_hash_value => 加入合适hint后改写的sql的plan hash value,
sql_handle => '原目标sql在步骤(1)中所产生的sql_handle');
end;
/
4. 删除步骤(1)中所产生的sql plan baseline
begin
:temp := dbms_spm.drop_sql_plan_baseline(
sql_handle => '原目标sql在步骤(1)中的sql_handle',
plan_name => '原目标sql在步骤(1)中的plan_name');
end;
/
就这么写，肯定一脸懵逼，下面通过一个实验去解释。
三、做个实验
实验环境，使用scott账号，并给scott赋予dba权限（实际上scott只需要administer sql management object权限就可以使用spm）
创建表和索引，并收集统计信息
SQL> create table test2 as select * from dba_objects;
SQL> create index idx_test2 on test2(object_id) online;
SQL> begin
dbms_stats.gather_table_stats(ownname=>'SCOTT',
tabname=>'TEST2',
cascade=>true,
no_invalidate=>false);
end;
/
执行原始的sql
SQL> set autot trace
SQL> select * from test2 where object_id=20;
Execution Plan ---------------------------------------------------------- Plan hash value: 4047680367 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST2 | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
可以看到原始sql的执行计划为索引范围扫描
新开一个窗口，查原始sql的sql_id和plan_hash_value
SQL> col sql_id for a20
SQL> col sql_text for a40
SQL> select sql_id, plan_hash_value, sql_text from v$sqlarea where sql_text like 'select * from test2 where object_id=20%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT -------------------- --------------- ---------------------------------------- 4tm6j886yvzj3 4047680367 select * from test2 where object_id=20
将原始sql语句加入基线
SQL> var temp number;
SQL> begin
:temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'4tm6j886yvzj3', plan_hash_value=>4047680367);
end;
/
查看原始sql的基线
SQL> col sql_handle for a24
SQL> col sql_text for a40
SQL> col plan_name for a35
SQL> select sql_handle, sql_text, plan_name, origin from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ------------------------ ---------------------------------------- ----------------------------------- -------------- SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k60b1ef84 MANUAL-LOAD
对原始sql加hint，执行新的sql
SQL> select /*+ full(test2) */ * from test2 where object_id=20;
获得新sql语句的sql_id和plan_hash_value
SQL> select sql_id, plan_hash_value, sql_text from v$sqlarea where sql_text like 'select /*+ full(test2) */%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT -------------------- --------------- ---------------------------------------- 11cptg7m2vcwr 300966803 select /*+ full(test2) */ * from test2 w here object_id=20
将新的sql_id和plan_hash_value加入到原始sql的基线中
SQL> var temp number;
SQL> begin
:temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'11cptg7m2vcwr', plan_hash_value=>300966803, sql_handle =>'SQL_20df29fdb3e8ac52');
end;
/
查看原始sql的基线
SQL> select sql_handle, sql_text, plan_name, origin from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ------------------------ ---------------------------------------- ----------------------------------- -------------- SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k60b1ef84 MANUAL-LOAD SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k99963deb MANUAL-LOAD
可以看到新的执行计划plan_name为SQL_PLAN_21rt9zqtyjb2k99963deb已经加到原始sql的基线中了
删除旧的sql_plan
SQL> var temp number;
SQL> begin
:temp := dbms_spm.drop_sql_plan_baseline(sql_handle =>'SQL_20df29fdb3e8ac52', plan_name=>'SQL_PLAN_21rt9zqtyjb2k60b1ef84');
end;
/
新开一个窗口，再运行原始sql语句
SQL> set autot trace
SQL> select * from test2 where object_id=20;
Execution Plan ---------------------------------------------------------- Plan hash value: 300966803 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST2 | 1 | 98 | 347 (1)| 00:00:05 | --------------------------------------------------------------------------- Note ----- - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k99963deb" used for this statement
可以看到原始sql的执行计划已经改变了。
四、总结
spm只是一个临时应急解决方案，数据库出现执行计划不正确，应该从源头查找原因，比如说统计信息，索引是否失效等等。
