ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于执行计划稳定性(三)

关于执行计划稳定性(三)

原创 Linux操作系统 作者:NinGoo 时间:2019-03-04 13:45:07 0 删除 编辑

关于执行计划稳定性(一):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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    132008