关于执行计划稳定性（三）

关于执行计划稳定性（一）：http://ningoo.itpub.net/post/2149/247475

关于执行计划稳定性（二）：http://ningoo.itpub.net/post/2149/247477

1.outline需要的相关权限

• create any outline
• alter any outline
• drop any outline
• execute on outln_pkg

其中outln_pkg是一个用来管理outline的一个包。该包提供了诸如批量删除未使用的outline(drop_unused)，批量删除某个category的所有outline(drop_by_cat)等功能，还为exp/imp等工具提供了API接口。

2.通过视图dba_outlines / all_outlines / user_outlines可以查看outlines的信息

SQL> select name,category,used from dba_outlines;

NAME CATEGORY USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_07010322071753105 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071743702 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071743701 MYCATEGORY USED
SYS_OUTLINE_07010322071753103 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071754606 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071756208 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071756207 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071757811 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071757809 MYCATEGORY UNUSED

已选择9行。

3.outlines实际上是通过一系列的hint来实现的，视图dba_outline_hints / all_outline_hints / user_outline_hints保存了相关信息。

SQL> select name,hint from dba_outline_hints;

NAME HINT
------------------------------ --------------------------------------------------------------------
SYS_OUTLINE_07010322071743701 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07010322071743701 ALL_ROWS
SYS_OUTLINE_07010322071743701 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071743701 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071753105 FULL(@"SEL$F5BB74E1" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071743702 FULL(@"SEL$F5BB74E1" "T"@"SEL$2")
SYS_OUTLINE_07010322071743702 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071743702 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071743702 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071743702 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071743702 ALL_ROWS
SYS_OUTLINE_07010322071743702 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071743702 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071743701 FULL(@"SEL$1" "T"@"SEL$1")
SYS_OUTLINE_07010322071753105 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071753105 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071753105 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071753105 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071753105 ALL_ROWS
SYS_OUTLINE_07010322071753105 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071753105 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071753103 FULL(@"DEL$1" "PLAN_TABLE"@"DEL$1")
SYS_OUTLINE_07010322071753103 OUTLINE_LEAF(@"DEL$1")
SYS_OUTLINE_07010322071753103 ALL_ROWS
SYS_OUTLINE_07010322071753103 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071753103 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071754606 FULL(@"SEL$4A78348A" "KOKBF$"@"SEL$E112F6F0")
SYS_OUTLINE_07010322071754606 OUTLINE(@"SEL$E112F6F0")
SYS_OUTLINE_07010322071754606 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071754606 MERGE(@"SEL$E112F6F0")
SYS_OUTLINE_07010322071754606 OUTLINE_LEAF(@"SEL$4A78348A")
SYS_OUTLINE_07010322071754606 ALL_ROWS
SYS_OUTLINE_07010322071754606 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071754606 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071756208 FULL(@"SEL$F5BB74E1" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071756208 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071756208 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071756208 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071756208 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071756208 ALL_ROWS
SYS_OUTLINE_07010322071756208 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071756208 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071756207 FULL(@"SEL$1" "PLAN_TABLE"@"SEL$1")
SYS_OUTLINE_07010322071756207 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07010322071756207 ALL_ROWS
SYS_OUTLINE_07010322071756207 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071756207 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071757811 FULL(@"SEL$F5BB74E1" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071757811 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071757811 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071757811 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071757811 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071757811 ALL_ROWS
SYS_OUTLINE_07010322071757811 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071757811 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071757809 FULL(@"SEL$2" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071757809 FULL(@"SEL$1" "PLAN_TABLE"@"SEL$1")
SYS_OUTLINE_07010322071757809 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071757809 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071757809 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07010322071757809 OUTLINE_LEAF(@"SEL$2")
SYS_OUTLINE_07010322071757809 ALL_ROWS
SYS_OUTLINE_07010322071757809 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071757809 IGNORE_OPTIM_EMBEDDED_HINTS

已选择64行。

4.outline和outline hint的相关基表为ol$和ol$hints。如果要将某个category迁移到另外一个db，可以将ol$和ol$hints表中的相关内容exp出来，在imp到新的db即可

5.outline重命名

SQL> alter outline SYS_OUTLINE_07010322071753105 rename to myoutline1;

大纲已变更。

6.改变outline的category，如果是不存在的category则会自动创建新的category

SQL> alter outline myoutline1 change category to mycategory1;

大纲已变更。

7.根据当前的环境重新生成outline

SQL> alter outline myoutline1 rebuild ;

大纲已变更。

8.删除outline

SQL> drop outline myoutline1;

大纲已删除。

附：

在9.2.0.4,9.0.2.5,10.1.0.2等版本的RAC环境中，使用use_stored_outlines可能导致library cache lock，这是一个bug，Bug 3312874

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50268/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50268/