SQL BASELINE修改固定执行计划

最新推荐文章于 2022-01-27 18:56:46 发布

转载最新推荐文章于 2022-01-27 18:56:46 发布 · 92 阅读

本文详细介绍了如何通过SQL Baseline来修改Oracle数据库中特定SQL语句的执行计划，包括创建测试表、设置和修改Baseline的具体步骤。

SQL BASELINE修改固定执行计划

http://www.itpub.net/thread-1445125-1-1.html
http://www.itpub.net/viewthread.php?tid=1445246
打算把固定执行计划的方法做个整理和比较。上面的链接是SQL PROFILE，SQL OUTLINE的用法。这篇说下BASELINE的用法。
目的：让执行计划走上全表扫描

查询语句：select count(*) from wxh_tbd where object_id=:a
SQL_ID:85f05qy1aq0dr
PLAN_HASH_VALUE：1501268522

步骤一-------------------------创建测试表，根据DBA_OBJECTS创建，OBJECT_ID上有索引
Create table wxh_tbd as select * from dba_objects;
create index t_3 on wxh_tbd(object_id);

步骤二------------------------创建指定SQLID的BASELINE,后面要做修改，由于默认走的索引
declare
  l_pls number;
begin
  l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id       => '85f05qy1aq0dr',
                                             plan_hash_value => 1501268522,
                                             enabled       => 'NO');
end;
/

步骤三--------------------------想办法构造出执行计划为全表扫描的SQL_ID
var a number
exec :a :=1234
select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=:a;
select sql_id ,sql_text from v$sql where sql_text like '%wxh_tbd%';
查出SQL_ID为89143jku5hzcw，PLAN_HASH_VALUE为853361775

步骤四--------------------------确定原始执行计划的 sql_handle
select sql_handle, plan_name, origin, enabled, accepted,fixed,optimizer_cost,sql_text
from dba_sql_plan_baselines where sql_text like '%count(*) from wxh_tbd %'
order by last_modified;
SQL_HANDLE:SYS_SQL_ad9f0ff741832bd9
PLAN_NAME:SYS_SQL_PLAN_41832bd9d63f8aa9

步骤五------------------------与正确的执行计划做关联
declare l_pls number;
begin
  l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id       => '89143jku5hzcw', -- hinted_SQL_ID'
                                             plan_hash_value => 853361775, --hinted_plan_hash_value
                                             sql_handle    => 'SYS_SQL_ad9f0ff741832bd9' --sql_handle_for_original
                                             );
end;
/

步骤六--------------------------删除错误的执行计划
declare l_pls number;
begin
  l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SYS_SQL_ad9f0ff741832bd9', --sql_handle_for_original
                                       plan_name  => 'SYS_SQL_PLAN_41832bd9d63f8aa9  ' --sql_plan_name_for_original

                                       );
end;
/

步骤七----------------------确认是否使用到BASELINE
explain plan for  select count(*) from wxh_tbd where object_id=:a;
select * from table(dbms_xplan.display);
--------------------------------------
| Id  | Operation       | Name |
--------------------------------------
| 0 | SELECT STATEMENT |       |
| 1 |  SORT AGGREGATE |       |
|*  2 | TABLE ACCESS FULL| WXH_TBD |
--------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_41832bd9cca3d082" used for this statement

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25380220/viewspace-697571/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25380220/viewspace-697571/