Oracle笔记-优化器计划稳定性

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