Statspackとは Statspack設定 statspackでの情報収集 statspackレポートの出力 便利スクリプト Oracleチューニング講座 第5章　待機イベントを理解してチューニングをしよう(1) 4章ではデータベースの稼動状況を示す「動的パフォーマンス・ビュー」を紹介しましたが、本章では「待機イベント」と呼ばれる、OracleのOracleは待機イベントを使用して、データベースの待機時間を管理します。待機イベントを理解することによって、問題の核心を的確につかみ、ボトルネックとなっている箇所をチューニングできます。 待機イベントとは 待機イベントは、プロセスが何らかの作業を待機していることを示します。待機イベントが発生すると、その待機イベントの待機時間が記録されます。 例）ブロック競合による待機 　　 チェックポイント遅延による待機　など 待機イベントの情報は、システムの問題点を顕著に示します。 パフォーマンスに問題があるシステムを診断する際は、まずは発生している待機イベントを調査することをお薦めします。 1） 待機イベントの種類 　 待機イベントの種類は、R10.1で約800あります。 これらは、主に以下のように分類できます。 ・アイドル待機イベント 　アイドル待機イベントは必ず発生するものであり、これらの情報は排除できます。 ・非アイドル待機イベント 　アイドル待機イベント以外の待機イベントです。チューニングの対象となります。 ※待機イベントはバージョンによって追加・削除されたり、名前が変更されている場合があります。本講座で 　は全てR10.1の名称を使用しています。 表1に代表的な待機イベントの説明を記述します。 表1：代表的な待機イベント一覧 待機イベント名 説明 direct path write direct path write temp ソート処理では、データベース・バッファ・キャッシュをバイパスしてプロセスがバッファをPGAから直接書込む（ダイレクト処理）。その際、書込みコールが完了するまでこのイベントを待機することがある。メモリー上でソートができるようにチューニングする。 enqueue エンキューによる待機。TM（DMLエンキュー）、TX（トランザクション・エンキュー）、UL（ユーザー定義エンキュー）などがある。 free buffer waits データベース・バッファ・キャッシュに空きバッファがない場合に発生する待機。データベース・バッファ・キャッシュが小さすぎるか、DBWRの書込みが遅延していることを示す。 latch free ラッチ競合が発生していることを示す。V$LATCHで詳細を調べる。 latch: library cache ライブラリ・キャッシュ・ラッチによる競合。ライブラリ・キャッシュ・ミスを減らす。 latch: library cache lock ライブラリ・キャッシュ・ロック・ラッチによる競合。ライブラリ・キャッシュ・ミスを減らす。 latch: cache buffers chains cache buffers chainsラッチはバッファ・キャッシュでバッファ・リストを保護する場合に使用される。このラッチの待機はアクセス頻度が高いホットブロックが存在することを意味する。ブロック競合を解消する。 latch: cache buffers lru chain キャッシュ内のバッファのリストを保護するラッチの競合。複数バッファ・プールを利用するとラッチ数が増加するため、競合が減少する可能性がある。 library cache load lock ライブラリ・キャッシュ内にオブジェクト（ライブラリ・キャッシュに格納される情報）をロードするときに必要なロックを待機した。ライブラリ・キャッシュ・ミスを減らす。 library cache lock library cache pin 複数のプロセスがライブラリ・キャッシュに同時にアクセスしていることに関連する待機。ライブラリ・キャッシュ・ミスを減らす。 log buffer space REDOログ・バッファがいっぱいでサーバー・プロセスが書込みを待機。ログ・バッファが小さすぎるか、LGWRの書込みが遅い（I/Oの問題）。 log file parallel write LGWRプロセスのREDOログ・ファイルへの書込みに関する待機。ログ・ファイルへの書込み負荷が高い可能性がある。 log file single write REDOログ・ヘッダー書込みに関連する待機。チェックポイント中に発生しやすい。 log file switch (checkpoint incomplete) チェックポイントが完了していないため、まだリカバリに必要なオンラインREDOログ・ファイルをLGWRが上書きしなければならなくなった際に発生する待機。チェックポイントが完了するまで上書きを待機する。通常、REDOログ・ファイルのサイズが小さすぎるか、ログ・ファイルのグループ数が少なすぎるために発生する。 log file switch completion ログ・スイッチ時に発生する待機。実際に発生したログ・スイッチの回数よりも待機した回数が多い場合は、I/O負荷が高い可能性がある。 log file switch (archiving needed) アーカイブが終了していないに関わらず、まだ必要としているREDOログ・ファイルを再利用しなければならなくなったときに発生する待機。 主に以下の理由が考えられる。 ・LGWRとの競合で読込みが低速。 ・アーカイブ先のディスクが低速。 ・アーカイブ先に空き領域がない。 log file sync COMMITまたはROLLBACKするとLGWRプロセスがREDO情報をREDOログ・ファイルにフラッシュする。COMMITまたはROLLBACKを実行するプロセスは、LGWRプロセスが書出し中だった場合、その書出しが完了するまで待機する。COMMITの回数が多すぎるか、I/O負荷が高いのが原因。 write complete waits サーバー・プロセスがアクセスしようとしたブロックが、DBWRプロセスによって書出し中だったときに発生する待機。DBWRの書出し遅延によって発生する場合がある。チェックポイント中に発生しやすいため、待機が長い場合はチェックポイントの頻度を調整する。 待機イベントの確認 待機イベントに関する情報は、主にV$SYSTEM_EVENT、V$SESSION_EVENT、V$SESSION_WAITビュー（R10.1からはV$SESSIONビュー）によって確認できます。 V$SYSTEM_EVENTビューでインスタンス・レベルで調査し、V$SESSION_EVENT、V$SESSION_WAITビューを使用してセッション・レベルの調査を行います。 1） V$SYSTEM_EVENTビュー 　 V$SYSTEM_EVENTビューには、インスタンスを起動してから現在までに発生した待機がすべてまとめられています。このビューによって、データベース全体で問題となっている待機イベントを簡単に特定できます。 ※発生していない待機イベントに関しては表示されません。 ◆ V$SYSTEM_EVENTビュー インスタンスで発生した待機イベントに関する情報を示します。 <主な列> ・EVENT ・TOTAL_WAITS ・TOTAL_TIMEOUTS ・TIME_WAITED ・AVERAGE_WAIT ・TIME_WAITED_MICRO ・EVENT_ID ：待機イベント名 ：イベントの合計待機回数 ：イベントの合計タイムアウト回数 ：イベントの合計待機時間（1/100秒） ：イベントの平均待機時間（1/100秒） 　TIME_WAITED/TOTAL_WAITSの値 ：イベントの合計待機時間（1/1000秒） ：待機イベントの識別子 ◆ 時間測定機能の設定 TIMED_STATISTICSパラメータをTRUEに設定すると、時間測定機能が設定されます。これにより様々な動的パフォーマンス・ビュー（V$）の時間統計も示されるようになります。そのため、このパラメータは常にTRUEに設定しておくことをお薦めします。 TIMED_STATISTICSパラメータ 概要 時間に関する統計情報を収集するかどうかを制御　　　　　　　　 構文 TIMED_STATISTICS = { FALSE | TRUE } デフォルト R9.0.1まで：FALSE R9.2以降 ：STATISTICS_LEVELパラメータがTYPICALまたはALLに設定されている 　　 　　 　 場合はTRUE、BASICの場合はFALSE 変更の可/不可 ALTER SESSION、ALTER SYSTEM ※R9.2からのSTATISTICS_LEVELパラメータはデフォルトでTYPICALになっているため、結果的に 　TIMED_STATISTICSのデフォルトはR9.2以降TRUEになっています。 以下に調査例を記します。 例）V$SYSTEM_EVENTビューを参照し、発生している待機イベントを調査する SQL> SELECT event,total_waits,time_waited,average_wait 2 FROM v$system_event e,v$event_name n 3 WHERE e.event = n.name 4 AND wait_class != 'Idle' 5 ORDER BY time_waited DESC; EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT ------------------------- ---------- ---------- ----------------- class slave wait 16 167357 10460 enq: TX - row lock contention 129 38754 299 db file sequential read 7710 11820 2 db file scattered read 1201 5321 4 log buffer space 4 2161 5 ※R10.1ではV$EVENT_NAMEビューにWAIT_CLASS列が追加されたため、簡単にアイドル待機イベント 　を排除して表示できるようになりました。V$EVENT_NAMEビューには、待機イベントに関する名前や説明 　が登録されています。 NOTE ： 同じ待機イベントが複数のセッションに同時に発生している場合、実際の時間より長い待機時間になる場合があります。例えば3セッションに同時に同じ待機イベントが発生し、各セッションが1秒待機したとします。この場合、実際の時間は1秒しか経過していませんが、3秒分の待機時間が記録されます。 例）特定期間に発生した待機イベントを調査する /* 現在のV$SYSTEM_EVENTビューの情報をWAIT_EVENT表に記録する */ SQL> CREATE TABLE wait_event AS 2 SELECT e.* FROM v$system_event e,v$event_name n 3 WHERE e.event = n.name 4 AND wait_class != 'Idle'; 表が作成されました。 ・・・30分間経過したと仮定・・・ /* WAIT_EVENT表に記録した情報と現在の差分を調べるSQLを実行 */ SQL> SELECT e.event, 2 (e.total_waits-decode(w.total_waits,null,0,w.total_waits)) total_waits, 3 (e.time_waited-decode(w.time_waited,null,0,w.time_waited)) time_waited, 4 ((e.time_waited-decode(w.time_waited,null,0,w.time_waited)) 5 /(e.total_waits-decode(w.total_waits,null,0,w.total_waits))) average_wait 6 FROM wait_event w,(SELECT e.* FROM v$system_event e,v$event_name n 7 WHERE e.event = n.name 8 AND wait_class != 'Idle') e 9 WHERE w.event(+) = e.event 10 AND (e.total_waits-decode(w.total_waits,null,0,w.total_waits)) > 1 11 ORDER BY 3 desc; EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT ------------------------- ---------- ---------- ----------------- class slave wait 4 48000 12000 db file sequential read 17533 42134 2.40312553 db file scattered read　 1781 7765 4.35991016 enq: RO - fast object reuse 　 65 6859 105.523077 rdbms ipc reply 212 5545 26.1556604 log buffer space 941 4984 5.29649309 free buffer waits 1666 3239 1.94417767 ・・・省略・・・ NOTE ： 重要なのは待機イベントが発生した回数ではなく、待機時間の長さです。 　　　 　　 　　　　　　　　　　　　　　　　　1/2　第5章　待機イベントを理解してチューニングをしよう(2) へ 　　　　　　　　　　　　　　　第6章　システム統計情報とセッション統計情報を取得してみよう