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

一、前言

前面文章(https://www.cnblogs.com/ddzj01/p/11365541.html)给大家介绍了当一条sql有多个执行计划时,如何通过spm去绑定其中一条执行计划。本文将继续介绍,如何给一条sql注入一个新的执行计划,去替换原始的执行计划。文章地址https://www.yii666.com/article/756083.html

二、解决办法

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;
/文章来源地址:https://www.yii666.com/article/756083.html

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;
/文章来源地址https://www.yii666.com/article/756083.html

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) */%';网址:yii666.com<

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;
/网址:yii666.com

新开一个窗口,再运行原始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只是一个临时应急解决方案,数据库出现执行计划不正确,应该从源头查找原因,比如说统计信息,索引是否失效等等。

版权声明:本文内容来源于网络,版权归原作者所有,此博客不拥有其著作权,亦不承担相应法律责任。文本页已经标记具体来源原文地址,请点击原文查看来源网址,站内文章以及资源内容站长不承诺其正确性,如侵犯了您的权益,请联系站长如有侵权请联系站长,将立刻删除

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信图片_20190322181744_03.jpg

微信扫一扫打赏

请作者喝杯咖啡吧~

支付宝扫一扫领取红包,优惠每天领

二维码1

zhifubaohongbao.png

二维码2

zhifubaohongbao2.png