ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL BASELINE修改固定执行计划

SQL BASELINE修改固定执行计划

原创 Linux操作系统 作者:wei-xh 时间:2011-06-10 12:50:51 0 删除 编辑
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


Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

1FUNCTION 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

2FUNCTION 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

3FUNCTION 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_idplan_hash_value的共享池对象创建baseline。一般用于当前sql执行计划已经是正确的,只是为了通过baseline来进一步固定。也可以用于sql对应多个执行计划,只有一个执行计划是需要通过baseline来稳固的情况。这种情况可以根据plan_hash_value来确定期望的执行计划。

第二个函数一般用于修正执行计划,当然也能实现第一个函数的功能。修正执行计划的情况,sql_id,plan_hash_value要是添加过hintsql的,不是原始sql的。sql_text是原始sql的。这个函数是我建议在修正执行计划的时候使用的,不推荐使用后面将要介绍的第三种。

第三个函数一般用于修正执行计划,也能够实现第一个函数的功能。修正执行计划的情况,sql_id,plan_hash_value要是添加过hintsql的,不是原始sql的。SQL_HANDLE是依据原始sql创建的baselineSQL_HANDLE

上面三个函数的FIXEDENABLED统一都设置成YES.FIXEDYES,代表我们创建的baseline禁止演化,演化的意思是,当ORACLE发现一个比目前执行计划更高效的执行计划时,自动创建一个不可接受状态的baseline.ENABLED的意思是,让baseline立即起效。

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE我们以如下查询为例。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获得这个sqlsql_idplan_hash_value

sql文本

select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=:a

sql_id

bmrc3akp0v4uh

plan_hash_value

853361775

 

步骤二:依据原始sql文本与正确的执行计划做关联。关联前最好再执行一下,步骤一里添加过HINTsql语句,以免执行计划已经被刷出共享池。

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',------------HINT SQL_ID

plan_hash_value => 853361775,-------------------HINT PLAN_HASH_VALUE

sql_text        => m_clob,-------------------------原始SQL文本

fixed           => 'YES', ---------------------禁止演化baseline

 enabled         => 'YES'));

 

end;

 

/

1.      验证方案

另开一个SESSION,确定已经用到了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

 

执行计划的note部分显示已经用到了baseline ,执行计划也由索引改为了全表扫描。

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

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

最新文章