Stored Outline存储提纲介绍

第一篇：outline是什么？

    outline设计来提供稳定的执行计划，以消除执行环境或对象统计信息的改变造成的影响。这个特性叫做计划稳定性。其实outline就是一堆hint的集合。

    查询优化器接收到sql后，在选择是否使用OUTLINE时的逻辑。

    a.对SQL语句进行标准化去除空白大小写差异

    b.计算签名

    c.对比数据字典，看是否已存在一样的签名

    d.如果是，再进一步比较sql语句是否相同（避免hash冲突）

    e.如果SQL一样，就可以将这个outline的相关hint收入郎中，以影响执行计划的生成了。

9i开始就可以用outline,但10g开始才可以从共享池的一个游标来手动创建outline。

要使用 use_stored_outlines，必须开启 use_stored_outlines参数

alter system set  use_stored_outlines = test;

当一条SQL语句有sql profile又有outline时，查询优化器会有限选择outline。

何时使用：

    每当调优一条特殊的SQL并且无法在应用程序里修改它时（比如，不能使用hint），就可以使用这项技术。但如果加了hint也不走一些执行计划，那用outline也木有用。

比如id列是 varchar2(1)并且 有索引，而sql用where to_number(id)=1，此时加了/*+index(t ind)*/也不走索引，用outline也无补于事。

第二篇：创建outline

1.自创创建outline

ALTER [SESSION | SYSTEM] SET create_stored_outlines=TRUE|类别名;

例子：alter session set create_stored_outlines=fwy;  --true模式的category是default

这个参数无法在参数文件指定，只能起库后再人工或触发器指定。

2.手动创建outline

2.1  基于文本

create or replace outline out_1

--for category test

on select 3+8 from dual;

2.2.基于hash_value

begin

  dbms_outln.create_outline(

  hash_value=>'1812086974',

  child_number =>0

-- category => 'test'

  );

end;

/

第三篇：查询outline

select * from dba_outlines where timestamp>sysdate-1/24;--过去一小时刚建的outline

select * from dba_outlines where timestamp>sysdate-5/(24*60); --过去5分钟建的outline

select hint from dba_outline_hints where name='OUTLINE_FROM_TEXT';--查询某个outline的hint集合

查看outline是否有被使用

1.

10gR2起，dbms_xplan函数看执行计划，会看到是否有使用Outline

2.

所有版本通行：

execute dbms_outln.clear_used(name=> 'OUT_1');   --重置outline使用标记

select used from dba_outlines  where name='OUT_1'; --显示UNUSED

然后执行SQL

select used from dba_outlines  where name='OUT_1';--此时如果显示为used，则上述SQL有用到该OUTLINE了。

第四篇：更改OUTLINE

--Rename outline

alter outline SYS_OUTLINE_13090110433120101 rename to out2;

--更改单个outline的类别

alter outline out2 change category to default;

--更改多个outline类别，属于类别TEST的整体替换为类别default

execute dbms_outln.update_by_cat(oldcat=>'TEST',newcat=>'DEFAULT');

--重建存储提纲

当你希望query optimizer根据新环境产生一个新的hint集合，可以使用它。比如改变了outline相关对象的访问结构时。

alter outline out_1 rebuild;

--query optimizer只考虑激活的outline，outline有两个条件符合才可以被激活

--第一，要处于enable状态。

创建时默认就是。如果不是可以通过以下语句做状态变换。

alter outline out_1 enable;

alter outline out_1 disable;

--第二，初始化参数use_stored_outlines在session级别或system级别激活。

true就是用default类别,false就是不用outline,或者某个类别名。

alter session set use_stored_outlines=true;

alter system set use_stored_outlines=true;

--这个参数一次只能激活一个类别，也就是跟sql profile一样，一个会话一次只能用一个类别。

十分奇怪的是，这个参数无法在pfile或者spfile中设置，只能实例系统后再设置。也可以借助触发器完成。

第五篇：移动outline

---------------------------------------  vi  exp.par

userid="/ as sysdba"

file=exp01.dmp

log=exp_outline.log

tables=(outln.ol$,outln.ol$hints,outln.ol$nodes)

query="where ol_name ='FWY_3'"

---------------------------------------------------- exp parfile=exp.par

------------------------------vi imp.par

userid="/ as sysdba"

file=exp01.dmp

log=imp_outline.log

full=y

ignore=y

-----------------------------imp parfile=imp.par

--移动单个outline或者单个类别，在上述基础上加上以下语句

query="where ol_name ='OUT_1'" 

query="where category='TEST'"

比如，如果一个给定的 SQL语句的执行计划使用了一个你本想避免的索引扫描，那么你可以在测试系统中删除索引，在那里生成一个Outline，然后再将其转移到生产环境。

第六篇：编辑outline

想要编辑outline里面的hint,对数据字典中的公有outline用一些普通的SQL进行update是不行的，而对私有outline进行编辑是可行的。私有outline是存储在工作表中的，经过创建、编辑、测试后，我们可以将私有outline发布成公有outline。

1创建的两种方法

1.1.

create or replace private outline out_3

on select * from dual;

1.2

create private outline p_out_1 from public out_1;

--10g后，默认工作表就是在system用户下的ol$,ol$hints,ol$nodes，还有默认的三个公共同义词指向它们。对于这三个表，在outln中也是有的，这才是作为公有的outline而存在的表。

如果用9i,就要手动创建工作表与公共同义词。

sqlplus '/as sysdba'

alter session set current_schema=system;

execute dbms_outln_edit.create_edit_tables;

create or replace public synonym ol$ for system.ol$;

create or replace public synonym ol$hints for system.ol$hints;

create or replace public synonym ol$nodes for system.  ol$nodes  ;

grant select,insert,update,delete on system.ol$ to public;

grant select,insert,update,delete on system.ol$hints to public;

grant select,insert,update,delete on system.ol$nodes to public;

一旦私有outline创建完毕，就可以列出私有outline相关的Hint

select hint#,hint_text

from system.ol$hints

where ol_name='OUT_4';

--然后用普通的sql语句可以更新它的hint

--本来HINT#的1的hint_text是     INDEX(@"SEL$1" "T"@"SEL$1" ("T"."N")) 

--现在要变成走全表扫描

update ol$hints

set hint_text='FULL(@SEL$1 T)'

where hint#=1

and ol_name='P_OUTLINE_EDITING';

--为了确保这个outline在内存中的副本也同步进行了更改，执行如下

execute dbms_outln_edit.refresh_private_outline('OUT_1')

--接着，激活并测试这个private outline.

alter session set use_private_outlines =true;

explain plan for select * from t where n=1970;

select  * from table(dbms_xplan.display(null,null,'basic +note)'));

--如果对private outline满意，就可以使用下面这个sql将它发布成public outline.

create public outline out_1 from private p_out_1;

第七篇  删除存储提纲

删除单个

drop outline out_1;

删除整类别

dbms_outln.drop_by_cat(cat=>'TEST');

删除私有提纲

drop private outline  out_1;

第八篇：OUTLINE互换

--将BEFORE与AFTER两个outline互换

UPDATE OUTLN.OL$HINTS

SET OL_NAME=DECODE(OL_NAME,'AFTER','BEFORE','BEFORE','AFTER')

WHERE OL_NAME IN ('AFTER','BEFORE');

exec dbms_outln.update_signatures;  --更新签名

drop outline after;--更换完后，可以将不用的outline删除

第九篇：其他事项

在10gr1之前，outline是不能覆盖初始化参数 ，为了成功用outline,这几个初始化参数要与执行化境设置保持一致才可以噢。

optimizer_features_enable

query_rewrite_enabled

star_transformation_enabled

A用户创建的outline，B用户也可以用，不用授权。

OUTLINE在RAC环境下，每个实例都需要开启使用参数，才能用OUTLINE。