outline操作脚本_老版本.txt

这里描述的是在Oracle 数据库生产环境中由于未知的原因，使得单个sql出现严重的性能问题，在判断出是执行计划不正确引起的时，采用outlines 技术，从其它具有正确的执行计划的环境，如测试库，开发库等获取正确的执行计划，并导入到生产库中的方法。

操作步骤：

0. 可预先在各个数据库中以 sys 执行以下步骤，以便节省后续的时间：
   -- 为 outln 用户授权
   grant create any outline to outln;
   grant drop any outline to outln;
   grant all on plan_table to outln;
   -- 创建 plan_table 并创建同义词
   @?/rdbms/admin/utlxplan.sql;
   create public synonym plan_table for plan_table;
   grant select ,insert, update, delete on plan_table to public;

1．在生产库获得存在性能问题的语句的 sql hash value （若为10g，则还应考虑获得sql id值），并且获取当前执行计划。

2．找到有好的执行计划其它环境(如测试库，开发库或其它同类型的生产库)。

3．如果没有能够找到好的执行计划，需要想办法在存在好的执行计划的其它环境制造出好的执行计划，如临时删除一个不合适的索引，从而强制其使用期望的索引。

4．在存在好的执行计划的其它环境(如测试库)中解锁outln用户并修改密码及进行授权
  alter user outln identified by outln account unlock;
  -- 若以前未进行过授权操作，则进行授权
  -- grant create any outline to outln;
  -- grant drop any outline to outln;
  -- grant all on plan_table to outln;
  
  --如果没有plan_table，则以 sys 用户执行以下操作：
  --@?/rdbms/admin/utlxplan.sql;
  --create public synonym plan_table for plan_table;
  --grant select ,insert, update, delete on plan_table to public;
    该步骤中的操作，可以提前预先进行授权操作，以便在应急处理时，仅需解锁outln 用户并修改密码。

5．在存在好的执行计划的其它环境(如测试库)使用DBA角色用户(如dbmgr,dba实名,sys,system或表属主用户)授权该SQL涉及到的所有表及视图的select权限给outln用户：

   Grant select on 属主.表名 to outln;

6．在生产库解锁outln用户并修改密码
   
   alter user outln identified by outln account unlock;

7．10g 创建 outline 的特别方法
   若难于容易地直接获得匹配的sql文本，且为Oracle 10g 中，则可以使用 10g 特有的存储过程，直接根据内存中存在好的执行计划的 child cursor 获得 sql 语句创建 stored outlines。

   Alter session set create_stored_outlines=true;
   Exec dbms_outln.create_outline(&hash_value, &child_number, 'SPECIAL');
   
   然后，跳转第 9 步。

8．各版本通用的创建 outline 方法
  注意:
  以下操作务必在sqlplus工具中进行，不能在其它工具中进行。否则可能有其它意外的原因导致无法成功生成匹配的 outlines。

  8.1 在生产库获得该top sql的sql text
  若为 10g及以上，既可以按以下方法进行，也可以直接从AWR 报告中获得相应的sql 语句。（执行Ctrl-F，输入sql 语句的 sqlid 号，找到相应的文本段，进行复制。）
    然后，跳转第 8.2 步。
  
  若为9i 及以下，需要先判断语句长度是否超过1000。
    Select sum(length(sql_text)) sql_length from v$sqltext where hash_value=&hash_value;
    
    若 sql 文本长度小于1000字符，则可直接从 v$sqlarea 中获得相应的 sql 语句。
      set linesize 1004
      column sql_text format a1004
      set head off
      spool /tmp/sql1.txt
      select sql_text ||’;’ sql_text from v$sqlarea where hash_value=&hash_value;
      spool off
      这里获得的语句，可以将其分号 ";" 号后的空格去除。
      
      然后跳转第 8.2 步。
    
    若 sql 文本长度大于1000字符，则需要从v$sqltext 中获得相应的sql语句片段，并整理成一句。
      set linesize 80
      set head off
      set feedback off
      spool /tmp/sql1.txt
      select sql_text || length(sql_text) sql_text from v$sqltext where hash_value=&hash_value
      order by piece;
      spool off
      
      然后，将以上输出的sql 语句内容复制于Ultra Edit 编辑工具中，在其中对sql 进行整理：
      a)	去掉每行尾部长度的数字，除最后一行外，其它行尾部的数字一定会是64，表示该行除该数字外有64个字符。最后一行的数字也为指示该行有多少字符。
      b)	从第2行开始复制每一行粘贴到第一行的末尾，把整个sql粘贴成一行，注意首尾空格绝对不能丢失，可以在每次复制后确认光标当前所在位置来确保没有出现漏或增空格的情况。如复制并粘贴第二行后，光标应处于129的位置(64*2+1=129),表示当前行已有128个字符。以此类推。
      c)	在sql尾部增加一个;分号。      
      通过以上编辑方法，可以其语句与生产环境当前语句完全匹配。
  
  8.2 将获得的 SQL 在存在好的执行计划的其它环境如测试库确认执行计划是否是好的执行计划：
    explain plan for 
    上面获得的语句；
    
    sqlplus “/ as sysdba”  @$ORACLE_HOME/rdbms/admin/utlxpls
    若确认为其执行计划为所期望的，则继续下一步。否则，应在制造出好的执行计划后，再继续下一步。
  
  8.3 在有好的执行计划的其它环境（如测试库）创建outline：
    Create outline  for category special on 
    上面获得的SQL语句； 
  
  8.4 在有好的执行计划的其它环境（如测试库）检查outline的sql 长度和sql文本：
    set long 10000
    set pagesize 100
    set linesize 120
    select OL_NAME,TEXTLEN,SQL_TEXT from outln.ol$;
    确认长度和文本是否正确（outline可能将SQL最前面的空格截掉，像这种情况可以忽略，长度为原长度减去被截去前面空格的大小）
    
    例如 sql 原长度为1102，创建outline后的长度为1100，前面两个空格被截去，这种情况是可以忽略的，但在outline 中的语句应含有原语句尾部的空格，故，若创建的outline 后的长度为 1097（即去除了尾部的3个空格），则表示创建stored outlines 时使用的语句不正确，其会导致无法与生产环境的语句相匹配，从而outline 未能发挥作用。

9．在存在好的执行计划的其它环境（如测试库）验证使用 outline 生成正确的执行计划。
  alter session set use_stored_outlines=special;
  explain plan for 
  上面整理后的语句;
  
  sqlplus “/ as sysdba”  @$ORACLE_HOME/rdbms/admin/utlxpls
  一般而言，若是严格按照步骤 8创建的stored outlines，其执行计划应是正确的，因而，可以继续后续步骤。
  若是对10g 数据库直接由步骤 7 跳转而来，且发现其执行计划不是正确的，则应跳转步骤 8 继续进行。

10．在存在好的执行计划的其它环境（如测试库）exp 出outline（先将无关的outline 进行disable）
  在导出outline 前，应检查除了当前新建的outline 外是否还有其它outline 的存在，若存在，则可以将其disable或drop。 若为disable，则在导出后可再 enable。
  
  -- alter outline  disable;
  -- drop outline 
  exp outln/outln wner=outln file=ol.dmp log=ol_exp.log
  -- alter outline  enable;

11．在生产库将导出的dmp文件scp从测试库取到生产库主机
  scp 操作系统用户名@测试库主机:目录名/ol.dmp .

12．在生产库imp outline (8i是两个表ol$及ol$hints，9i及以上是三个表,较8i多了个 OL$NODES)
  在生产环境导入outline 前,应检查原语句是否已有相应的outlines存在，若存在，需将其 drop。
  select ol_name,category, hash_value from ol$ where hash_value=&hash_value;
  -- drop  outline ;
  imp outln/outln file=ol.dmp full=y ignore=y log=ol_imp.log

13．在生产库启用outline
  exec dbms_outln.update_signatures;
  alter system set use_stored_outlines=special;

14．在生产库检查执行计划：
  explain plan for 
  上面整理后的语句；
  
  sqlplus “/ as sysdba”  @$ORACLE_HOME/rdbms/admin/utlxpls
  执行计划应该正常

15．在生产库kill还在使用原执行计划的session，以便其重新登录后使用新计划(必要时，flush shared pool: alter system flush shared_pool;)

16．在生产库和测试库修改outln密码成复杂密码并锁定outln用户
  alter user outln  identified by  account lock;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11411056/viewspace-745446/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11411056/viewspace-745446/