oracle idle event list

oracle对等待事件进行了清晰的分类。其中有一类事件为idle wait。

SQL> select wait_class#,wait_class from v$system_wait_class;

WAIT_CLASS# WAIT_CLASS
----------- ----------------------------------------------------------------
	  0 Other
	  1 Application
	  2 Configuration
	  4 Concurrency
	  5 Commit
	--  6 Idle
	  7 Network
	  8 User I/O
	  9 System I/O

已选择9行。

在11g下idle event共有：

SQL> select event#,name from v$event_name where wait_class#=6;

    EVENT# NAME
---------- ----------------------------------------------------------------
	 1 pmon timer
	 3 VKTM Logical Idle Wait
	 4 VKTM Init Wait for GSGA
	 5 IORM Scheduler Slave Idle Wait
	 7 rdbms ipc message
	59 i/o slave wait
	65 VKRM Idle
	66 wait for unread message on broadcast channel
	67 wait for unread message on multiple broadcast channels
	68 class slave wait
	69 KSV master wait

    EVENT# NAME
---------- ----------------------------------------------------------------
	70 PING
	72 watchdog main loop
	73 DIAG idle wait
	74 ges remote message
	75 gcs remote message
	76 heartbeat monitor sleep
	77 GCR sleep
	78 SGA: MMAN sleep for component shrink
       109 MRP redo arrival
       129 LNS ASYNC archive log
       130 LNS ASYNC dest activation

    EVENT# NAME
---------- ----------------------------------------------------------------
       131 LNS ASYNC end of log
       144 simulated log write delay
       145 LGWR real time apply sync
       201 parallel recovery slave idle wait
       202 LogMiner builder: idle
       205 LogMiner builder: branch
       206 LogMiner preparer: idle
       209 LogMiner reader: log (idle)
       210 LogMiner reader: redo (idle)
       211 LogMiner client: transaction
       212 LogMiner: other

    EVENT# NAME
---------- ----------------------------------------------------------------
       213 LogMiner: activate
       214 LogMiner: reset
       215 LogMiner: find session
       216 LogMiner: internal
       217 Logical Standby Apply Delay
       219 parallel recovery coordinator waits for slave cleanup
       224 parallel recovery control message reply
       231 parallel recovery slave next change
       244 PX Deq: Txn Recovery Start
       245 PX Deq: Txn Recovery Reply
       250 fbar timer

    EVENT# NAME
---------- ----------------------------------------------------------------
       256 smon timer
       257 PX Deq: Metadata Update
       258 Space Manager: slave idle wait
       260 PX Deq: Index Merge Reply
       261 PX Deq: Index Merge Execute
       262 PX Deq: Index Merge Close
       264 PX Deq: kdcph_mai
       265 PX Deq: kdcphc_ack
       302 shared server idle wait
       303 dispatcher timer
       306 cmon timer

    EVENT# NAME
---------- ----------------------------------------------------------------
       307 pool server timer
       309 JOX Jit Process Sleep
       310 jobq slave wait
       312 pipe get
       315 PX Deque wait
       316 PX Idle Wait
       317 PX Deq: Join ACK
       318 PX Deq Credit: need buffer
       319 PX Deq Credit: send blkd
       320 PX Deq: Msg Fragment
       321 PX Deq: Parse Reply

    EVENT# NAME
---------- ----------------------------------------------------------------
       322 PX Deq: Execute Reply
       323 PX Deq: Execution Msg
       324 PX Deq: Table Q Normal
       325 PX Deq: Table Q Sample
       336 Streams fetch slave: waiting for txns
       342 Streams: waiting for messages
       343 Streams capture: waiting for archive log
       347 single-task message
       352 SQL*Net message from client
       356 SQL*Net vector message from client
       357 SQL*Net vector message from dblink

    EVENT# NAME
---------- ----------------------------------------------------------------
       366 PL/SQL lock timer
       369 Streams AQ: emn coordinator idle wait
       370 EMON slave idle wait
       371 Streams AQ: waiting for messages in the queue
       372 Streams AQ: waiting for time management or cleanup tasks
       375 Streams AQ: delete acknowledged messages
       376 Streams AQ: deallocate messages from Streams Pool
       377 Streams AQ: qmn coordinator idle wait
       378 Streams AQ: qmn slave idle wait
       379 Streams AQ: RAC qmn coordinator idle wait
       380 HS message to agent

    EVENT# NAME
---------- ----------------------------------------------------------------
       386 ASM background timer
       392 auto-sqltune: wait graph update
       393 WCR: replay client notify
       394 WCR: replay clock
       396 WCR: replay paused
       398 JS external job
       407 cell worker idle

已选择95行。

通常意义下，idle event是可以忽略的。但是有时也可以帮助我们解决问题。例如：

Whether or not the event SQL*Net message from client (and SQL*Net message from dblink) should be ignored depends on how the application works. Foreground processes post this event when they wait for instructions from client processes. In other words, the foreground processes are sitting idle waiting for more work to do. For example, a user may run a short query and spend time looking at the result or go out to lunch without logging off. All the while, the foreground process faithfully waits for the user to return, posting the SQL*Net message from client event and increasing the value in the TIME_WAITED column. Therefore, the SQL*Net message from client is the most prevalent event in OLTP systems, and this is why many DBAs choose to ignore this event.

列举几个常用的 none idle 事件，对比上面的idle events。（思考一下 sql net message from client 和more from client的区别），