性能调优工具03–OUTLINE技术暂时锁定SQL的执行计划

7月 15th, 2014
OUTLINE技术暂时锁定SQL的执行计划

  1. Oracle的outline技术和hint技术可以在特殊情况下保证执行计划的稳定,使用outline技术锁定执行计划的场景:
    1. 短时间内无法完成sql的优化任务;
    2. 在CBO模式下,当统计信息出现问题时,导致执行计划出现变化;
    3. 由于数据库的bug导致sql的执行计划出现异常;
    4. 使用第三方的系统,sql语句无法直接修改时;
  2. OUTLINE相关的参数:
    1. CREATE_STORED_OUTLINES:会话中执行的sql语句是否自动创建并存储为OUTLINE,初始化参数;
      1. true:开启自动创建outline的功能,系统会自动指定名称并存储在DEFAULT类别下,如果在DEFAULT类别下已经存在的话不会重复创建;
      2. false:默认值,不开启自动创建功能,推荐不要修改此参数;
      3. category_name:跟true功能一样,只是默认存在category_name类别下;
    2. USE_STORED_OUTLINES:使用公有OUTLINE产生执行计划,不是初始化参数;
      1. true:使优化器使用DEFAULT类别的OUTLINE产生执行计划;
      2. false:默认值,不使用outline;
      3. category_name:是优化器使用category_name类别的outline产生执行计划;
    3. USE_PRIVATE_OUTLINES:使用私有OUTLINE产生执行计划,不是初始化参数;
      1. 参数的含义与USE_STORED_OUTLINES一致;
      2. 限制:只有USE_STORED_OULINES关闭的时候才能打开此参数;
    4. 都可以使用ALTER SYSTEM/ALTER SESSION语法来修改;
  3. 创建OUTLINE的语法解析:
    1. 创建语法:CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE outln_name [FROM PUBLIC|PRIVATE source_outline] [FOR CATEGORY category_name] [ON statement];
    2. PUBLIC:默认值,创建一个公有的OUTLINE;
    3. PRIVATE:在当前的session创建一个私有的OUTLINE,存在当前的SCHEMA下;要创建私有的OUTLINE,必须要在当前SCHEMA下使用DMBS_OUTLN_EDIT.CREATE_EDIT_TABLES过程创建一个表来存储信息;
    4. FROM子句:从一个现有的outline中创建一个新的outine,如果指定FROM子句就不用指定ON子句了;
    5. FOR CATEGORY子句:指定OUTLINE所属的分类,如果不指定就默认属于DEFAULT分类,如果指定的分类不存在,系统自动创建该分类;
    6. ON子句:要创建OUTLINE的sql语句;
      1. 当有FROM子句时不用指定;
      2. 不能是多路插入语句;
      3. 不能是远程服务器的DML操作;
    7. 修改OUTLINE:ALTER OUTLINE outln_name;
    8. 删除OUTLINE:DROP OUTLINE outln_name;
  4. 相关的包:
    1. DBMS_OUTLN:
      1. CLEAR_USED:清除outline的USED标识;
      2. CREATE_OUTLINE:使用shared pool中的sql生成一个outline;
      3. DROP_BY_CAT:删除某一个分类下的所有outline;
      4. DROP_UNUSED:删除从来没有被使用过的outline;
      5. UPDATE_BY_CAT:修改一个outline的分类;
      6. UPDATE_SIGNATURES:修改outline的signature到当前的版本;
    2. DBMS_OUTLN_EDIT:
      1. CHANGE_JOIN_POS:修改执行计划中步骤的顺序;
      2. CREATE_EDIT_TABLES:在当前schema下创建一个表用来保存私有的outline;
      3. DROP_EDIT_TABLES:删除表;
  5. 相关的视图:
    1. DBA_OUTLINE:所有的outline信息,其中USED表示此outline是否被使用过,SIGNATURE是sql语句的唯一标示符;
    2. DBA_OUTLINE_HINTS:所有outline的执行计划;
    3. 与OUTLINE相关的数据都保存了OUTLN用户下的对象中了:
      1. ALTER USER outln IDENTIFIED BY outln ACCOUNT UNLOCK;
      2. outln.ol$:outline的信息;
      3. outln.ol$hints:保存执行计划;
      4. outln.ol$nodes:OUTLINE节点信息;
  6. 创建outline;
  7. 使用outline;
  8. 清除outline:
    1. 可以使用DMBS_OUTLN包来实现删除某个分类的outline;
    2. 使用drop outline语法删除单个个outline;
  9. 把一个环境中的outline应用于其它环境:使用expdp导出当前环境中outln schema,然后impdp导入到其它环境即可;
  10. 如果想要创建一个使用变量的outline,可以在创建outline时使用绑定变量,例子;
  11. 关于HINTS:
    1. 最后才使用的方法;
    2. hints是硬编码;
    3. Hints apply to only the statement block in which they appear:只能用在第一个sql上,如果有子查询,要单独使用hints;
    4. 建议使用表的别名;
    5. HINTS写错的话,会当成注释;
————————– 创建OUTLINE ————————–
— 1.在hr用户下创建测试表;
CREATE TABLE tbobjects AS SELECT * FROM dba_objects;
— 2.创建一个outline,并属于CATE分类;
CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = 100;
— 3.查看outln.ol$hints中保存的执行计划;
SELECT hint_text FROM outln.ol$hints
WHERE ol_name = ‘OL_OBJECTS’
ORDER BY HINT#;
— 4.查看真正的执行计划;
————————– 创建OUTLINE ————————–
————————– 使用OUTLINE ————————–
— 1.在表上创建索引,来改变执行计划;
CREATE INDEX IDX_TBOBJECTS_ID ON TBOBJECTS (object_id);
— 2.查看当前实际的执行计划;
— 3.查看outline是否被使用过;
SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;
— 4.强制会话使用outline中保存的执行计划,要首先设置当前会话的CATEGORY为CATE:ALTER SESSION SET USE_STORED_OUTLINES = CATE;
— 5.查看outline是否被使用;
SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;
— 6..消除outline对sql执行计划的影响;
方法1.ALTER SESSION SET USE_STORED_OUTLINE = FALSE;
方法2.ALTER OUTLINE ol_objects DISABLE;
————————– 使用OUTLINE ————————–
————————– 创建OUTLINE时使用绑定变量 ————————–
— 1.创建一个outline

CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = :object_id;

— 2.设置当前会话使用此outline
ALTER SESSION SET USE_STORED_OUTLINES = CATE;
— 3.使用绑定变量调用;
variable object_id NUMBER;
exec :object_id := 100;
SELECT * FROM tbobjects WHERE object_id = :object_id;
— 4.查看outline是否被使用;
SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;
————————– 创建OUTLINE时使用绑定变量 ————————–
标签:
本文的评论功能被关闭了.