[ZT] 存储框架(Stored Outline)
1.存储框架(Stored Outline)简介

两大优点
1)更改执行计划时无需更改SQL源代码
2)永久的SQL调整变更
如果你已经进行了SQL调整,那么你可以设置alter system set create_stored_outlines=true,这样oracle就会在这些SQL语句执行时,为所有会话中的SQL语句定义计划.如果你还没有调整SQL,那么你可以输入 alter session set create_stored_outlines=true;命令,来为会话创建个体存储框架
任何时候,只要有可能,所有的SQL都应该存储在PL/SQL过程函数或软件包中,同时使用绑定变量以确保SQL可以同时使用

1.1.优化去计划稳定性背后的思想

如果表中记录数经常变动,你可能不希望使用优化去计划稳定性,因为,你希望CBO基于当前的CBO统计资料为每一个SQL语句重新生成执行计划
如果表相对稳定,那么使用存储框架将会很大程度提高SQL的性能,因为这样做将绕过SQL解析过程(即使Oracle初始化参数和CBO统计资料变更时)
如果你选择为所有的SQL实施存储框架,这种情况下只有新的查询才会使用CBO统计资料,因此,你可以使用这个办法降低表和索引进行重新分析的次数

1.2.在向CBO迁移时使用优化器计划稳定性

alter system set use_stored_outlines=true命令

2.使用存储框架要做的准备

1)设置use_stored_outlines命令
2)检测重要的初始化参数
3)创建框架包
4)为存储框架定义表空间

2.1.设置use_stored_outlines命令

为整个数据库使用存储框架,可以使用alter system set use_stored_outlines=true,还可以在会话层启用优化器计划稳定性alter session set use_stored_outlines=true命令

2.2.检测重要的初始化参数

oracle建议在使用存储框架时,必须首先设置几个初始化参数
cursor_sharing=force:如果系统中的SQL包含嵌入的直接量,那么请设置这个参数
query_rewrite_enabled=true:这个参数启用物化视图
star_transformation_enabled=true:这个参数启用星型连接
optimizer_features_enable=true:这个设置将启用初始化参数b_tree_bitmap_plans.complex_view_merging,fast_full_scan_enabled和push_join_predicate

2.3.创建框架包

安装了oracle8i软件后,将创建一个叫做outln的用户,口令是outln.在$oracle_home/rdbms/admin目录中用来创建outln_pkg包的脚本是dbmsol.sql.
11:22:14 idle> conn sys/hewy0526@oracle10g as sysdba;
已连接。
11:22:25 sys@ORACLE10> @F:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\dbmsol.sql

2.4.创建存储框架表空间

create tablespace outline_ts datafile 'F:\oracle\product\10.2.0\oradata\oracle10g\outline.ora' size 10m autoextend on next 8m maxsize 100m extent management local autoallocate segment space management auto
下面,我们设置默认用户outln使用这个表空间
alter user outln default tablespace outline_ts;

3.如何创建和修改存储框架
注意如下2个查询
13:30:52 scott@ORACLE10> set autotrace traceonly explain
13:31:01 scott@ORACLE10> select count(*) from emp;

执行计划
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 15 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------

13:31:05 scott@ORACLE10> select /*+ full(emp) */count(*) from emp;

执行计划
----------------------------------------------------------
Plan hash value: 2083865914

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 15 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

13:31:09 scott@ORACLE10>

3.1.创建2个存储框架

其中一个加了提示强制更改执行计划
在这里为了演示
我们先给没有加提示的查询创建存储框架
语法如下:
create [or replace] outline outline_name[for category category_name] on sql_statement;
其中:outline_name 这是框架的惟一名称,自动框架被存储为SYS_OUTLINE-NNN其中nnn是一个大的唯一数
[for category category_name]这个可选字句将指定多个名称惟一的类,从而允许多个框架与一个单独查询相关联
所以:create or replace outline old_emp on select count(*) from emp;
再给加了提示的查询创建存储框架
create or replace outline new_emp on select /*+ full(emp) */count(*) from emp;

3.2.交换存储框架

update outln.ol$hints set ol_name=decode(ol_name,'OLD_EMP','NEW_EMP','NEW_EMP','OLD_EMP') where ol_name in ('OLD_EMP','NEW_EMP');
commit;

3.3.测试交换结果

sql查询时启用存储框架
alter system/session set use_stored_outlines=true/category(名)

13:47:50 scott@ORACLE10> alter session set use_stored_outlines=true;

会话已更改。

13:48:42 scott@ORACLE10> select count(*) from emp;

执行计划
----------------------------------------------------------
Plan hash value: 2083865914

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 15 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- outline "OLD_EMP" used for this statement

13:48:45 scott@ORACLE10>

从以上可以看到没有加提示的查询使用了加了提示的查询(从而实现了在不需要修改源代码的情况下更改查询的执行计划,进行优化.当然这里是为了演示,本来不加提示时的效率更高)

4.关联存储框架

4.1.为存储框架使用字典视图和表

主要有dba_outlines视图和ol$hints表
如下测试:
13:56:59 scott@ORACLE10> exec print_table('select * from dba_outlines where name
=''OLD_EMP''');
NAME : OLD_EMP
OWNER : SCOTT
CATEGORY : DEFAULT
USED : USED
TIMESTAMP : 20-7月 -07
VERSION : 10.2.0.1.0
SQL_TEXT : select count(*) from emp
SIGNATURE : 7D6BDCDAF50F877023AE078330586EA9
COMPATIBLE : COMPATIBLE
ENABLED : ENABLED
FORMAT : NORMAL
-----------------

PL/SQL 过程已成功完成。

13:57:18 scott@ORACLE10> exec print_table('select * from dba_outlines where name
=''NEW_EMP''');
NAME : NEW_EMP
OWNER : SCOTT
CATEGORY : DEFAULT
USED : UNUSED
TIMESTAMP : 20-7月 -07
VERSION : 10.2.0.1.0
SQL_TEXT : select /*+ full(emp) */count(*) from emp
SIGNATURE : 8FE40B282A47BFA087F920848B0C4DB0
COMPATIBLE : COMPATIBLE
ENABLED : ENABLED
FORMAT : NORMAL
-----------------

PL/SQL 过程已成功完成。

4.2.使用框架包

确定和删除不再使用的存储框架
使用直接量生成SQL的系统中存在一个问题是,即存在大量不可再用的SQL语句,在这种情况下,应该设置cursor_sharing=force来重新书写SQL,使用主机变量替代直接量,从而使这些SQL可以被再次使用
oracle为我们提供了一个释放不再使用的存储框架的过程,下面我们将运行某查询来查看外面的数据库是否包含没有再次使用的SQL语句

14:05:17 scott@ORACLE10> exec print_table('select * from dba_outlines where used
=''UNUSED''');
NAME : NEW_EMP
OWNER : SCOTT
CATEGORY : DEFAULT
USED : UNUSED
TIMESTAMP : 20-7月 -07
VERSION : 10.2.0.1.0
SQL_TEXT : select /*+ full(emp) */count(*) from emp
SIGNATURE : 8FE40B282A47BFA087F920848B0C4DB0
COMPATIBLE : COMPATIBLE
ENABLED : ENABLED
FORMAT : NORMAL
-----------------

PL/SQL 过程已成功完成。
现在,我们将释放所有没有再次使用的存储框架,
14:07:51 scott@ORACLE10> execute outln_pkg.drop_unused;

PL/SQL 过程已成功完成。

14:08:50 scott@ORACLE10> exec print_table('select * from dba_outlines where used
=''UNUSED''');

PL/SQL 过程已成功完成。

14:08:58 scott@ORACLE10>

4.3.关联存储框架的分类

1)drop_by_cat过程用来删除某一特定类的所有框架.drop_by_cat过程有一个输入变量cat,它使一个varchar2型的变量,用于说明想要释放的类别名称
14:08:58 scott@ORACLE10> create or replace outline test_emp for category my_test
on select count(*) from emp;

大纲已创建。

14:12:53 scott@ORACLE10> exec print_table('select * from dba_outlines where cate
gory=''MY_TEST''');
NAME : TEST_EMP
OWNER : SCOTT
CATEGORY : MY_TEST
USED : UNUSED
TIMESTAMP : 20-7月 -07
VERSION : 10.2.0.1.0
SQL_TEXT : select count(*) from emp
SIGNATURE : 7D6BDCDAF50F877023AE078330586EA9
COMPATIBLE : COMPATIBLE
ENABLED : ENABLED
FORMAT : NORMAL
-----------------

PL/SQL 过程已成功完成。

现在为了删除my_test类中所有的存储框架
14:14:59 scott@ORACLE10> exec outln_pkg.drop_by_cat('MY_TEST');

PL/SQL 过程已成功完成。

14:15:21 scott@ORACLE10> exec print_table('select * from dba_outlines where cate
gory=''MY_TEST''');

PL/SQL 过程已成功完成。

2)update_by_cat过程将合并一个类中的所有框架,并生成一个新的类.重复的框架不会合并到新类中
自行进行测试

结论:
1)存储框架将会改进SQL的性能,因为使用存储框架的SQL语句在调用时,不需要重新组织执行计划
2)如果不能对SQL源代码进行修改,那么可以使用存储框架来调整数据库
3)我们可以使用alter system set use_stored_outlines=true命令在系统层启用存储框架
4)外面可以使用alter session set use_stored_outlines=true命令在会话层启用存储框架
5)alter system set create_stored_outlines=true创建存储框架
6)alter session set create_stored_outlines=true创建存储框架
7)或者对单个SQL执行,如前面所讲