• Oracle Database
• 2013.12.23
• 渡部 亮太

ash_wait_chains.sql from TPT Scripts : Display ASH wait chains

このエントリは (全部俺) Oracle ACE Director Tanel Poder Advent Calendar 2013 23日目の記事です。

Advanced Oracle Troubleshooting Guide - Part 11 ：ash_wait_chains.sqlを用いた複雑な待機チェーンのシグネチャ分析 で使われていたash_wait_chains.sqlについて書きます。

ash_wait_chains.sql は、ASH(v$active_session_history)から情報を取得し、該当セッションを待機させている（ブロックしている）セッションを示すblocking_session列を利用して、セッション間の待機関係を表示するスクリプトです。 このスクリプトの動作については、 Advanced Oracle Troubleshooting Guide - Part 11 ：ash_wait_chains.sqlを用いた複雑な待機チェーンのシグネチャ分析で説明されています。上記エントリでは、このスクリプトがEXPERIMENTALな扱いであることが協調されていますが、ash_wait_chains.sqlスクリプト（v0.2）でbuffer busy waitsを診断するでも触れられているとおり、2013年12月22日時点では、バージョン0.1から0.2にバージョンアップされ、BETA扱いに昇格！しています。 🙂

内部実装についていくつかメモ書きしておきます。

階層問合せで待機関係をトラバース

このスクリプトの発想はシンプルなもので、v$active_session_historyのblocking_sessionとsession_idを親子関係に見立ててやれば、階層問合せを使用することで、セッション間の待機関係を表すことができるであろうというものです。

スクリプトから階層問合せの箇所を抜粋します。

chains AS (
    SELECT
        sample_time ts
      , level lvl
      , session_id sid
      --, SYS_CONNECT_BY_PATH(&1, ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN '('||d.session_id||')' ELSE NULL END path
      , REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ') path -- there's a reason why I'm doing this (ORA-30004 🙂 <= (*3)
      , CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids
      , CONNECT_BY_ISLEAF isleaf
      , CONNECT_BY_ISCYCLE iscycle
      , d.*
    FROM
        ash_samples s
      , ash_data d
    WHERE
        s.sample_id = d.sample_id 
    AND d.sample_time BETWEEN &3 AND &4
    CONNECT BY NOCYCLE
        (    PRIOR d.blocking_session = d.session_id  <== (*2)
         AND PRIOR d.blocking_inst_id = d.inst_id     <== (*2)
         AND PRIOR s.sample_id = d.sample_id          <== (*2)
        )
    START WITH &2 <== (*1)
)

(*1) の箇所で、階層のルートとなる行を指定しています。条件はスクリプトの第2引数("&2")として与えられます。機能的には、待機させられているセッション、被害者となっているセッションを絞り込む形で指定することが想定されています。例えば、ある特定のユーザーを示す条件 username='SYS'や、フォアグラウンドセッションを示す条件、session_type='FOREGROUND'が考えられます。特に条件を指定しない場合は、1=1を指定する必要があることに注意してください。

(*2)の箇所で、階層問合せの親子関係を示す条件を指定しています。機能的には、ブロックされているセッションに対応する行のblocking_session列から、ブロックしているセッションのsession_idを親→子という方向でたどることで、待機関係をたどるようになっています。

(*3)の箇所で、SYS_CONNECT_BY_PATH でデータをつないで出力しています。機能的には、セッションの待機関係を' -> ' で繋いで表示しています。左にある方がブロックされているセッション、すなわち、被害者のセッションです。 （意図しないところで ORA-30004 が出される問題があったのでしょうか？ 一旦 データを'->'でつないでから、REPLACEで'->'を' -> 'に文字列置換しています）

上記の階層問合せは、WITH内に記載されており、chains という名前で参照されます。 メインの問合せブロックでは、chainsの結果セットを待機チェーンごとに集約して統計情報（比率と秒数）を返します。

SELECT
    LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This" <== (*5)
  , COUNT(*) seconds
  , path wait_chain
  -- , COUNT(DISTINCT sids)
  -- , MIN(sids)
  -- , MAX(sids)
FROM
    chains
WHERE
    isleaf = 1
GROUP BY
    &1    <== (*4)
  , path  <== (*4)
ORDER BY
    COUNT(*) DESC
/

(*4)の箇所で、chainsの結果セットを集約するキーを指定しています。キーはスクリプトの第1引数とpath((*3)で得られるセッションの待機関係の文字列表示)です。スクリプトの第1引数は省略できないので、event2などを指定してください。

(*5)では、分析関数RATIO_TO_REPORT を使用して、全体に対する比率を求めています。

このスクリプトは非常に有用ですが、実際の待機状態を適切に得られるかどうかは、ASHのblocking_session列に適切に値が設定されているかどうかに依存することに注意してください。 ash_wait_chains.sqlスクリプト（v0.2）でbuffer busy waitsを診断するでも触れられていますが、一部の待機イベントについては、ASHのblocking_session列に適切に値が設定されないようですので、状況によっては他の方法も併用することが安全でしょう。もしよろしければ、http://co-sol.jp/techdb/2013/05/db_tech_showcase_2013_analyzeing_oracle_database_hang_issues.htmlなどでご紹介した方法もご活用ください 🙂