空闲等待事件
1.1 概念
空闲等待事件，表示服务器进程(server process)正在等待。换句话说，就是数据库服务正在闲着，或者说数据库资源充足、够用，如果真的存在瓶颈，那么这个瓶颈绝对不是数据库资源。该类等待事件出现，属于正常情况，在处理问题时可以被忽略；当然一些空闲等待事件，可以从侧面表明当前系统的瓶颈不在这方面。
1.2 查看空闲等待事件
1.2.1 当前数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
1.2.2 通过配置statspack查看
配置statspack后，可通过stats$idle_event视图查看有哪些空闲事件。
SQL> select * from stats$idle_event;
EVENT
----------------------------------------------------------------
AQ Proxy Cleanup Wait
ASM background timer
DBRM Logical Idle Wait
DIAG idle wait
EMON idle wait
EMON slave idle wait
GCR sleep
HS message to agent
IORM Scheduler Slave Idle Wait
JOX Jit Process Sleep
JS external job
KSV master wait
LGWR real time apply sync
LNS ASYNC archive log
LNS ASYNC dest activation
LNS ASYNC end of log
LogMiner builder: branch
LogMiner builder: idle
LogMiner client: transaction
LogMiner preparer: idle
EVENT
----------------------------------------------------------------
LogMiner reader: log (idle)
LogMiner reader: redo (idle)
LogMiner: activate
LogMiner: client waiting for transaction
LogMiner: find session
LogMiner: generic process sleep
LogMiner: internal
LogMiner: other
LogMiner: reader waiting for more redo
LogMiner: reset
LogMiner: slave waiting for activate message
LogMiner: waiting for processes to soft detach
LogMiner: wakeup event for builder
LogMiner: wakeup event for preparer
LogMiner: wakeup event for reader
Logical Standby Apply Delay
MRP redo arrival
Null event
PING
PX Deq Credit: need buffer
PX Deq Credit: send blkd
EVENT
----------------------------------------------------------------
PX Deq: Execute Reply
PX Deq: Execution Msg
PX Deq: Index Merge Close
PX Deq: Index Merge Execute
PX Deq: Index Merge Reply
PX Deq: Join ACK
PX Deq: Metadata Update
PX Deq: Msg Fragment
PX Deq: Par Recov Execute
PX Deq: Parse Reply
PX Deq: Signal ACK
PX Deq: Table Q Normal
PX Deq: Table Q Sample
PX Deq: Txn Recovery Reply
PX Deq: Txn Recovery Start
PX Deq: kdcph_mai
PX Deq: kdcphc_ack
PX Deque wait
PX Idle Wait
Queue Monitor Shutdown Wait
Queue Monitor Slave Wait
EVENT
----------------------------------------------------------------
Queue Monitor Wait
SGA: MMAN sleep for component shrink
SQL*Net message from client
SQL*Net message to client
SQL*Net more data from client
SQL*Net vector message from client
SQL*Net vector message from dblink
STREAMS apply coord waiting for slave message
STREAMS apply slave idle wait
STREAMS apply slave waiting for coord message
STREAMS capture process filter callback wait for ruleset
STREAMS fetch slave waiting for txns
STREAMS waiting for subscribers to catch up
Space Manager: slave idle wait
Streams AQ: RAC qmn coordinator idle wait
Streams AQ: deallocate messages from Streams Pool
Streams AQ: delete acknowledged messages
Streams AQ: emn coordinator idle wait
Streams AQ: qmn coordinator idle wait
Streams AQ: qmn slave idle wait
Streams AQ: waiting for messages in the queue
EVENT
----------------------------------------------------------------
Streams AQ: waiting for time management or cleanup tasks
Streams capture: waiting for archive log
Streams fetch slave: waiting for txns
Streams: waiting for messages
VKRM Idle
VKTM Init Wait for GSGA
VKTM Logical Idle Wait
WCR: replay client notify
WCR: replay clock
WCR: replay paused
auto-sqltune: wait graph update
cell worker idle
class slave wait
client message
cmon timer
dispatcher timer
fbar timer
gcs for action
gcs remote message
ges remote message
heartbeat monitor sleep
EVENT
----------------------------------------------------------------
i/o slave wait
jobq slave wait
knlqdeq
lock manager wait for remote message
master wait
null event
parallel query dequeue
parallel recovery control message reply
parallel recovery coordinator waits for slave cleanup
parallel recovery slave idle wait
parallel recovery slave next change
parallel recovery slave wait for change
pipe get
pmon timer
pool server timer
queue messages
rdbms ipc message
shared server idle wait
simulated log write delay
single-task message
slave wait
EVENT
----------------------------------------------------------------
smon timer
virtual circuit status
wait for activate message
wait for unread message on broadcast channel
wait for unread message on multiple broadcast channels
wakeup event for builder
wakeup event for preparer
wakeup event for reader
wakeup time manager
watchdog main loop
135 rows selected
1.2.3 通过v$event_name视图查看
SQL> select name from v$event_name where wait_class='Idle';
NAME
----------------------------------------------------------------
pmon timer
VKTM Logical Idle Wait
VKTM Init Wait for GSGA
IORM Scheduler Slave Idle Wait
rdbms ipc message
i/o slave wait
VKRM Idle
wait for unread message on broadcast channel
wait for unread message on multiple broadcast channels
class slave wait
KSV master wait
PING
watchdog main loop
DIAG idle wait
ges remote message
gcs remote message
heartbeat monitor sleep
GCR sleep
SGA: MMAN sleep for component shrink
MRP redo arrival
NAME
----------------------------------------------------------------
LNS ASYNC archive log
LNS ASYNC dest activation
LNS ASYNC end of log
simulated log write delay
LGWR real time apply sync
parallel recovery slave idle wait
LogMiner builder: idle
LogMiner builder: branch
LogMiner preparer: idle
LogMiner reader: log (idle)
LogMiner reader: redo (idle)
LogMiner client: transaction
LogMiner: other
LogMiner: activate
LogMiner: reset
LogMiner: find session
LogMiner: internal
Logical Standby Apply Delay
parallel recovery coordinator waits for slave cleanup
parallel recovery control message reply
parallel recovery slave next change
NAME
----------------------------------------------------------------
PX Deq: Txn Recovery Start
PX Deq: Txn Recovery Reply
fbar timer
smon timer
PX Deq: Metadata Update
Space Manager: slave idle wait
PX Deq: Index Merge Reply
PX Deq: Index Merge Execute
PX Deq: Index Merge Close
PX Deq: kdcph_mai
PX Deq: kdcphc_ack
virtual circuit next request
shared server idle wait
dispatcher timer
cmon timer
pool server timer
JOX Jit Process Sleep
jobq slave wait
pipe get
PX Deque wait
PX Idle Wait
NAME
----------------------------------------------------------------
PX Deq: Join ACK
PX Deq Credit: need buffer
PX Deq Credit: send blkd
PX Deq: Msg Fragment
PX Deq: Parse Reply
PX Deq: Execute Reply
PX Deq: Execution Msg
PX Deq: Table Q Normal
PX Deq: Table Q Sample
Streams fetch slave: waiting for txns
Streams: waiting for messages
Streams capture: waiting for archive log
single-task message
SQL*Net message from client
SQL*Net vector message from client
SQL*Net vector message from dblink
PL/SQL lock timer
Streams AQ: emn coordinator idle wait
EMON slave idle wait
Streams AQ: waiting for messages in the queue
Streams AQ: waiting for time management or cleanup tasks
NAME
----------------------------------------------------------------
Streams AQ: delete acknowledged messages
Streams AQ: deallocate messages from Streams Pool
Streams AQ: qmn coordinator idle wait
Streams AQ: qmn slave idle wait
Streams AQ: RAC qmn coordinator idle wait
HS message to agent
ASM background timer
auto-sqltune: wait graph update
WCR: replay client notify
WCR: replay clock
WCR: replay paused
JS external job
cell worker idle
96 rows selected
