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;