Oracle - SPM固定执行计划(二)

原创

wx57edf9829d3c7 2022-01-13 10:50:14 ©著作权

文章标签 oracle sql 执行计划基线 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者wx57edf9829d3c7的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、前言

前面文章给大家介绍了当一条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的执行计划已经改变了。