稳定执行计划操作手册--oracle库

2017-01-06 07:56:55来源:CSDN作者:wish503人点击



稳定执行计划操作手册


一、 概述
本文档列举了不同版本间稳定执行计划的方法,给出了详细的操作步骤。考虑到可阅读性及可操作性,未做过多的原理介绍,需要使用者在操作前已经具备相关技术知识,以便操作起来得心应手。
Outline 的使用前提
Outline的功能是为了保存sql执行计划,以保证sql在db配置或者数据变化时每次执行时都使用该执行计划。Outline的实现是通过保存生成sql 执行计划的输入信息(包括hint, 初时化参数等)。
Outline使用需要注意的事项:
1) Outline的计划只有在outline内所有hint都有效的情况下才会被使用。如:索引没有创建的前提下,索引的hint是失效的,导致整个outline计划不会被使用。
2) 参数Cursor_sharing=force时不能使用outline. 当前环境cursor_sharing =exact
3) literial sql的共享程度不高,Outline针对绑定变量的sql较好。针对literial sql的情况,需要每条sql都生成outline。或是在cursor_sharing=similar 情况下生成。但在这种情况下,
    生成outline 的环境与生产环境应具有相同的cursor_sharing 参数值。
4) 创建outline需要有create any outline的权限。

二、 9i 使用方法
简述
这里描述的是在Oracle 数据库生产环境中由于未知的原因,使得单个sql出现严重的性能问题,在判断出是执行计划不正确引起的时,采用outlines 技术,从其它具有正确的执行计划的环境,
如测试库,开发库等获取正确的执行计划,并导入到生产库中的方法。
以下步骤可用SYS用户或者实名用户。

步骤一、 授权
可预先在各个数据库中以 sys 执行以下步骤,以便节省后续的时间:(这一步一般不需要处理)
-- 为 outln 用户授权
   grant create any outline to outln;
   grant drop any outline to outln;
   grant all on plan_table to outln;
   -- 创建 plan_table 并创建同义词
   @?/rdbms/admin/utlxplan.sql;
   create public synonym plan_table for plan_table;
   grant select ,insert, update, delete on plan_table to public;
  
步骤二、 获取执行计划
在生产库获得存在性能问题的SQL的 sql_hash_value ,并且获取当前执行计划。

步骤三、 获取SQL文本
从v$sqltext 中获得相应的sql语句片段,通过UE替换成完整SQL。
除最后一行外,其它行尾部的数字一定会是64,表示该行除该数字外有64个字符,最后一行的数字也为指示该行有多少字符。通过UE替换64^p为空,再将最后的字符数替换为分号即可。
set linesize 80
set head off
set feedback off
spool /tmp/sql1.txt
select sql_text || length(sql_text) sql_text from v$sqltext where hash_value=&hash_value
order by piece;
spool off

步骤四、 生成outline
1) 找到有好的执行计划其它环境,如COW库、测试库、开发库或其它同类型的生产库。
2) 如果没有能够找到好的执行计划,需要想办法在其它环境制造出好的执行计划,如临时删除一个不合适的索引,从而强制其使用期望的索引。
通过如下SQL生成outline,红色部分需要替换:
create outline <outline名字> for category special on
<步骤三获取的问题SQL文本>;

步骤五、 导出outline
使用如下命令导出outline,parfile内容如下,红色部分注意替换:
exp parfile=exp_outl.par
--exp_outl.par
userid="outln/outln"
tables=(outln.ol$,outln.ol$hints,outln.ol$nodes)
file=outln_20120627.dmp
query="WHERE ol_name in ('<outline名字>','<outline名字>','<outline名字>')"
STATISTICS=NONE

步骤六、 传输dmp文件
通过主机命令将dmp文件传输至目标库。

步骤七、 导入outline
在目标库导入outline,命令如下,红色部分需要替换:
imp outln/outln file=outln_20120627.dmp full=y ignore=y log=ol_imp.log

步骤八、 启用outline
导入outline后,执行如下命令启用outline
exec dbms_outln.update_signatures;
alter system set use_stored_outlines=special;

步骤九、 验证outline
在生产库kill还在使用原执行计划的session,以便其重新登录后使用新计划,检查如下确认outline生效:
01. 检查dba_outlines:
select name,
       to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,
       USED,
       SQL_TEXT
  from dba_outlines
 where name like 'KXN%';
02. 检查新进的SQL是否用上好的执行计划

三、 10g 使用方法
简述
10g做outline比9i简单,不需要用UE处理SQL文本,直接拿sql_hash_value和child_number即可,实际中可以使用SYS用户和实名用户,如果用10g的方法抓到SQL是错误的,可以使用9i的方法。

步骤一、 获取SQL信息
查出问题SQL的hash_value和child_number
select hash_value,child_number from v$sql where hash_value in('1030675442',
'1165145440',
'2506807260');

步骤二、 生成 outline
alter session set create_stored_outlines=true;
exec dbms_outln.create_outline(2452139749, 0, 'SPECIAL');

步骤三、 修改outline名称
上一步生成的outline是系统自动命名的,可将outline名称修改为有意义的名字。
alter outline SYS_OUTLINE_12062711343918532 rename to kxn_1030675442;

步骤四、 导出outline
使用如下parfile导出outline,注意替换红色部分命令如下:
exp parfile=exp_outl.par
--exp_outl.par
userid="outln/outln"
tables=(outln.ol$,outln.ol$hints,outln.ol$nodes)
file=outln_20150927.dmp
query="WHERE ol_name in ('<outline名字>','<outline名字>','<outline名字>')"
STATISTICS=NONE

步骤五、 传输dmp文件
通过主机命令将dmp文件传输至目标库。

步骤六、 导入outline
在目标库导入outline,命令如下,红色部分需要替换:
imp outln/outln file=outln_20150927.dmp full=y ignore=y log=ol_imp0927.log

步骤七、 启用outline
导入outline后,执行如下命令启用outline
exec dbms_outln.update_signatures;
alter system set use_stored_outlines=special;

步骤八、 验证outline
在生产库kill还在使用原执行计划的session,以便其重新登录后使用新计划,检查如下确认outline生效:
01. 检查dba_outlines:
select name,
       to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,
       USED,
       SQL_TEXT
  from dba_outlines
 where name like ‘LIZY%’;
02. 检查新进的SQL是否用上好的执行计划

四、 outline 互换
简述
 一般通过导出导入outline可解决紧急问题,但是有一些情况,比如需要导出导入的执行计划是经过修改后的SQL得到的(比如hint),
 修改后的SQL要保证和修改前的处理逻辑是一样的,返回结果是一样的,这种情况单纯的导出导入outline是不行的,需要用到outline互换。
 这里前两步做outline的方法,可用9i的,如果是10g及以上,也可以用10g的方法,在互换名称的时候注意名字正确就可以。
 
步骤一、 优化前做outline
 这一步是为原来的,性能差的执行计划生成outline。
CREATE OR REPLACE OUTLINE before for category special ON
<问题SQL文本>;

步骤二、 优化后做outline
这一步是为加Hint后的,好的执行计划生成outline。
CREATE OR REPLACE OUTLINE after for category special ON
<问题SQL文本>;

步骤三、 互换outline名字
这一步是关键,通过互换outline名字,达到交换执行计划的目的。
UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'after','before','before','after')
WHERE OL_NAME IN ('after','before');

步骤四、 启用outline
交换完成后,执行如下命令启用outline。
exec dbms_outln.update_signatures;
alter system set use_stored_outlines=special;

步骤五、 删除无用outline
通过前面交换后,加hint后的SQL执行执行计划变成原来的较差的执行计划了,为了不影响版本下发等,将这个outline删除掉。
drop outline after;

步骤六、 修改outline名称
将前面保留下来的before修改为有意义的名字。
alter outline before rename to kxn_1030675442;

步骤七、 验证outline
在生产库kill还在使用原执行计划的session,以便其重新登录后使用新计划,检查如下确认outline生效:
01. 检查dba_outlines:
select name,
       to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,
       USED,
       SQL_TEXT
  from dba_outlines
 where name like 'KXN%';
02. 检查新进的SQL是否用上好的执行计划

五、 11g SPM使用方法
简述
 11g数据库,采用SPM管理执行计划,应该优先使用SPM解决SQL执行计划问题,下面介绍常用场景下的操作方法。
 
场景一. 加hint优化后替换原执行计划
当在同一个数据库,既有好的执行计划,又有坏的执行计划时,采用这种方法。

步骤一、 装载坏的执行计划
获得执行计划错误的SQL语句的SQL_ID,并当前将坏的执行计划装载到SPM里。
variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => '&SQL_ID', PLAN_HASH_VALUE => &HASH_VALUE) ;

步骤二、 确认被装载到SPM
检查SPM,确认相关的SQL计划已经被装载到SPM,LOAD进来的一般是最新的。
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT
  from dba_SQL_PLAN_BASELINES
 where ACCEPTED = 'YES'
 order by LAST_MODIFIED;
 
步骤三、 优化SQL
调整SQL语句,如增加新的hint,确认获得理想的执行计划后,执行调整后的语句,取得SQL_ID和Plan hash value。
select sql_id, plan_hash_value
  from v$sql
 where sql_text like '%/*+ test2-nbh INDEX(demand_state_alias%';
注意
对于有绑定变量的SQL,最好也使用绑定变量的方式来获得正确的执行计划,如果使用字面量,执行计划虽然被装载,但可能无法被SQL语句使用。
同时可以在SQL语句增加一些特别的提示,以容易获得修改后的语句,如上面的查询增加test2-nbh这样一个标识。

步骤四、 装载好的执行计划
variable cnt number;
exec :cnt := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID          => '&SQL_ID',
                                                   PLAN_HASH_VALUE => &plan_hash_value,
                                                   SQL_HANDLE      => '&SQL_HANDLE');
如上变量解释如下表:
SQL_ID 步骤三中查询获得
Plan hash value 步骤三中查询获得
SQL_HANDL 步骤一中查询获得

步骤五、 验证SPM执行计划
方法一:
 步骤一的SQL_HANDLE在SPM记录有两个,可以通过时间的先后顺序来确定哪一个是好的执行计划。
select *
  from dba_sql_plan_baselines
 where CREATED > sysdate - 1 / 48
 order by created;
 
方法二:
 这里的sql_handle和PLAN_NAME来自步骤一生成的
select *
  from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('&sql_handle', '&PLAN_NAME');
 
步骤六、 删除坏的执行计划
通过上一步,可以判断出好的执行计划已经装载到SPM,此时在SPM删除坏的执行计划。
variable cnt number ;
exec :cnt :=dbms_spm.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=> '&SQL_HANDLE', PLAN_NAME=> '&PLAN_NAME')

步骤七、 验证执行计划生效
重新执行SQL,检查好的执行计划是否已经用上。
select EXECUTIONS,
       PLAN_HASH_VALUE,
       ELAPSED_TIME / 1000000,
       ELAPSED_TIME / 1000000 / EXECUTIONS,
       LAST_ACTIVE_TIME,
       ROWS_PROCESSED
  from v$sql
 where EXECUTIONS > 0
   and sql_id = '&sql';

select * from table(dbms_xplan.display_cursor('&sql'));

场景二. 导出导入执行计划
适用于将其它环境好的执行计划导入到目标库,例如从测试环境导入到生产环境。

步骤一、 生成base_line
在测试环境抓出有好执行计划的sql ,生成base_line。
variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bvsy2p39cc820', PLAN_HASH_VALUE => 672253729) ;
select *
  from table(dbms_xplan.display_sql_plan_baseline('SQL_f82353ef7b179e90'));
 
步骤二、 打包base line
在测试环境将好的base line 打包。
SET SERVEROUTPUT ON
DECLARE
   l_plans_packed  PLS_INTEGER;
BEGIN
   l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
    table_name      => 'T0SMSCP_BASELINE',
    table_owner     => 'DBMGR',
    sql_handle => 'SQL_5e7cfc266d4f7b4d',
    plan_name => 'SQL_PLAN_5wz7w4tqnyyud360478a9');

   DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台