对于一个高并发的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–
Latest Comments
RSS