SQL BASELINE修改固定执行计划
上一篇 /下一篇 2011-06-10 12:50:51/ 个人分类：ORACLE DATABASE
查看( 273 ) / 评论( 1 ) http://www.itpub.net/thread-1445125-1-1.htmlhttp://www.itpub.net/viewthread.php?tid=1445246
打算把固定执行计划的方法做个整理和比较。上面的链接是SQLPROFILE，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
1）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------------- --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
2）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
----------------------------------------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_TEXT CLOB IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
3）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
----------------------------------------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_HANDLE VARCHAR2 IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
DBMS_SPM包里有三个同名的LOAD_PLANS_FROM_CURSOR_CACHE函数，作用各不同。第一个函数用于直接对特定sql_id、plan_hash_value的共享池对象创建baseline。一般用于当前sql执行计划已经是正确的，只是为了通过baseline来进一步固定。也可以用于sql对应多个执行计划，只有一个执行计划是需要通过baseline来稳固的情况。这种情况可以根据plan_hash_value来确定期望的执行计划。
第二个函数一般用于修正执行计划，当然也能实现第一个函数的功能。修正执行计划的情况，sql_id,plan_hash_value要是添加过hint的sql的，不是原始sql的。sql_text是原始sql的。这个函数是我建议在修正执行计划的时候使用的，不推荐使用后面将要介绍的第三种。
第三个函数一般用于修正执行计划，也能够实现第一个函数的功能。修正执行计划的情况，sql_id,plan_hash_value要是添加过hint的sql的，不是原始sql的。SQL_HANDLE是依据原始sql创建的baseline的SQL_HANDLE。
上面三个函数的FIXED，ENABLED统一都设置成YES.FIXED为YES,代表我们创建的baseline禁止演化，演化的意思是，当ORACLE发现一个比目前执行计划更高效的执行计划时，自动创建一个不可接受状态的baseline.ENABLED的意思是，让baseline立即起效。
我们以如下查询为例。object_id列上存在索引。查询默认的执行计划走了object_id列上的索引。
sql文本 |
select count(*) from wxh_tbd where object_id=:a |
sql_id |
85f05qy1aq0dr |
plan_hash_value |
1501268522 |
我们可能对于这个查询计划的固化有两种需求：
1）想继续用走索引的执行计划，为确保执行计划不走错，通过baseline来固化执行计划。步骤如下：
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id=> ' bmrc3akp0v4uh ',
plan_hash_value=> 1501268522,
enabled => 'YES');
end;
/
2)不想用走索引的执行计划，想让执行计划走全表扫描。可以通过如下方式操作：
步骤一：通过添加HINT，构造出需要的执行计划。需要注意的是，这一步的意义是需要在共享池里产生出正确的执行计划，后面需要跟它做关联，需要确保这个执行计划不要被刷新出共享池。否则第二步的关联会无效。
select /*+ full(wxh_tbd)*/count(*) from wxh_tbd where object_id=1;
查询v$sql获得这个sql的sql_id和plan_hash_value
sql文本 |
select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=:a |
sql_id |
bmrc3akp0v4uh |
plan_hash_value |
853361775 |
步骤二：依据原始sql文本与正确的执行计划做关联。关联前最好再执行一下，步骤一里添加过HINT的sql语句，以免执行计划已经被刷出共享池。
declare
m_clob clob;
begin
select sql_fulltext
into m_clob
from v$sql
where sql_id = '9jnx2cjukjtu7'--------------原始sql的 sql_id
and child_number = 0;-----------------为了让sql只返回一行，也可以rownum=1代替
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(
sql_id => 'bmrc3akp0v4uh',------------HINTSQL_ID
plan_hash_value => 853361775,-------------------HINTPLAN_HASH_VALUE
sql_text => m_clob,-------------------------原始SQL文本
fixed => 'YES', ---------------------禁止演化baseline
enabled => 'YES'));
end;
/
1. 验证方案
另开一个SESSION，确定已经用到了baseline。
explain plan for select count(*) from wxh_tbd whereobject_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
执行计划的note部分显示已经用到了baseline，执行计划也由索引改为了全表扫描。