Oracle笔记-优化器计划稳定性
2024-08-29 13:39:24
供稿：网友
第 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权限
