Oracle常见等待事件

IO 等待事件

Buffer Busy Waits

An Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. Possible reasons:

•     The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
•     Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

Buffer busy waits is common in I/O bound systems. As this is about contention for specific block, increasing buffer cache cannot reduce the number of such wait. The main remedy would be tuning SQL to reduce the I/O number.
P1: file#  P2#:block#  P3: class#

Log File Sync

Wait for LGWR to flush dirty buffers on commit. This is a synchronous wait event. Possible cause:
auto-commit in applications.
Frequent commit, ie: one commit per row
p1: log buffer block

Log file parallel write
等待将log buffer内容写入redo log file。因每组redo log文件内有镜像，故而parallel write。

解决办法：调整SQL，降低逻辑读。

Free buffer waits
No place to put a new block in buffer cache.
解决办法：
    降低逻辑读
    增大buffer cache
    增加DBWR进程数

Enqueue
Oracle中，queue是实现锁的一种。当多个进程/事务需要对同一对象加锁时，他们依次进入一个排队queue。
TM – table modification
TX – Transaction locks
UL – user lock
CI – Cross Instance
CU – Cursor Bind
HW – High Water
RO – Reusable Object
ST – Space Transaction
TS – Temporary Space

enq: TX - row lock contention

行级排它锁. 例如：两个会话（session）都更新(update)同一行记录。其中一个得到排它锁，另一个就会被阻塞，它的等待事件就是enq: TX - row lock contention.

enq: TX - allocate ITL entry

表示Session试图在ITL(Interested transaction list)里加入一条记录。因为取得锁之前，session需要在block的ITL区给自己占一个“坑”。当“坑位”不够时，就会频繁出现这个等待事件。

enq: SQ

Waiting on exclusive access to a sequence。 解决办法：增大sequence cache。

Latch -Oracle 的轻量化锁。用于内存中的数据结构。

Latch: shared pool

shared pool的竞争。可能是因为SQL解析太多。

PX相关

PX Deq: Table Q Normal
Indicates that the slave waits for data to arrive on its input table queue.
One slave set works on the data ( e.g. read data from disk , do a join ) called the produces slave set and the other slave set waits to get the data so that they can start the work. The slaves in this slave set are called consumer.
The wait event "PX Deq: Table Q Normal" means that the slaves in the consumer slave have to wait for rows( data ) from the other slave set that they can start their work.

PX Deq: Execute Reply
The QC is expecting a response (acknowledgement) to a control message from the slaves or is expecting to de-queue data from the producer slave set.
This means he waits that the slaves finished to execute the SQL statement and that they send the result of the query back to the QC.

Huge number of such waits indicates Oracle is spending too much on coordinating, this could be caused by imbalance work load between slaves.

resmgr:cpu quantum
The session is waiting to be allocated a quantum of cpu. This event occurs when the resource manager is enabled and is throttling CPU consumption. To reduce the occurrence of this wait event, increase the CPU allocation for the sessions's current consumer group.

查看resource manager的配置

show parameter resource
NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
resource_limit                                     boolean     FALSE                                                                                                
resource_manager_cpu_allocation                    integer     4                                                                                                    
resource_manager_plan                              string      SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN  

RESOURCE_LIMIT: determines whether resource limits are enforced in database profiles.
RESOURCE_MANAGER_PLAN: specifies the top-level resource plan to use for an instance. The resource manager will load this top-level plan along with all its descendants (subplans, directives, and consumer groups). If you do not specify this parameter, the resource manager is off by default.

常用解决办法，禁用resource manager:

ALTER system SET resource_manager_plan='';
EXECUTE dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
EXECUTE dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
 
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',
                               operation => NULL,
                               window_name => NULL);
END;

该办法参考了网上资料：

http://www.killdb.com/2011/11/21/resmgrcpu-quantum-led-to-performance-problems.html

PX Deq Credit: send blkd

Imagine the following scenario:
The Query Slave P3 just deliver a message of 4KB to Query Coordinator.
The Query Coordinator received the message from P3 and more than 200 from other Queries Slaves.
The Query Slave P3 is ready to send another message to the Query Coordinator, but the Query Coordinator has not finished processing the message sent earlier by the Query Slave P3.
The Query Slave P3 is waiting the Query Coordinator finish processing your previous message in order to receive the new message, and while wait generating the event “PX Deq Credit: send blkd”.
In summary, this wait event occurs when a Query Slave is ready to post a message, but must wait until the Query Coordinator has finished processing a message sent earlier so send the next message.

PX Deq: Execution Msg

PX Deq: Execution Msg
This event appears when a PQ slave has nothing to do, but is not allowed to go idle.
One scenario where you can see large values for the event is when (for example) you have a large data set coming out of a parallel ORDER BY.
The last layer of PX slaves in the query will receive a ranged set of rows and sort them. The QC will then request ALL the rows from the first PX slave, then the second, then the third and so on.
When the first slave has supplied all its rows, it will go into "PX Deq: Execution Msg" waiting for the QC to tell it do die - which happens only after the QC has got all the rows from all the slaves and passed them to the front end.
This means that it is a usually an idle event.