Oracle Database
2013.05.21
渡部亮太

Excelを用いたASH分析方法の紹介とdb tech showcase 大阪 2013セッション担当のお知らせ

ASHは主にパフォーマンス分析に使用されるツールですが、適切でない動作を調査するなどのトラブルシューティングにも使用できます。

パフォーマンス分析においてASHを活用した例でお馴染みなのは、Oracle Enterprise Managerの Average Active Session画面でしょう。この画面は、ある特定の時刻や、一定の時間帯において、インスタンス内の全セッションのうち、"Active"なセッションの総数と、セッションの状態（待機イベントやCPUを使用しているか）に着目することにより、負荷の大きさ(=総和)や、負荷全体における要因の内訳(=比率)を直感的に理解できる点が優れています。

http://docs.oracle.com/cd/E16338_01/server.112/b56313/tdppt_realtime.htm#CHDIHEBJ

リアルタイムなデータベースのパフォーマンスの監視 via kwout

しかし、トラブルシューティングの場合は、パフォーマンス分析とは異なる観点でASHを捉えるやり方がさらに役に立ちます。特定のセッションの待機イベントが時系列でどのように変化したか、特定のセッションの変化にしたがって、ほかのセッションの状態はどのように変化したか。などに注目できる図が求められます。

端的にいってしまうと、 ASH Visualizations: R, ggplot2, Gephi, Jit, HighCharts, Excel ,SVG に記載されている http://dboptimizer.com/wp-content/uploads/2012/01/Akj6X4.png のような、インスタンス内の各セッションにおける待機イベント発生状況の推移をビジュアル的にわかりやすく表示した図がほしいわけです。

http://dboptimizer.com/2012/01/22/ash-visualizations-r-ggplot2-gephi-jit-highcharts-excel-svg/

DB Optimizer ≫ ASH Visualizations: R, ggplot2, Gephi, Jit, HighCharts, Excel ,SVG via kwout

上で示した図は Greg Rahn氏が統計解析向けプログラミング言語Rで作成したようです。しかし、残念ながら作成方法について書かれた情報を確認することはできませんでした。また、残念ながら、私は統計解析向けプログラミング言語Rを知らないため、方法を推測するや実際にやってみることは難しそうです。

そこで、Excelの機能を使って、インスタンスの複数セッションの待機イベント発生状況の推移をビジュアル的にわかりやすく表示する方法を考えたので、これを紹介します。

なお、説明する手順を行うと、以下のような図が得られます。

ASHをExcelにエクスポートする

ASHのデータをExcelに取り込みます。あらかじめ、ASHの実体であるDBA_HIST_ACTIVE_SESS_HISTORYビューから、注目したい時間帯のデータを別の表に抽出しておき、その表をSQL Develooperの機能を使ってExcelにエクスポートするのが簡単でしょう。

DBA_HIST_ACTIVE_SESS_HISTORYビューから、注目したい時間帯のデータを別の表に抽出するSQLは以下のようになります。

define srctab=dba_hist_active_sess_history
define ashtab=exp_hist_active_sess_history 
define start_time='13-05-13 13:36:00'
define end_time  ='13-05-13 13:38:00'

drop table &ashtab;
create table &ashtab
as select * from &srctab
where to_timestamp(sample_time) 
between to_timestamp('&start_time','yy-mm-dd HH24:MI:SS')
    and to_timestamp('&end_time','yy-mm-dd HH24:MI:SS');

このSQLで作成した表をExcelにエクスポートします。

ASHデータの縦横変換について

ASHのデータはV$SESSIONのデータを1秒間隔で収集し、1/10にサンプリングしたものです。 ASHのデータをキーという観点でみると、V$SESSIONのキーであるSESSION_ID列とSERIAL#列に加えて、サンプリング時間を示すSAMPLE_TIMEで構成される複合キーを持つといえます。

← キー ─────────────────→ ←─ 属性 ─────────────────→
SAMPLE_TIME       SESSION_ID SESSION_SERIAL# (さまざまな列) EVENT           (さまざまな列) 
----------------  ---------- ---------------                ---------------
2013/05/20 22:00           1             101                待機イベントA
2013/05/20 22:00           2             102                待機イベントB
2013/05/20 22:00           3             103                待機イベントC
          :                ;               :                  :
2013/05/20 22:00          99             199                待機イベントD
2013/05/20 22:10           1             101                待機イベントA
2013/05/20 22:10           2             102                待機イベントB
2013/05/20 22:10           3             103                待機イベントC
          :                ;               :                  :
2013/05/20 22:10          99             199                待機イベントX
2013/05/20 22:20           1             101                待機イベントA
2013/05/20 22:20           2             102                待機イベントB
2013/05/20 22:20           3             103                待機イベントC
          :                ;               :                  :
2013/05/20 22:20          99             199                待機イベントX

しかし、それぞれのセッションを時系列で捉えるためには、上記の表データを縦横変換する必要があります。具体的には縦軸にセッションの識別子であるSESSSION_IDとSESSION_SERIAL#が、横軸にSAMPLE_TIMEの値、縦横が交わるところにはEVENTの値がくるように並べたいのです。

SESSION_ID SESSION_SERIAL# 2013/05/20 22:00 2013/05/20 22:10 2013/05/20 22:20 
---------- --------------- ---------------- ----------------  ----------------
         1             101 待機イベントA    待機イベントA    待機イベントA    
         2             102 待機イベントB    待機イベントB    待機イベントB    
         3             103 待機イベントC    待機イベントC    待機イベントC    
         :               :   :                :                ;              
        99             199 待機イベントX    待機イベントX    待機イベントX

このように並べ替えることで、セッションの待機イベントの推移を時系列で捉えやすくなります。

しかし、この並べ替え作業、横軸に並ぶ値の数（2013/05/20 22:00, 2013/05/20 22:10, 2013/05/20 22:20 , ...)がデータによって異なるため、意外とやりにくいです。しかし、Excelのピボットテーブルの機能を使うと、比較的簡単に並べ替えを行えます。

ピボットテーブルで縦横変換

Ctrl-Aを押してASHデータを選択し、挿入→ピボットテーブルを選択してピボットテーブルを作成します。作成したピボットテーブルを後から参照するため、ピボットテーブルが作成されたシートの名称を"Pivot1"と変更しておきます。

今回Excel 2010を使用しています。私はExcel 2003のピボットテーブル操作方法に慣れているため、ピボットテーブルを右クリック→オプションを選択して、以下のオプション指定を行います。

「集計とフィルター」→「行の総計を表示する」OFF、「列の総計と表示する」OFF
「表示」→「従来のピボットテーブルレイアウトを使用する（グリッド内でのフィールドのドラッグが可能）」

右に表示されるフィールド一覧から、データ項目を選択して、縦軸、横軸、データにドラッグアンドドロップします。対応は以下のとおりです。

縦軸(列ラベル): SESSION_ID, SESSION_SERIAL#, PROGRAM
横軸(列ラベル): SAMPLE_TIME
データ(値): EVENT_ID ※:2013/05/22追記：ドロップ後、左クリック→値フィールドの設定→集計方法にて最大値を選択します。

これとあわせて、不要な小計表示を削除します。

データ箇所にEVENTではなくEVENT_IDを指定する理由は、Excelのピボットテーブルの制限のためです。 Excelのピボットテーブルは、データ(値)の箇所に文字列データをおくと、これをうまく扱うことができません。数値であればうまく扱えるので、数値データであるEVENT_IDを指定します。（あとでEVENTに変換します)

待機イベント名が表示されるように

ASHデータの縦横変換はできましたが、データ箇所にEVENTではなくEVENT_IDを指定したために、待機イベント名が表示されません。これでは待機イベントの推移を直感的に追いにくいので、待機イベント名が表示されるように工夫します。

シートを1つ追加して、ここに以下のSQLの結果を貼り付けます。

SELECT event_id, name FROM V$EVENT_NAME;

このデータはあとで参照します。参照しやすくするため、貼り付けたセル範囲に名前を付けておきます。ここでは、"eventlist"としておきます。

http://kokoro.kir.jp/excel/name.html

このデータを参照して、待機イベント名を表示できるようにします。そのために、Excelのセル参照と関数を活用して、ピボットテーブルのデータにコピーしつつ、変換をかけます。若干力技ですが、以下の手順を踏みます。（もっとよいやり方があるかもしれない・・・）

シートを1つ追加する。
ピボットテーブルの縦軸、横軸の見出しに相当する箇所(下図の赤枠の箇所)に以下の数式を書きます。
```
=IF(Pivot1!<セル参照形式>="","",Pivot1!<セル参照型式>)
```
セルA1に書くべき数式は
```
=IF(Pivot1!A1="","",Pivot1!A1)
```
になります。 A1に上記の数式を入力し、フィルハンドルで他のセルにドラッグするのが簡単でしょう。
ピボットテーブルのデータに相当する箇所(下図の青枠の箇所)に以下の数式を書きます。
```
=IF(Pivot1!<セル参照型式>="","",VLOOKUP(Pivot1!<セル参照型式>, eventlist,2))
```
セルD5に書くべき数式は
```
=IF(Pivot1!D5="","",VLOOKUP(Pivot1!D5, eventlist,2))
```
となります。 D5に上記の数式を入力し、フィルハンドルで他のセルにドラッグするのが簡単でしょう。

条件付書式で色分け

と、ここまでの作業で表データの縦横変換は完了です。あとは、待機イベントの推移をより見やすくするために条件付き書式を適用してみます。

データ箇所を選択して、セルのデータがある待機イベント名であったら色分け表示するように、条件付き書式を追加してゆきます。

データ箇所を何度も選択することになるので、セル範囲に名前をつけておくのがよいでしょう。

あまりスマートな手順ではありませんが、あまり一般的でないソフトウェアを導入することなく、できたと思います。正直何度も何度もやりたくはない手順ではありますが、たまにやるなら耐えられる面倒さかなと思ってます。（多分何度も何度もやる羽目になったら、ツールを作るかRを活用する方法を検討するでしょう・・・）

db tech showcase 大阪 2013でセッションを担当します

インサイトテクノロジー社様が主催するdb tech showcase 大阪 2013にてセッションを1コマ担当させていただくことになりました！セッションのタイトルは、 "Analyzing Oracle Database hang issues using various diagnostics."です。

OS/ハードウェアなどの外的な要因やBugによって、ハングに類似した状況が発生した場合に、問題を特定したり絞込んだりする方法について説明させていただきます。セッションの中で、今回ご紹介したExcelを用いたASH分析方法についても触れる予定ですが、セッション時間が45分と短く、操作方法まで説明する時間がありません。このため、操作方法を説明すべく、このエントリを書いてみました。参考になれば幸いです。

また、ハング分析やOracle Databaseの診断機能にご興味がある方は、ぜひセッションに足をお運びください！