Oracle Outline总结

it2025-06-05 78

一、基本概述 Oracle Outline，中文也称为存储大纲，是最早的基于提示来控制SQL运行计划的机制。也是9i以及之前版本号唯一能够用来稳定和控制SQL运行计划的工具。

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

当运行

SQL解析时。 Oracle 会与 outline 中的 SQL 比較，假设该 SQL 有保存的 outline ，则通过保存的 hint集合生成指定运行计划。注意： SQL解析时，使用SQL文本却匹配数据字典outline保存的文本。此处匹配的方式为去掉SQL空格，忽略SQL大写和小写差别后。进行的比較。比如,select * from dual 和SELECT * FROM dual这两个语句将使用相同的outline。三、使用场景为避免在升级后某些SQL出现严重性能下降并且在短时间内不能优化的情况，我们能够使用outline的功能将原生产库中的sql运行计划实施在新的数据库上。为避免SQL的运行计划在统计数据不准确的情况（如未能及时收集表或索引的统计信息）下导致变化从而引起的性能减少。为避免easy由于Bind Peeking导致SQL运行计划变差从而引起的性能减少。

避免大规模分布实施的应用出现数据库版本号、配置等差别引起的优化器产生不同的运行计划。某些Bug引起优化器生成较差的运行计划。在bug修复前我们能够使用outline来强制SQL的运行计划的正确。早期优化器版本号从rule转换为cbo模式时，过渡期间用来维护业务稳定（运行计划稳定）注意不论什么一个数据库中。大部分的SQL语句运行计划应该是通过优化器自己主动生成，而且高效运行，而仅仅有极少部分，须要通过各种工具（outine、sql profile）来锁定运行计划四、注意事项 outline存在在outln用户中，Outln用户是一个很重要的系统用户，其重要性跟sys，system一样。在不论什么情况下都不建议用户删除outln。否则会引起数据库错误。

优化器通过Outline生成运行计划前提是outline内全部hint都有效的。

仅仅有设置use_stored_outlines參数后才干启用outline。

使用字面值的sql的共享程度不高（没有使用绑定变量），Outline针对绑定变量的sql较好。

针对

使用字面值的sql的情况，须要每条sql都生成outline。创建outline须要create any outline or execute_catelog_role权限。要注意从CBO的角度来看。数据库表和索引的统计信息是随着数据量的变化而不断改变的。固定的运行计划在某些时段并不一定是最优的运行计划。所以outline的使用是要依据详细情况来决定的。第一次应用Outline （alter system ）这个操作是会产生Library cache pin的，需慎重。10.2.0.4 outline bug 6455659 use_stored_outlines參数重新启动后失效，须要又一次设置当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', 'OLXXXXX _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。也能够在系统级激活OUTLINESQL> 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 INDEXSQL> 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 INDEXSQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771; ID NAME TYPE---------- ------------------------------ --------------- 771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEXSQL> 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 INDEXSQL> 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行。激活outlineSQL> 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=771SQL> select /* outlinetest1 */ * from dh_stat where id=771; ID NAME TYPE---------- ------------------------------ --------------- 771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEXSQL> 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=771SQL> 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创建为一个私有的类别，等验证完成且符合预期后，再正式公布出来。这样也不会须要改动数据库当前已有參数。

转载于:https://www.cnblogs.com/bhlsheji/p/5349874.html

相关资源：数据结构—成绩单生成器

最新回复(0)