课程频道导航
课程分类导航
当前位置：首页 > 数据库 > Oracle

Oracle Outline总结

一、基本概述
Oracle Outline，中文也称为存储大纲，是最早的基于提示来 控制 SQL运行计划的机制。也是9i以及之前版本号唯一能够用来稳定和控制SQL运行计划的工具。
outline 是一个hints（提示）的集合，更详细的讲，outline能够锁定一个给定 SQL的 运行计划，保持其运行计划稳定，无论数据库环境怎样变更（如统计信息。部分參数等）
注意：
  1. 从10g以后，oracle连续公布了sql profile和sql baseline来实现 SQL运行计划的 控制 。而且outline这个工具基本已经被Oracle废弃而且不在维护，可是无论怎么说。在10g以及11g版本号都还是能够使用。而且这个特性也一直使用的非常好。
  2. 10g以后建议使用sql profile或者sql baseline
  3. 因为眼下outline如今已经非常少使用，此文也尽量介绍有用的一部分
二、执行机制
Outline将运行计划的hint集合保存在outline的表中（数据字典）。

当运行

SQL解析时。Oracle会与outline中的SQL比較，假设该SQL有保存的outline，则通过保存的hint集合生成指定运行计划。
注意：
  1. SQL解析时，使用SQL文本却匹配数据字典outline保存的文本。此处匹配的方式为去掉SQL空格，忽略SQL大写和小写差别后。进行的比較。
  2. 比如,select * from dual 和SELECT *    FROM dual这两个语句将使用相同的outline。
三、使用场景
  1. 为避免在升级后某些 SQL 出现严重性能下降并且在短时间内不能优化的情况，我们能够使用 outline 的功能将原生产库中的 sql 运行计划实施在新的数据库上。
  2. 为避免 SQL 的运行计划在统计数据不准确的情况（如未能及时收集表或索引的统计信息）下导致变化从而引起的性能减少。
  3. 为避免easy由于 Bind Peeking 导致 SQL 运行计划变差从而引起的性能减少。
  4. 避免大规模分布实施的应用出现数据库版本号、配置等差别引起的优化器产生不同的运行计划。
  5. 某些 Bug 引起优化器生成较差的运行计划。在 bug 修复前我们能够使用 outline 来强制 SQL 的运行计划的正确。
  6. 早期优化器版本号从rule转换为cbo模式时，过渡期间用来维护业务稳定（运行计划稳定）
注意
不论什么一个数据库中。大部分的SQL语句运行计划应该是通过优化器自己主动生成，而且高效运行，而仅仅有极少部分，须要通过各种工具（outine、sql profile）来锁定运行计划
四、注意事项
  1. outline存在在outln用户中， Outln 用户是一个很重要的系统用户，其重要性跟 sys ， system 一样。在不论什么情况下都不建议用户删除 outln 。否则会引起数据库错误。
  2. 优化器通过 Outline 生成运行计划前提是 outline 内全部 hint 都有效的。
  3. 仅仅有设置use_stored_outlines參数后才干启用outline。
  4. 使用 字面值的sql 的共享程度不高（没有使用绑定变量）， Outline 针对绑定变量的 sql 较好。

    针对

     使用 字面值的 sql 的情况，须要每条 sql 都生成 outline 。
  5. 创建 outline 须要 create any outline  or execute_catelog_role 权限 。
  6. 要注意从 CBO 的角度来看。数据库表和索引的统计信息是随着数据量的变化而不断改变的。固定的运行计划在某些时段并不一定是最优的运行计划。所以 outline 的使用是要依据详细情况来决定的。
  7. 第一次应用 Outline  （ alter system  ）这个操作是会产生 Library cache pin 的，需慎重。
  8. 10.2.0.4 outline bug 6455659 
  9. use_stored_outlines參数重新启动后失效，须要又一次设置
  10. 当outline依赖的对象被删除时。outline并不会自己主动删除
五、outline相关的视图
  • 两个基本视图：dba_outlines,dba_outline_hints
  • 三个底层表：ol$、ol$hints、ol$nodes
六、使用outline
一、创建outline
创建outline的方法有三种。以下我们一一简介
1、给会话甚至整个系统运行的每一条SQL语句都创建outline，能够设置例如以下參数，分别针对会话级和系统级
ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;
注意：基本上没有不论什么一个数据库会这么做，因此这样的方式我们不做測试；
2、手工通过CREATE OUTLINE方式来创建给定SQL语句的outline，例如以下
CREATE or replace OUTLINE  outline_dh_test  FOR CATEGORY test on select * from dh_stat where id=11;
or
CREATE or replace OUTLINE  outline_dh_test1  on select * from dh_stat where id=11;
演示样例：
SQL > CREATE or replace OUTLINE  outline_dh_test  FOR CATEGORY test on select * from dh_stat where id=11;
Outline created.
SQL > set linesize 200 pagesize 999
SQL> set long 30
SQL> set long 50
SQL> select name,owner,category,used,sql_text from dba_outlines;
NAME                           OWNER                          CATEGORY                       USED   SQL_TEXT
------------------------------ ------------------------------ ------------------------------ ------ 
OUTLINE_DH_TEST                DBMON                          TEST                           UNUSED select * from dh_stat where id=11
SQL> select name,hint from dba_outline_hints;
NAME                           HINT
------------------------------ --------------------------------------------------
OUTLINE_DH_TEST                FULL(@"SEL$1" "DH_STAT"@"SEL$1")
OUTLINE_DH_TEST                OUTLINE_LEAF(@"SEL$1")
OUTLINE_DH_TEST                ALL_ROWS
OUTLINE_DH_TEST                OPT_PARAM(‘_optimizer_use_feedback‘ ‘false‘)
OUTLINE_DH_TEST                OPT_PARAM(‘_optimizer_adaptive_cursor_sharing‘ ‘fa
OUTLINE_DH_TEST                OPT_PARAM(‘_optimizer_extended_cursor_sharing_rel‘
OUTLINE_DH_TEST                OPT_PARAM(‘_bloom_pruning_enabled‘ ‘false‘)
OUTLINE_DH_TEST                OPT_PARAM(‘_gby_hash_aggregation_enabled‘ ‘false‘)
OUTLINE_DH_TEST                OPT_PARAM(‘_optimizer_extended_cursor_sharing‘ ‘no
OUTLINE_DH_TEST                OPT_PARAM(‘_bloom_filter_enabled‘ ‘false‘)
OUTLINE_DH_TEST                OPT_PARAM(‘_optimizer_null_aware_antijoin‘ ‘false‘
OUTLINE_DH_TEST                OPT_PARAM(‘_optim_peek_user_binds‘ ‘false‘)
OUTLINE_DH_TEST                DB_VERSION(‘11.2.0.1‘)
OUTLINE_DH_TEST                OPTIMIZER_FEATURES_ENABLE(‘11.2.0.1‘)
OUTLINE_DH_TEST                IGNORE_OPTIM_EMBEDDED_HINTS
15 rows selected.
注意：
  • 不指定outline类别是，默觉得default，并且此处创建时，不能指定为default类别（会报错）。
  • 这种方法不是非常方便。由于必须将整个SQL文本作为语句的一部分，可能导致语句无法共享等问题。因此非常少使用这样的方法

3、从10g起，能够通过引用共享池中已经存在的SQL语句来创建outline
exec DBMS_OUTLN.create_outline(hash_value=>1752921103,child_number => 0,category=>‘test‘);
注意
  • 这样的方法不能指定outline的详细名字。由系统自己主动生成，能够通过alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2改动
  • 不指定类别的话默觉得default，并且此处创建时，不能指定为default类别（会报错）。
  • 我们使用outline固定运行计划时，一般都是选用此种方法
  • 后面有一个简单演示样例，能够加深理解
二、改动outline
1、更改outline名称
alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2
2、更改outline类别
exec dbms_outln.update_by_cat(oldcat=>‘DDD‘,newcat=>‘DEFAULT‘);
3、重建outline
alter outline DH_TEST2 rebuild;
三、激活outline
Oracle优化器仅仅会考虑激活的outline，这意味着假设创建的outline没有被激活，Oracle根本不会使用它。一个outline必须满足例如以下两个条件才干被激活：
1、outline必须处于可用状态（创建时默认就是可用。一般不会有问题）。改动方法,alter outline DH_TEST2 disable;
2、类别必须通过初始化參数use_stored_ouotlines在会话或者系统级激活，能够设置为“TRUE/FALSE/类别名”三种，当中TRUE表示启用default类别
alter session set use_stored_outlines=TRUE;
四、移动outline
仅仅能通过将数据字典中保存的hint数据拷贝到还有一个数据库的数据字典。除此之外没有其他办法。

只是还好这种方法也很easy。由于outline相关的hint数据都保存在outln用户下的三张表中：ol$、ol$hints、ol$nodes。能够用以下的命令来导入和导出可用的outline
exp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) file=xxx.dmp
五、编辑outline，兴许会提供两种方法
当优化器无法为给定的SQL生成高效的运行计划时，能够通过编辑outline来实现，能够理解为改动outline中的hint
1 、使用原语句建 Outline
2 、查看 Outline 运行计划
 Select HINT_TEXT,USER_TABLE_NAME,JOIN_PRED,cardinality,bytes,cost
   from OUTLN.OL$HINTS
  where ol_name = ‘OLXXXXX_ORIG‘
3、 在 SQL 上加hint 。运行语句 （注意语句结构不能改变。不能添加或删除查询块什么的）
4、查看加上hint的SQL语句，运行计划是否与我们期望的一致
5、得到期望的运行计划后，将两个outline的运行计划进行互换，即完毕outline的编辑！
UPDATE OUTLN.OL$HINTS
   SET OL_NAME = DECODE(OL_NAME,
                        ‘OLXXXXX_MOD‘,
                        ‘OL XXXXX_ORIG‘,
                        ‘OLXXXXX_ORIG‘,
                        ‘OLXXXXX_MOD‘)
 WHERE OL_NAME IN (‘OLXXXXX_MOD‘, ‘OLXXXXX_ORIG‘);
6、启用outline
六、删除outline
能够用例如以下命令分别删除指定的outline或者某个类别下的全部outline
drop outline dh_test1;
dbms_outln.drop_by_cat(cat=>‘test‘);

演示样例一（引用使用共享池中的SQL来创建outline）
SQL > create table dh_stat as select rownum id ,object_name name ,object_type type from dba_objects;
SQL > create index ind_1 on dh_stat(id) compute statistics;
SQL > alter system flush shared_pool;
系统已更改。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>‘dbmon‘,TABNAME=>‘dh_stat‘,ESTIMATE_PERCENT=>30,METHOD_OPT=>‘FOR ALL COLUMNS SIZE 1‘,NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);
PL/SQL 过程已成功完毕。
SQL>  col name format a15
SQL>  col name format a30
SQL>  col sql_text format a55
给执行的语句加入一个独特的凝视，方便兴许查找语句
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like ‘%outlinetest1%‘ and sql_text not like ‘%v$sql%‘;
SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
select /* outlinetest1 */ * from dh_stat where id=771   053nzgm4f6rdr 3370343863            0
SQL>  select * from table(dbms_xplan.display_cursor(‘053nzgm4f6rdr‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
已选择19行。

SQL> exec DBMS_OUTLN.create_outline(hash_value=>3370343863,child_number => 0,category=>‘TEST‘);
PL/SQL 过程已成功完毕。
SQL> col category format a10
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403  TEST       UNUSED select /* outlinetest1 */ * from dh_stat where id=771
能够看到。outline确实已经生成
SQL> select /* outlinetest1 */ * from dh_stat where id=771;

        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403  TEST       UNUSED select /* outlinetest1 */ * from dh_stat where id=771
此处outline的USED状态没有改变，由于我们没有激活TEST类别的outline，再次申明，outline必须通过use_stored_outlines參数激活后，优化器才会使用outline！


SQL> select * from table(dbms_xplan.display_cursor(‘053nzgm4f6rdr‘,‘‘,‘‘));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
已选择19行。


以下这一步激活TEST类别的OUTLINE。也能够在系统级激活OUTLINE
SQL> alter session set use_stored_outlines=TEST;
会话已更改。


SQL> select /* outlinetest1 */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403  TEST       USED   select /* outlinetest1 */ * from dh_stat where id=771

再次查询OUTLINE的使用状态，能够看到，我们刚新建的outline确实已经被使用过。通过以下的运行计划，更加能够证实这一点
SQL> select * from table(dbms_xplan.display_cursor(‘053nzgm4f6rdr‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
SQL_ID  053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
Note
-----
   - outline "SYS_OUTLINE_14061209594622403" used for this statement
已选择42行。



演示样例二、编辑outline的两种方法演示样例
1、第一种是直接改动字典里的hint。这里就不測试了。
2、通过更换两个outline名称。来完毕改动
--这个实验紧接着“演示样例一（引用使用共享池中的SQL来创建outline）"
SQL > exec dbms_outln.clear_used(name=>‘SYS_OUTLINE_14061209594622403‘);
PL/SQL 过程已成功完毕。
---通过上面的过程。将outline的使用记录清理掉，USED状态再次转变为UNSED。方便我们測试
SQL> select name,category,used,sql_text from dba_outlines;
NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403  TEST       UNUSED select /* outlinetest1 */ * from dh_stat where id=771
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like ‘%outlinetest2%‘ and sql_text not like ‘%v$sql%‘;
SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 3fcq9c8xu4a92 1000483106            0
h_stat where id=771
SQL> alter session set use_stored_outlines=true;
会话已更改。
SQL> select * from table(dbms_xplan.display_cursor(‘3fcq9c8xu4a92‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3fcq9c8xu4a92, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=771
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |   124 (100)|          |
|*  1 |  TABLE ACCESS FULL| DH_STAT |     1 |    38 |   124   (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=771)
已选择19行。

SQL> exec DBMS_OUTLN.create_outline(hash_value=>1000483106,child_number => 0,category=>‘TEST‘);
PL/SQL 过程已成功完毕。
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004  TEST       UNUSED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
                                                 h_stat where id=771
SYS_OUTLINE_14061209594622403  TEST       UNUSED select /* outlinetest1 */ * from dh_stat where id=771
能够看到。我们新建的outline。确实已经成功
SQL> alter session set use_stored_outlines=TEST;
会话已更改。


SQL> select /* outlinetest1 */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select name,category,used,sql_text from dba_outlines;
NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004  TEST       USED   select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
                                                 h_stat where id=771
SYS_OUTLINE_14061209594622403  TEST       USED   select /* outlinetest1 */ * from dh_stat where id=771
能够看到，两个outline都已经标记为已经使用过
SQL> select * from table(dbms_xplan.display_cursor(‘3fcq9c8xu4a92‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3fcq9c8xu4a92, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=771
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |   124 (100)|          |
|*  1 |  TABLE ACCESS FULL| DH_STAT |     1 |    38 |   124   (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=771)
Note
-----
   - outline "SYS_OUTLINE_14061210153067004" used for this statement
已选择23行。

SQL> select * from table(dbms_xplan.display_cursor(‘053nzgm4f6rdr‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
....此处为了排版，省略了一个child number 0 的运行计划！........

SQL_ID  053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
Note
-----
   - outline "SYS_OUTLINE_14061209594622403" used for this statement
已选择42行。
SQL> alter session set use_stored_outlines=TRUE;
会话已更改。
通过例如以下方式，我们调换两个outline里面的hints
SQL> UPDATE OUTLN.OL$HINTS
  2     SET OL_NAME = DECODE(OL_NAME,
  3                          ‘SYS_OUTLINE_14061210153067004‘,
  4                          ‘SYS_OUTLINE_14061209594622403‘,
  5                          ‘SYS_OUTLINE_14061209594622403‘,
  6                          ‘SYS_OUTLINE_14061210153067004‘)
  7   WHERE OL_NAME IN (‘SYS_OUTLINE_14061210153067004‘, ‘SYS_OUTLINE_14061209594622403‘);
已更新12行。
SQL> commit;
提交完毕。
SQL> col hint_text format a50
SQL> select hint#,hint_text from outln.ol$hints a where ol_name=‘SYS_OUTLINE_14061209594622403‘;
     HINT# HINT_TEXT
---------- --------------------------------------------------
         1 FULL(@"SEL$1" "DH_STAT"@"SEL$1")
         2 OUTLINE_LEAF(@"SEL$1")
         3 ALL_ROWS
         4 DB_VERSION(‘11.2.0.1‘)
         5 OPTIMIZER_FEATURES_ENABLE(‘11.2.0.1‘)
         6 IGNORE_OPTIM_EMBEDDED_HINTS
已选择6行。


果然和我们预期的一样。outline里面的运行计划已经调换
SQL> select /* outlinetest1 */ * from dh_stat where id=771;

        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select * from table(dbms_xplan.display_cursor(‘053nzgm4f6rdr‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
SQL_ID  053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
Note
-----
   - outline "SYS_OUTLINE_14061209594622403" used for this statement
SQL_ID  053nzgm4f6rdr, child number 2
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771

Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=771)
已选择61行。
激活outline
SQL> alter session set use_stored_outlines=TEST;
会话已更改。

SQL> exec dbms_outln.clear_used(name=>‘SYS_OUTLINE_14061209594622403‘);
PL/SQL 过程已成功完毕。

SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004  TEST       USED   select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
                                                 h_stat where id=771
SYS_OUTLINE_14061209594622403  TEST       UNUSED select /* outlinetest1 */ * from dh_stat where id=771

SQL> select /* outlinetest1 */ * from dh_stat where id=771;

        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004  TEST       USED   select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
                                                 h_stat where id=771
SYS_OUTLINE_14061209594622403  TEST       USED   select /* outlinetest1 */ * from dh_stat where id=771

SQL> select * from table(dbms_xplan.display_cursor(‘053nzgm4f6rdr‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID  053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
SQL_ID  053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
Note
-----
   - outline "SYS_OUTLINE_14061209594622403" used for this statement
SQL_ID  053nzgm4f6rdr, child number 2
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=771)
SQL_ID  053nzgm4f6rdr, child number 3
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=771)
Note
-----
   - outline "SYS_OUTLINE_14061209594622403" used for this statement
已选择84行。

能够看到，尽管OUTLINE已经被使用，可是并非我们预期的那样。

想要得到我们预期，通过全表扫描来实现，我在測试中是通过以下的方法实现

SQL> alter session set use_stored_outlines=dd;
会话已更改。
SQL> exec dbms_outln.clear_used(name=>‘SYS_OUTLINE_14061209594622403‘);
PL/SQL 过程已成功完毕。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004  TEST       USED   select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
                                                 h_stat where id=771
SYS_OUTLINE_14061209594622403  TEST       UNUSED select /* outlinetest1 */ * from dh_stat where id=771
SQL> alter session set use_stored_outlines=TRUE;
会话已更改。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004  TEST       USED   select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
                                                 h_stat where id=771
SYS_OUTLINE_14061209594622403  TEST       UNUSED select /* outlinetest1 */ * from dh_stat where id=771
SQL> exec dbms_outln.update_by_cat(oldcat=>‘TEST‘,newcat=>‘DEFAULT‘);
PL/SQL 过程已成功完毕。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX
SQL> select name,category,used,sql_text from dba_outlines;
NAME                           CATEGORY   USED   SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004  DEFAULT    USED   select /* outlinetest2 */ /*+ full(dh_stat) */ * from d
                                                 h_stat where id=771
SYS_OUTLINE_14061209594622403  DEFAULT    USED   select /* outlinetest1 */ * from dh_stat where id=771
SQL> select * from table(dbms_xplan.display_cursor(‘053nzgm4f6rdr‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
....为了排版，省略了前面的4个子运行计划.........
SQL_ID  053nzgm4f6rdr, child number 4
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |   124 (100)|          |
|*  1 |  TABLE ACCESS FULL| DH_STAT |     1 |    38 |   124   (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------
   1 - filter("ID"=771)
Note
-----
   - outline "SYS_OUTLINE_14061209594622403" used for this statement
已选择102行。
能够看到。这次outline已经和我们预期的一样生效，而且是通过全表扫描来实现
并且通过多次实验证明，必须改动一下outline的类别名或者将语句刷出共享池才干实现。因此。我们先将outline创建为一个私有的类别，等验证完成且符合预期后，再正式公布出来。这样也不会须要改动数据库当前已有參数。

原文：http://www.cnblogs.com/bhlsheji/p/5349874.html
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！

相关教程推荐

其他课程推荐