Oracle Outline总结



转载：http://www.linuxidc.com/Linux/2014-07/103936.htm

一、基本概述

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。

Oracle教程：实例故障恢复 http://www.linuxidc.com/Linux/2011-08/40857.htm

Linux-6-64下安装Oracle 12C笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm

在CentOS 6.4下安装Oracle 11gR2(x64) http://www.linuxidc.com/Linux/2014-02/97374.htm

Oracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htm

Debian 下 安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm

三、使用场景

1. 为避免在升级后某些SQL出现严重性能下降而且在短时间内不能优化的情况，我们可以使用outline的功能将原生产库中的sql执行计划实施在新的数据库上。
2. 为避免SQL的执行计划在统计数据不准确的情况（如未能及时收集表或索引的统计信息）下导致变化从而引起的性能降低。
3. 为避免容易因为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固定执行计划时，一般都是选用此种方法
• 后面有一个简单示例，可以加深理解