oracle px execute reply,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 SyncWait 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: SQWaiting 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.