第 11 章 优化器计划稳定性
　　11.1 概览

　　CREATE OR REPLACE ONLINE MyOutLine

　　FOR CATEGORY mycategory

　　ON

　　SELECT ……；

　　需要CREATE OUTLINE权限

　　使用时指定会话的CATEGORY即可：

　　ALTER SESSION SET USE_STORED_OUTLINES = mycategory；

　　11.2 OPS的使用

　　对已封装的应用中SQL进行的优化方法

　　ALTER SESSION SET CREATE_STORED_OUTLINES = test；

　　执行应用，如一个存储过程等

　　ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE；

　　SET LONG 5000

　　SELECT name， sql_text FROM user_outlines WHERE category = ‘test’；

　　此时可以看到所运行的SQL语句。也可以通过一个ON LOGON触发器来实现，即一登陆就ALTER SESSION…

　　优化时修改OPTIMIZER_GOAL后：

　　ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS；

　　ALTER OUTLINE name REBUILD；

　　ALTER SESSION SET OPTIMIZER_GOAL = CHOOSE；

　　此时就固定为OPTIMIZER_GOAL = FIRST_ROWS时的执行计划了。

　　一个开发工具

　　由于开发环境与实际部署环境可能不一致，为了保证执行计划与开发环境一致，可以建立一个ON LOGON触发器来将执行计划归入一个category中，然后exp/imp到新环境中。

　　用来观察是否使用了索引

　　SELECT name， hint FROM user_outline_hints

　　WHERE hink LIKE ‘INDEX%’；

　　用来观察应用使用了什么SQL语句

　　11.3 OPS如何工作

　　OUTLINES与OUTLINE_HITS

　　均分别有DBA_、USER_、ALL_三张视图，其中DBA_多一个owner字段，说明创建者，另两张与用户有关系。

　　DBA_OUTLINES：

　　NAME

　　OUTLINE名，若创建时未指定，则使用系统命名

　　OWNER

　　创建时的方案名

　　CATEGORY

　　创建的列别，若未指定则为DEFAULT

　　USED

　　是否使用过

　　TIMESTAMP

　　创建的时间

　　VERSION

　　创建时的数据库版本

　　SQL_TEXT

　　SQL查询语句

　　DBA_OUTLINE_HINTS：

　　NAME

　　OUTLINE名，若创建时未指定，则使用系统命名

　　OWNER

　　创建时的方案名

　　NODE

　　提示应用的层次，从最外层查询（1）开始累加计数

　　STAGE

　　提示应用的阶段，即提示在编译的哪个阶段写入

　　JOIN_POS

　　提示应用的表名，对非访问方式提示为0

　　HINT

　　提示

　　11.4 创建存储概要

　　相关的权限

　　CREATE ANY OUTLINE – 创建概要，否则报ORA-18005错误

　　ALTER ANY OUTLINE – 修改或重新计算概要

　　DROP ANY OUTLINE – 删除概要

　　EXECUTE ON OUTLN_PKG – 执行OUTLINE包

　　注意这里权限都是全局的，概要不存在真正的所有者。

　　使用DDL

　　CREATE <OR REPLACE> OUTLINE OUTLINE_NAME

　　<FOR CATEGORY CATEGORY_NAME>

　　ON STATEMENT

　　使用ALTER SESSION

　　ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE；

　　ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE；

　　ALTER SESSION SET CREATE_STORED_OUTLINES = mycategory；

　　当设为TRUE时，所创建的概要归类入DEFAULT.

　　11.5 OUTLN用户

　　所有8i数据库中均缺省创建，缺省密码为OUTLN，并可在安装后立即更改。方案含有两个表和一些索引，存放于SYSTEM表空间中，若需要大量使用概要，可用如下方法转移表空间（其中一张表含有LONG类型字段，无法ALTER TABLE MOVE）。

　　EXP USERID=OUTLN/OUTLN OWNER=OUTLN

　　ALTER USER OUTLN DEFAULT TABLESPACE tools；

　　REVOKE UNLIMITED TABLESPACE FROM OUTLN；

　　ALTER USER OUTLN QUOTA 0K ON SYSTEM；

　　ALTER USER OUTLN QUOTA UNLIMITED ON tools；

　　DROP TABLE ol$；

　　DROP TABLE ol$hints；

　　IMP USERID=OUTLN/OUTLN FULL=YES

　　若系统已经使用了概要，则操作应尽量在单用户模式下执行，数据库无其它活动终端用户。

　　11.6 在数据库间转移概要

　　EXP USERID=OUTLN/OUTLN QUERY=“where category=‘test’” tables=（ol$， ol$hints）

　　IMP USERID=OUTLN/OUTLN FULL=Y IGNORE=YES

　　这里也可以使用参数文件来定义导出的查询条件。

　　11.7 获得正确的概要

　　有时仅修改某些参数是无法获得所需要的执行计划的，还要添加提示。但概要的使用是基于相同的SQL文本，为了不修改应用但使用添加了提示的执行计划，可以采用如下方法：

　　例如需要SELECT FROM （SELECT /*+ use_hash（emp） */ FROM emp） emp，

　　（SELECT /*+ use_hash（dept） */ FROM dept） dept

　　WHERE emp.deptno=dept.deptno；

　　则可以在另一个方案中删除emp、dept表，将内层查询语句建立成名为emp和dept的视图，然后对SELECT * FROM emp，dept WHERE emp.deptno=dept.deptno； 建立概要。则此后可以指定应用使用此概要（SQL文本一致）。

　　这也是利用了OPS是全局的，并不关心所引用对象，而是纯粹根据SQL文本进行转换。

　　11.8 管理概要

　　通过DDL

　　ALTER OUTLINE outline_name RENAME TO new_name；

　　ALTER OUTLINE outline_name CHANGE CATEGORY TO new_category_name；

　　ALTER OUTLINE outline_name REBUILD；

　　DROP OUTLINE outline_name；

　　OUTLN_PKG包

　　作用：提供批量管理的功能；提供EXP/IMP的API

　　由DBMSOL.SQL和PRVTOL.PLB脚本（%ORACLE_HOME%/RDBMS/ADMIN）创建，而这两个脚本由CATPROC.SQL调用并缺省安装到数据库。

　　DROP_UNUSED – 删除所有类别中所有未使用的概要。

　　EXEC OUTLN_PKG.DROP_UNUSED；

　　DROP_BY_CAT – 删除指定类别中的所有概要。

　　EXEC OUTLN_PKG.DROP_BY_CAT（category_name）；

　　UPDATE_BY_CAT – 重命名一个类别或将其合并入另一个类别。

　　EXEC OUTLN_PKG.UPDATE_BY_CAT（old_category_name， new_category_name）； 若新名已被用，则合并，且若新旧类别存在相同SQL文本的概要，保留新类别中的，而此重复的概要仍保留于原类别中。

　　11.9 最后说明

　　创建概要需要CREATE ANY OUTLINE权限，若无权限，利用ALTER SESSION方式来创建概要时不会提示错误，但不会创建概要。

　　删除用户时即便指定CASCADE选项，也不会删除其创建的概要。

　　如果CURSOR_SHARING设为force，则用DDL和ALTER SESSION两种方法获得的SQL文本可能是不同的，前者就是输入的SQL，而后者是系统内部已经转换过绑定变量的SQL.

　　概要的使用依靠文本完全匹配，即便是大小写不同也会造成SQL文本不匹配。

　　OR扩展问题：由于WHERE条件中有OR的SQL会被改写为UNION ALL模式，概要记录的提示可能无法正常使用，而只是作用到第一个条件上。因此要注意USER_OUTLINE_HINTS表中HINT LIKE ‘USE_CONCAT%’的概要和提示，应当删除或移走。

　　使用概要对性能影响很小。创建概要时接近首次分析该条语句的时间，此后第一次分析慢于正常分析时间，而随后概要已经进入缓存，将不会观察到性能影响。

　　11.10 可能遇到的错误

　　ORA-18001 – 使用ALTER OUTLINE语法错误

　　ORA-18002 – 所引用的概要不存在（从未创建过或者被删除）

　　ORA-18003 – 概要的数字签名已存在，数字签名用于快速查找到合适的概要，此错误极少发生

　　ORA-18004 – 概要已存在，一般是命名冲突

　　ORA-18005 – 需要CREATE ANY OUTLINE权限

　　ORA-18006 – 需要DROP ANY OUTLINE权限

　　ORA-18007 – 需要ALTER ANY OUTLINE权限