第 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权限
