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

2/9/2008来源:Oracle教程人气:5057

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