中国开发网: 论坛: 数据库: 贴子 869148
DeepBlue:优化器计划稳定性
第 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权限


执行力=流程+计划+组织

把理想变成计划,
把计划变成步骤,
把步骤变成行动,
把行动变成成果。

好語說盡人必易之。規矩行盡人必繁之。福若受盡緣必孤。勢若使盡禍必至。

相关信息:


欢迎光临本社区,您还没有登录,不能发贴子。请在 这里登录