Oracle idle wait events and non-idle wait events
Oracle wait events are an important basis and indicator for measuring the operating status of Oracle. The concept of wait events is introduced in Oracle7.0.1.2, with approximately 100 wait events. In Oracle 8.0, this number was increased to approximately 150, with approximately 200 events in Oracle8i and about 360 waiting events in Oracle9i. There are two main types of wait events, namely, idle wait events and non-idle wait events.
Idle events refer to Oracle waiting for some kind of work. When diagnosing and optimizing databases, we don't need to pay too much attention to these events.
Common idle events include:
- Dispatcher timer
- Lock element cleanup
- Null event
- Parallel query dequeue wait
- Parallel query idle wait-Slaves
- Pipe get
- PL/SQL lock timer
- Pmon timer-pmon
- Rdbms ipc message
- Slave wait
- Smon timer
- SQL * Net break/reset to client
- SQL * Net message from client
- SQL * Net message to client
- SQL * Net more data to client
- Virtual circuit status
- Client message
Non-idle wait events are specific to Oracle activities. They are the waiting events that occur during database tasks or application running. We should pay attention to and study these waiting events when adjusting the database.
Some common non-idle wait events include:
- Db file scattered read
- Db file sequential read
- Buffer busy waits
- Free buffer waits
- Enqueue
- Latch free
- Log file parallel write
- Log file sync