优化器计划稳定性 Outlines

http://www.psoug.org/reference/outlines.html[@more@]

1.1 概览
CREATE OR REPLACE ONLINE MyOutLine
FOR CATEGORY mycategory
ON
SELECT ……；

需要CREATE OUTLINE权限,使用时指定会话的CATEGORY即可：
ALTER SESSION SET USE_STORED_OUTLINES = mycategory；

1.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语句





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

DBA_OUTLINES：
NAME OUTLINE名，若创建时未指定，则使用系统命名
OWNER 创建者
CATEGORY 创建的列别，若未指定则为DEFAULT
USED 是否使用过
TIMESTAMP 创建的时间
VERSION 创建时的数据库版本
SQL_TEXTSQL 查询语句



DBA_OUTLINE_HINTS：
NAME OUTLINE名，若创建时未指定，则使用系统命名
OWNER 创建者
NODE 提示应用的层次，从最外层查询（1）开始累加计数
STAGE 提示应用的阶段，即提示在编译的哪个阶段写入
JOIN_POS 提示应用的表名，对非访问方式提示为0
HINT 提示


1.4 创建存储概要
相关的权限
CREATE ANY OUTLINE – 创建概要，否则报ORA-18005错误
ALTER ANY OUTLINE – 修改或重新计算概要
DROP ANY OUTLINE – 删除概要
EXECUTE ON OUTLN_PKG – 执行OUTLINE包
注意这里权限都是全局的，概要不存在真正的所有者。
使用DDL
CREATE OUTLINE OUTLINE_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.


1.5 OUTLN用户
缺省创建，缺省密码为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
若系统已经使用了概要，则操作应尽量在单用户模式下执行，数据库无其它活动终端用户。
1.6 在数据库间转移概要
EXP USERID=OUTLN/OUTLN QUERY=“where category=‘test’” tables=（ol$，ol$hints）
IMP USERID=OUTLN/OUTLN FULL=Y IGNORE=YES
这里也可以使用参数文件来定义导出的查询条件。
1.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文本进行转换。
1.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文本的概要，保留新类别中的，而此重复的概要仍保留于原类别中。
1.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%’的概要和提示，应当删除或移走。
使用概要对性能影响很小。创建概要时接近首次分析该条语句的时间，此后第一次分析慢于正常分析时间，而随后概要已经进入缓存，将不会观察到性能影响。

1.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权限

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/82387/viewspace-1016322/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/82387/viewspace-1016322/