顶级等待事件:
前文还提到另外一个重要视图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 NOHost Name Platform CPUs
Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
test7 Linux 64-bit for AMD 4 2 2 2.0Snapshot 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 -