待機イベント基礎

Oracle待機イベント:V$EVENT_NAMEによって待機イベントの種類を知る

待機イベントの数

Oracle10gR2には874個待機イベントがある。

SQL> select count(*) from v$event_name;

  COUNT(*)
----------
       874

Oracle 9iR2には401個待機イベントがある。
SQL> select count(*) from v$event_name;

  COUNT(*)
----------
       401

待機イベント大分類:

SQL> select wait_class#,wait_class,count(*) from v$event_name group by wait_class#,wait_class order by wait_class#;

WAIT_CLASS# WAIT_CLASS                                                         COUNT(*)
----------- ---------------------------------------------------------------- ----------
          0 Other                                                                   590
          1 Application                                                              12
          2 Configuration                                                            23
          3 Administrative                                                           46
          4 Concurrency                                                              24
          5 Commit                                                                    1
          6 Idle                                                                     62
          7 Network                                                                  26
          8 User I/O                                                                 17
          9 System I/O                                                               24
         10 Scheduler                                                                 2
         11 Cluster                                                                  47

※ WAIT_CLASSの分類説明::待機イベントクラス

V$SYSTEM_WAIT_CLASSによって、待機イベントクラス毎の待機回数と待機時間を調べられる。

SQL> select * from v$system_wait_class order by time_waited desc;

    WAIT_CLASS_ID       WAIT_CLASS# WAIT_CLASS                           TOTAL_WAITS TIME_WAITED
----------------- ----------------- ------------------------------ ----------------- -----------
       2723168908                 6 Idle                                     4731144  1818094075
       3875070507                 4 Concurrency                                19258       76430
       4108307767                 9 System I/O                               1081248       63951
       1740759767                 8 User I/O                                   13141        8759
       3386400367                 5 Commit                                     19510        6206
       1893977003                 0 Other                                       4467        5051
       3290255840                 2 Configuration                                221          57
       4217450380                 1 Application                                  222          11
       2000153315                 7 Network                                      134           0

V$SESSIONの進化とV$SESSION_WAIT

もっと便利にセッション待機を調べられるため、Oracle10gR1からV$SESSIONに待機イベントに関する情報を追加された。
それによってV$SESSION_WAITが余計になってしまうと言われるのだろう。

Oracle 10gのセッション待機情報強化:V$SESSION_WAIT_HISTORYビューの追加

セッション直近10回の待機イベント情報を記録する。

ASH(Active Session History) Oracle10g新機能

  • アクティブセッションの待機イベント情報を収集する。1秒に1回。
  • 隠しパラメータ_ash_enableによってASHを機能するかどうかを制御する。
  • 隠しパラメータ_ash_sampling_interalによって、収集間隔を制御する。
  • バックグランドプロセスMMNLが情報収集を行う。

ASHレコードをShared Poolのローリングバッファに保存され、V$ACTIVE_SESSION_HISTORYを問い合わせて情報を調べられる。

SQL> select * from v$sgastat where name like 'ASH%';

POOL         NAME                      BYTES
------------ -------------------- ----------
shared pool  ASH buffers             8388608

ASHレポート作成:?/rdbms/admin/ashrpt.sql

SQL> @?/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1076419146 ATOM                1 atom

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text
... ... ... ...

TOP10待機イベント:V$SYSTEM_EVENT

Oracle10gR2開発環境

SQL> select * from (select event,wait_class,time_waited from v$system_event order by time_waited desc ) where rownum <= 10;

EVENT                                                            WAIT_CLASS                     TIME_WAITED
---------------------------------------------------------------- ------------------------------ -----------
rdbms ipc message                                                Idle                            1279165020
pmon timer                                                       Idle                             118947822
Streams AQ: qmn coordinator idle wait                            Idle                             118615981
Streams AQ: qmn slave idle wait                                  Idle                             118615180
smon timer                                                       Idle                             114953842
jobq slave wait                                                  Idle                             109929624
Streams AQ: waiting for time management or cleanup tasks         Idle                              62058980
SQL*Net message from client                                      Idle                                922151
class slave wait                                                 Idle                                353523
os thread startup                                                Concurrency                          80159

10 rows selected.

Oracle 9iR2本番生産環境
SQL> select * from (select event,time_waited from v$system_event order by time_waited desc ) where rownum <= 10;

EVENT                                    TIME_WAITED
---------------------------------------- -----------
SQL*Net message from client               4006200251
rdbms ipc message                         2539164929
pmon timer                                 442395856
smon timer                                 427450073
db file sequential read                    286461315
buffer busy waits                           11420499
db file scattered read                      10316240
log file sync                                 804868
control file parallel write                   717756
log file sequential read                      571434

10 rows selected.

重要な待機イベント

db file sequential read(User I/O類)

該当待機イベントは最も一般的なI/O待機イベントとなる。一つの索引ブロックの読み込みや索引を経由してデータブロックの読み込みの時、該当待機イベントを記録される。

該当待機イベントのパラメータ:

SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name = 'db file sequential read';

NAME                                     PARAMETER1           PARAMETER2           PARAMETER3
---------------------------------------- -------------------- -------------------- --------------------
db file sequential read                  file#                block#               blocks
  • PARAMETER1:データファイルの番号
  • PARAMETER2:データファイルから読み込もうとしたデータブロック番号
  • PARAMETER3:読み込みブロックの数(通常は「1」となる)。

この待機イベントが著しくなったら、下記の原因を検討すべき:

  • テーブル結合時の順序に問題がないか?ドライブテーブル(駆動表)が正しく選択されたか。
  • 索引の使用に問題があるか?索引を使えば必ずSQLの効率が良くなることに限らないので、索引を使わなくて全表走査のほうが早いケースもある。だから索引を正しく使われているかどうかを調べたほうが良い。

きちんと開発してチューニングしたデータベースに対し、db file sequential readイベントが大量に発生しても正常だと言われる。

db file scattered read(User I/O類)

該当待機イベントも本番環境によく見られるのでしょう。該当イベントのパラメータは以下の通りだ。

SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name = 'db file scattered read';

NAME                                     PARAMETER1           PARAMETER2           PARAMETER3
---------------------------------------- -------------------- -------------------- --------------------
db file scattered read                   file#                block#               blocks

パラメータの定義はdb file sequential readイベントのと同じだ。
全表走査(Full table scan)と全索引走査(Index Fast Full Scan)によって該当待機イベントを起こさせる可能性がある。
大量なdb file scattered read待機が発生してしまえば、アプリケーションに欠陥があるか、索引を正しく作られていないかを
確認したほうが良いです。

該当待機イベントが著しく発生した場合、以下のビューを調べれば役に立つ情報を見つけられるかもしれない。

  • V$SESSION_WAIT:他のビューと結合して、問題になるSQLを特定できる。
  • V$SESSION_LONGOPS:実行時間が長いセッション(6秒以上)のセッションを調べられる。
  • V$SQL_PLAN:Oracle9iから追加され、Library Cacheの中のSQL実行計画を見つけられる。
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License