V1.0
|
Revision |
Author/Modifier | Comments | |
|
No. |
Date |
||
| 1.0 | 2012-02 | 魏兴华 | 初稿 |
一、 目的
通过outline来固定SQL执行计划以确保执行计划稳定性以及提高性能。outline是oracle 9i提供的唯一的固化执行计划的方法。ORACLE 10G后，ORACLE更建议通过sql profile,baseline来固定执行计划。我们线上库用outline来固定执行计划的case还很少，在我看来outline有三个明显的缺点：1）即使使用了outline，还是可能观察到执行计划的改变。2）outline的系统级启用，需要设置参数use_stored_outlines，而这个参数不可以在初始化参数文件里设置，每次需要在数据库启动后，显式的进行设置，当然这个缺点可以通过系统触发器来进行规避。3）对于执行计划走错的SQL，通过outline修正执行计划，过程繁琐。
二、 适用范围
l 对于执行计划容易出错的SQL通过outline进行固化。
l SQL动态拼接，无法直接在原始SQL文本里添加HITN。
l 执行计划走错，数据库压力飙升，应用端不能及时发布修正。对于SQL执行次数非常高的SQL，如果要通过outline固定执行计划，只能作为临时方案，以免SQL文本变更，导致执行计划再次走错，数据库崩溃。
l ORACLE版本升级。通过在源库生成outline然后导入到新库，来保证升级前后执行计划的稳固。
三、 风险评估
l outline、sql profile、baseline这三种固定执行计划的方式都依赖与SQL的文本，如果SQL文本变了，就失去了固定的作用。因此，通过outline来固定执行计划，一般只能作为临时方案来应急，特别是执行频率非常高的SQL，最好通过HINT或者分析统计信息等手段解决。
l outline是9I所提供的固化执行计划的方式，它一个很大的缺点是，数据库启动后，需要显示的去设置use_stored_outlines参数值为true,才能启用已经创建的outline。而且这个参数use_stored_outlines不能在初始化参数文件里设置。
l outline是依赖sql文本进行匹配的，这意味着一份outline可能同时被用于两张有相同名称但分属于不同schema下的表，使用前需要确认是否有此类情况，如果存在，而且两个不同schema的SQL要走不同的执行计划，那么使用outline就可能有问题。解决的办法是，修改原始SQL文本使之不相同。
l 创建的outline的信息最终会保存在系统的outln用户下，Outln用户是一个非常重要的系统用户，其重要性跟sys，system一样。在任何情况下都不建议用户删除outln，否则会引起数据库错误。
l 需要注意，表对象被删除时，outline并不会被删除，这个应该也不是很严重的问题。如果想要删除outline，必须显式的用命令删除。
四、 操作流程
1. 准备工作
a) 检查创建outline的用户是否有相关权限。创建、删除、更改outline的系统权限分别是create any outline,drop any outline,alter any outline。
b) 为了编辑私有outline。需要创建工作表（10G以后默认工作表就是存在的，不需要做任何操作）。请确认工作表是否存在。通过如下方式创建工作表.
CONNECT / AS SYSDBA
ALTER SESSION SET current_schema = system;
execute dbms_outln_edit.create_edit_tables
CREATE OR REPLACE PUBLIC SYNONYM ol$ FOR system.ol$;
CREATE OR REPLACE PUBLIC SYNONYM ol$hints FOR system.ol$hints;
CREATE OR REPLACE PUBLIC SYNONYM ol$nodes FOR system.ol$nodes;
GRANT SELECT,INSERT,UPDATE,DELETE ON system.ol$ TO public;
GRANT SELECT,INSERT,UPDATE,DELETE ON system.ol$hints TO public;
GRANT SELECT,INSERT,UPDATE,DELETE ON system.ol$nodes TO public;
c) 确认数据库有相关的trigger来确保每次数据库启动后，会自动设置初始化参数use_stored_outlines，如果不设置初始化参数，所有之前创建的outline将不会生效。可以通过如下方式来创建系统级别的trigger.
Create the following trigger to be run on startup of the database:
create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate(’alter system set use_stored_outlines=true’);
end;
/
d) 获得需要固化SQL的文本
2. 执行过程
我们以如下查询为例。object_id列上存在索引。查询默认的执行计划走了 object_id列上的索引。
select * from wxh_tbd where object_id=:1
我们可能对于这个查询计划的固化有两种需求：
1）想继续用走索引的执行计划，为确保执行计划不走错，通过outline来固化执行计划。步骤如下：
create or replace outline pub_out on select * from wxh_tbd where object_id=:1;
这样整个固化的过程就完成了，非常的简单。
2)不想用走索引的执行计划，想让执行计划走全表扫描。可以通过如下方式操作：
步骤一：基于原始SQL文本创建一个outline。由于默认的执行计划是走索引的，因此后续我们需要修改这个outline,,让它走全表
create or replace outline pub_out on select * from wxh_tbd where object_id=:1;
步骤二：创建两个私有outline，后面我们要交换这两个outline的hint。
create or replace private outline pri_out_1 on select * from wxh_tbd where object_id=:1;
create or replace private outline pri_out_2 on select /*+ full(wxh_tbd) */ * from wxh_tbd where object_id=:1;
步骤三：交换两个outline的HITN
update ol$hints set ol_name=decode(ol_name,’PRI_OUT_1′,’PRI_OUT_2′,’PRI_OUT_2′,’PRI_OUT_1′) where ol_name in (’PRI_OUT_1′,’PRI_OUT_2′);
commit;
步骤四：测试结果。
alter session set use_private_outlines=true;
execute dbms_outln_edit.refresh_private_outline(’PRI_OUT_1′);
execute dbms_outln_edit.refresh_private_outline(’PRI_OUT_2′);
explain plan for
select * from wxh_tbd where object_id=:1;
select * from table(dbms_xplan.display(null,null,’outline’));
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 577 | 99K| 140 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| WXH_TBD | 577 | 99K| 140 (2)| 00:00:02 |
—————————————————————————
Note
—–
- outline “PRI_OUT_1″ used for this statement
在当前SESSION生效，看到执行计划Note部分用到了outline
步骤五：发布到公共outline
create or replace outline pub_out from private PRI_OUT_1;
3. 验证方案
另开一个SESSION，确定已经用到了OUTLINE
explain plan for
select * from wxh_tbd where object_id=:1;
select * from table(dbms_xplan.display(null,null,’outline’));
PLAN_TABLE_OUTPUT
————————————————————————–
Plan hash value: 3295978849
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 577 | 99K| 140 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| WXH_TBD | 577 | 99K| 140 (2)| 00:00:02 |
—————————————————————————
Note
—–
- outline “PUB_OUT” used for this statement
执行计划的note部分显示已经用到了outline ，执行计划也由索引扫描改为了全表扫描。这一步需要注意，请确保参数use_stored_outlines已经正确设置为true.
五、 核心对象风险
理论上outline影响到的只是一条特定的SQL,因此相对风险比较低。对于核心对象，请尽量放到业务低峰期进行操作。对于执行频率非常高的SQL，不建议将使用outline来作为最终方案，以免SQL文本变更后，导致执行计划走错。
六、 回退方案
drop outline outline_name;
七、 历史故障及教训
无
