稳定执行计划操作手册–oracle库。稳定执行计划操作手册。
一、 概述
本文档列举了不同版本间稳定执行计划的方法，给出了详细的操作步骤。考虑到可阅读性及可操作性，未做过多的原理介绍，需要使用者在操作前已经具备相关技术知识，以便操作起来得心应手。
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 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 (”,”,”)”
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 (”,”,”)”
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这样一个标识。