Hello DBA

对于一个高并发的OLTP系统，SQL执行计划的改变往往意味着灾难。很多因素都可能导致执行计划发生不可预期的改变，比如表结构，索引，统计信息等变化，甚至我们发生过一个小小的grant操作，导致执行计划失效，重新解析后生成了一个不正确的执行计，让整个系统Crash的案例。最近，我们对一个分区表增加分区后，导致了执行计划发生改变，故障再次重演。

如何控制SQL的执行计划就成为了一个课题，之前我曾经写过一篇关于调整SQL执行计划的文章，但是内容比较粗略。因为Oracle提供了很多手段去控制执行计划，所以我打算为每个版本都整理一个最佳实践。

我们设想一个场景，一个SQL本来应该走nested loop join，但是由于某种原因，突然变成了hash join，调整统计信息无效，数据库load不断升高，只留给你很少的时间，怎么办？最直接有效的方法是在SQL上加hints，但是需要程序发布，或者程序根本无法修改。

9i为我们提供了Stored outline，大家都非常熟悉，但是对于上面的场景，还是需要点技巧。方法是：生成两个stored outline，一条是错误的，一条是正确的（加hints），两个然后将其执行计划交换。看下面的步骤：

1.创建两个public stroed outline，第一个是目前运行的，第二个是加了hints.

create or replace outline OUTLN1 on select e.ename from emp e, dept d where e.deptno=d.deptno;
create or replace outline OUTLN2 on select /*+ use_nl(e d)*/ e.ename from emp e, dept d
where e.deptno=d.deptno;

拷贝成为private outline

create or replace private outline PRIV_OUTLN1 from OUTLN1;
create or replace private outline PRIV_OUTLN2 from OUTLN2;

交换两个stored outline的执行计划

update ol$ set ol_name=decode(ol_name,'PRIV_OUTLN1','PRIV_OUTLN2','PRIV_OUTLN2','PRIV_OUTLN1')
where ol_name in ('PRIV_OUTLN1','PRIV_OUTLN2');

设置本session生效，并测试结果

alter session set use_private_outlines=true;

刷新

execute dbms_outln_edit.refresh_private_outline('PRIV_OUTLN1');
execute dbms_outln_edit.refresh_private_outline('PRIV_OUTLN2');

发布到public outline

create or replace outline OUTLN1 from private PRIV_OUTLN1;
create or replace outline OUTLN2 from private PRIV_OUTLN2;

设置使用，调整完毕

alter system set use_stored_outlines=true;

这样操作的原因是因为stroed outline必须依赖SQL的文本匹配，所以，我们利用加hints之后的SQL产生正确的执行计划，并通过交换的方式，让SQL的执行计划变成我们想要的结果。当然，如果你愿意，也可以直接修改ol$hints表的内容，同样可以达到改变执行计划的目的。

–EOF–