作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


顶级等待事件:

前文还提到另外一个重要视图v$system_event,该视图记录的是数据库自启动以来等待事件的汇总。通过查询该视图,就可以快速获得数据库等待事件的总体概况,了解数据库运行的基本状态:

sys@CCDB> select *
  2  from (select event,time_waited
  3        from v$system_event
  4        order by time_waited desc)
  5  where rownum < 11;
EVENT                                                            TIME_WAITED
---------------------------------------------------------------- -----------
rdbms ipc message                                                 6207605140
DIAG idle wait                                                    1033906433
virtual circuit status                                             517841938
dispatcher timer                                                   517838356
Streams AQ: qmn coordinator idle wait                              517837446
Streams AQ: qmn slave idle wait                                    517833505
fbar timer                                                         517818073
smon timer                                                         517784201
pmon timer                                                         517766357
Streams AQ: waiting for time management or cleanup tasks           517763231
10 rows selected.

以上是一个测试环境中的Top 10等待事件。

在Oracle的Statspack Report中,有一部分信息为Top 5 Wait Events(在Oracle 9i中更改为Top 5 Time Events),这部分信息是来自v$system_event视图的采样。

以下是一个Statspack的诊断报告:

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3313878466 ccdb                1 26-Jan-10 16:32 11.1.0.6.0  NO

Host                             Name             Platform                CPUs
Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     test7            Linux 64-bit for AMD       4     2       2          2.0

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:          1 27-Mar-10 15:32:01       24       1.5
  End Snap:          2 27-Mar-10 15:32:19       25       1.4
   Elapsed:           0.30 (mins)
   DB time:           0.02 (mins)                               DB CPU:
      0.02 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:       224M              Std Block Size:         8K
     Shared Pool:       356M                  Log Buffer:     6,209K
... ...
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                         1          53.0
control file parallel write                         20           0     14   19.3
log file switch completion                           2           0     97   13.1
log file parallel write                              8           0     18    9.6
os thread startup                                    1           0     26    1.7
          -------------------------------------------------------------

这里的Top 5 Timed Events是诊断的重要依据。

- The End -