1、打开一个session，设置自动创建sql的stored_outline
alter session set create_stored_outlines = demo;
2、抓取性能不好的sql，例如（假设nest loop 比较优）：
SELECT /*+ use_hash(a,b)*/* FROM game_draw_stats_site_sale a ,game_draw b WHERE a.draw_id =b.draw_id AND a.site_id =24;
在本session中运行。抓取stored_outline，视图user_outlines，
User_Outline_Hints中可查到：
SELECT * FROM user_outlines;
SELECT * FROM User_Outline_Hints;
1SYS_OUTLINE_1001042142101 DEMO USED -1-4 20:20:2110.2.0.2.0
2NEW_PLAN DEMO UNUSED-1-4 20:56:22 10.2.0.2.0
3、停止收集执行的路径（否则以下你执行的一些SQL也会放到存储概要的表格中，令接下来的处理有点困难）。
alter session set create_stored_outlines = false；
4、在有问题的sql上创建新的outline
此时的sql是 加上正确hint的语句，例如本例中将/*+ use_hash(a,b)*/ 换成了 /*+ use_nl(a,b)*/ )
create or replace outline new_plan for category demo on SELECT /*+ use_nl(a,b)*/ * FROM
game_draw_stats_site_sale a ,game_draw b WHERE a.draw_id =b.draw_id AND a.site_id =24;
其实就是将性能不好的sql的hint名改成现在的这个性能好的hint名，达到优化、固定执行计划的目的。
5、用新创建的outline进行交换：
update outln.ol$hints
set ol_name =
decode(
ol_name,
'NEW_PLAN','SYS_OUTLINE_1001042142101',
'SYS_OUTLINE_1001042142101','NEW_PLAN'
)
where ol_name in ('SYS_OUTLINE_1001042142101','NEW_PLAN')
;
update outln.ol$ ol1
set hintcount = (
select hintcount
from ol$ ol2
where ol2.ol_name in ('SYS_OUTLINE_1001042142101','NEW_PLAN')
and ol2.ol_name != ol1.ol_name
)
where
ol1.ol_name in ('SYS_OUTLINE_1001042142101','NEW_PLAN')
;
6、启动一个新的session进行测试：
alter session set use_stored_outlines =demo;
7、测试无误后，修改系统参数：
alter system set use_stored_outlines = demo;
8、对绑定变量的情况也可参考
SQL> alter system set use_stored_outlines =true;
System altered
SQL> create or replace outline ol_1 on SELECT * FROM game_draw_stats_site_sale a ,game_draw b WHERE a.draw_id =b.draw_id AND a.site_id =:1;
Outline created
SQL> create or replace outline ol_2 on SELECT /*+ use_hash */ * FROM game_draw_stats_site_sale a ,game_draw b WHERE a.draw_id =b.draw_id AND a.site_id =:1;
Outline created
SQL> show user
User is "SYS"
SQL> UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,'OL_1','OL_2', 'OL_2','OL_1') WHERE OL_NAME IN ('OL_1','OL_2');
16 rows updated
SQL> DROP OUTLINE OL_2;
Outline dropped
如果觉得《stored_outline优化稳定执行计划》对你有帮助，请点赞、收藏，并留下你的观点哦！
