等待事件概述！

等待事件不要分为两种类别，即空闲（idle）等待事件和非空闲（non-idle）等待事件。

空闲等待事件指Oracle正在等待某种工作，在诊断和优化数据库的时候，我们不用过多注意这部分事件。

非空闲等待事件专门针对Oracle的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是我们在调整数据库的时候应该关注与研究的。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

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

  COUNT(*)
----------
       889

可以看见在Oracle 10g中等待事件有889个。

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

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS                                                         COUNT(*)
----------- ------------- ---------------------------------------------------------------- ----------
          0    1893977003 Other                                                                   601
          1    4217450380 Application                                                              12
          2    3290255840 Configuration                                                            23
          3    4166625743 Administrative                                                           46
          4    3875070507 Concurrency                                                              25
          5    3386400367 Commit                                                                    1
          6    2723168908 Idle                                                                     63
          7    2000153315 Network                                                                  27
          8    1740759767 User I/O                                                                 17
          9    4108307767 System I/O                                                               24
         10    2396326234 Scheduler                                                                 2

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS                                                         COUNT(*)
----------- ------------- ---------------------------------------------------------------- ----------
         11    3871361733 Cluster                                                                  48

已选择12行。

可以看见空闲等待事件有63个之多。

SQL> select name,wait_class from v$event_name where wait_class = 'Idle';

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
pmon timer                                                       Idle
rdbms ipc message                                                Idle
i/o slave wait                                                   Idle
wait for unread message on broadcast channel                     Idle
wait for unread message on multiple broadcast channels           Idle
class slave wait                                                 Idle
KSV master wait                                                  Idle
watchdog main loop                                               Idle
DIAG idle wait                                                   Idle
ges remote message                                               Idle
gcs remote message                                               Idle

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
SGA: MMAN sleep for component shrink                             Idle
LNS ASYNC archive log                                            Idle
LNS ASYNC dest activation                                        Idle
LNS ASYNC end of log                                             Idle
PX Deq: Par Recov Reply                                          Idle
PX Deq: Par Recov Execute                                        Idle
PX Deq: Par Recov Change Vector                                  Idle
LogMiner: slave waiting for activate message                     Idle
LogMiner: wakeup event for builder                               Idle
LogMiner: wakeup event for preparer                              Idle
LogMiner: wakeup event for reader                                Idle

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
LogMiner: client waiting for transaction                         Idle
LogMiner: reader waiting for more redo                           Idle
parallel recovery coordinator waits for cleanup of slaves        Idle
smon timer                                                       Idle
PX Deq: Txn Recovery Start                                       Idle
PX Deq: Txn Recovery Reply                                       Idle
PX Deq: Index Merge Reply                                        Idle
PX Deq: Index Merge Execute                                      Idle
PX Deq: Index Merge Close                                        Idle
PX Deq: kdcph_mai                                                Idle
PX Deq: kdcphc_ack                                               Idle

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
virtual circuit status                                           Idle
dispatcher timer                                                 Idle
jobq slave wait                                                  Idle
pipe get                                                         Idle
PX Deque wait                                                    Idle
PX Idle Wait                                                     Idle
PX Deq: Join ACK                                                 Idle
PX Deq Credit: need buffer                                       Idle
PX Deq: Msg Fragment                                             Idle
PX Deq: Parse Reply                                              Idle
PX Deq: Execute Reply                                            Idle

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
PX Deq: Execution Msg                                            Idle
PX Deq: Table Q Normal                                           Idle
PX Deq: Table Q Sample                                           Idle
Streams fetch slave: waiting for txns                            Idle
single-task message                                              Idle
SQL*Net message from client                                      Idle
SQL*Net message from dblink                                      Idle
PL/SQL lock timer                                                Idle
EMON idle wait                                                   Idle
Streams AQ: waiting for messages in the queue                    Idle
Streams AQ: waiting for time management or cleanup tasks         Idle

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
Streams AQ: delete acknowledged messages                         Idle
Streams AQ: deallocate messages from Streams Pool                Idle
Streams AQ: qmn coordinator idle wait                            Idle
Streams AQ: qmn slave idle wait                                  Idle
Streams AQ: RAC qmn coordinator idle wait                        Idle
HS message to agent                                              Idle
ASM background timer                                             Idle
JS external job                                                  Idle

已选择63行。

这里就列出了所有的空闲等待事件。