今回はOracleの待機イベントについて調査、検証しました。
待機イベントとは
プロセス（セッション）が処理できず（CPUを使用できず）、他の処理が完了するのを待っているイベント（出来事、アクション）です。
たとえばプロセスA（セッションA）に対して「db file sequential read」という待機イベントが発生している場合、現在ディスクのランダムアクセスが発生しているため、プロセスA（セッションA）は「db file sequential read」が完了するのを待っているということになります。
プロセス（セッション）に待機が発生した場合、待機イベント情報として待機理由や待機時間、待機回数などが記録されます。
主な待機クラス
待機クラスは10gから導入された概念で、待機イベントを特性ごとにグルーピングしたものです。
AWRレポートなどを使用して性能調査する場合、一般的に先ず待機クラスを見た方が良いと言われています。
待機クラスの状況からシステムの傾向やボトルネックのあたりをつけて調査範囲を絞り込むことができるため、いきなり待機イベントを見るより効率的だと言われています。
主な待機イベント
待機イベントは1000種類以上あると言われていますが、その中でも私が今までの現場で良く見た待機イベントを載せます。
enq: TX - row lock contention
log file sync / log file parallel write
buffer busy waits
library cache lock
free buffer waits
log buffer space
SQL*Net message from client
本待機イベントは「SQL文が発行されていない」という状態のため、通常無視して問題ありません。
SQL*Net message to client
SQL*Net more data from client
「SQL*Net message from client」待機イベントとの違いは、クライアントからSQL文が送られてきている途中で送信が滞ってしまったなど。
Oracleはパケットを複数回やり取りすることもあるため、途中で送受信が滞ってしまった場合はこの待機イベントが発生します。
SQL*Net more data to client
db file parallel write
【補足】
データベース・バッファ・キャッシュのバッファ状態
バッファには4つの状態があります。
空き（未使用）
データが読み込まれていない空のバッファ
確保済み（PIN）
読み書き中のバッファ。このバッファにアクセスできるセッションは1つだけであり、別のセッションはアクセスできるまで待機
クリーン
データの内容がデータファイルと一致しているバッファ。CRバッファ（読み取り一貫性）もこの状態
使用済み
データファイルに未だ書き込まれていないバッファ。データの内容がデータファイルと一致していないバッファ。書き込まれたあとは「クリーン」に遷移
db file scattered read
データベース・バッファ・キャッシュ上に単一ブロックが分散的（不連続）に配置されるため、「scattered」となる
db file sequential read
データベース・バッファ・キャッシュ上に複数ブロックが連続に配置されるため、「sequential」となる
direct path read
大規模表（データベース・バッファ・キャッシュにキャッシュされないブロック）を直接PGAに配置することで下記メリットがある
・データベース・バッファ・キャッシュにブロックを配置する処理が省略できる
・大量のブロックがデータベース・バッファ・キャッシュに配置されることで既存のブロックがエージアウトされることを防ぐ
待機イベントを確認できる主なビュー
v$event_name
待機イベント一覧の情報
v$session_wait
セッションごとの現在待機している待機イベント（待機状態）
※10g以降はv$sessionにV$SESSION_WAITの列情報も包含されているため、個別でV$SESSION_WAITを見る必要はなくなった
v$system_event
インスタンス起動後の待機イベント（累積値）
v$session_event
セッションごとの待機イベント（累積値）
待機イベントを発生、確認する検証をしてみました。
■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)
■検証パターン
①待機イベント（enq: TX - row lock contention）発生
■検証
①待機イベント（enq: TX - row lock contention）発生
複数のセッションで同一の行をUPDATEする場合、一方のセッションがCOMMITするまでもう一方のセッションは待機イベント（enq: TX - row lock contention）が発生するか検証します。
【検証手順】
1. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
2. [セッション1 / アプリケーションユーザ] EMP表のEMPNO = 7369の行をUPDATE（COMMITしない）
3. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
4. [セッション2 / アプリケーションユーザ] EMP表のEMPNO = 7369の行をUPDATE（COMMITしない）
5. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
6. [セッション1 / アプリケーションユーザ] COMMIT
7. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
【作業ログ】
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');
SID USERNAME EVENT STATE
---------- -------------------- ---------------------------------------- ---------------
15 HR SQL*Net message from client WAITING
275 SCOTT SQL*Net message from client WAITING
★クライアントからリクエスト待ち
2. [セッション1 / アプリケーションユーザ] EMP表のEMPNO=7369の表をUPDATE（COMMITしない）
SQL> update emp set sal = 9999 where empno = 7369;
1 row updated.
3. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');
SID USERNAME EVENT STATE
---------- -------------------- ---------------------------------------- ---------------
15 HR SQL*Net message from client WAITING
275 SCOTT SQL*Net message from client WAITING
4. [セッション2 / アプリケーションユーザ] EMP表のEMPNO=7369の表をUPDATE（COMMITしない）
SQL> update SCOTT.emp set sal = 9999 where empno = 7369;
★応答なし
5. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');
SID USERNAME EVENT STATE
---------- -------------------- ---------------------------------------- ---------------
15 HR enq: TX - row lock contention WAITING
275 SCOTT SQL*Net message from client WAITING
★HRが「enq: TX - row lock contention」イベントで行ロック取得を待機している
6. [セッション1 / アプリケーションユーザ] UPDATE後のコミット
SQL> commit;
Commit complete.
7. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');
SID USERNAME EVENT STATE
---------- -------------------- ---------------------------------------- ---------------
15 HR SQL*Net message from client WAITING
275 SCOTT SQL*Net message from client WAITING
★HRが行ロックを取得できたため、「enq: TX - row lock contention」イベントは解消した
■参考資料
Oracle待機イベント
待機イベントの説明
Oracleの現場を効率化する100の技
オラクルマスター教科書 Gold DBA Oracle Database AdministrationⅡ
オラクルマスター教科書 ORACLE MASTER Expert パフォーマンス・チューニング編
データベースの限界性能を引き出す技術
新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意
■おわりに
待機イベントは「待機」というキーワードからネガティブなイメージが持たれやすいですが、SQL文を処理する都合上、必要な待機時間、無視できる待機時間もあります。（もちろん無駄な待機時間もありますが）
無駄な待機時間から待機時間（待機イベント）の原因を特定し、チューニング（SQL文のロジック修正やハードウェアのスケールアップなど）してくのが基本的な対処方法になります。
