Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 268

5 P022 IN USE 75 24191 549 739 DWH SSSVC1
5 P017 IN USE 59 24181 562 22027 DWH SSSVC1
5 P023 IN USE 76 24193 592 7108 DWH SSSVC1
5 P013 IN USE 44 24173 605 210 DWH SSSVC1
5 P002 IN USE 61 26009 648 4453 DWH SSSVC1
5 P003 IN USE 62 26011 701 358 DWH SSSVC1
5 P004 IN USE 63 26013 738 7416 DWH SSSVC1
28 rows selected.
Wait Events Related to Parallel Operations
Resources are not available in abundance; there is always a hard limit on the amount of resources that are available
for any operation. Parallel queries though can take advantage of utilizing resources across instances and can also
encounter resources contention. Oracle provides instrumentation through its wait interface to understand resource
waits. In this section, we discuss wait events that are related to parallel operations. 9
PX Deq Credit: need buffer
The slaves and the QC use table queues for the communication. Each table queue uses buffer to send data between
two slaves or a slave and the QC. At a given point in time, only a process can send data, and this is controlled by setting
the credit bit on the table queue. A process needs the credit bit if it has to send data via a table queue. The credit bit is
sent via a buffer from slave to slave or from a slave to the QC. This wait event indicates that the process is waiting for a
buffer from the other slave/QC that sends the credit bit and so that it could send a block.
PX Deq Credit: send blkd 10
The process wishes to send a message and does not have the flow control credit. Process must first dequeue a message
to obtain the credit. This wait event indicates that the receiver has not dequeued and/or completely consumed the
prior message yet.
PX Deq Credit: send blkd and PX Deq Credit: need buffer are nearly the same. Due to internal reason, you
see PX Deq Credit: send blkd more on local systems and PX Deq Credit: need buffer more on RAC systems.
PX Deq: Msg Fragment
This is a non-idle wait normally noticed when several bits of the message are dequeued from a fragmented message.
Fragmentation is an indication that the message buffer size may not be large enough. Increasing the size of
PARALLEL_EXECUTION_MESSAGE_SIZE reduces the probability of message fragmentation. However, the contra effect of a
considerable increase in the message size is an increase in memory overhead.
PX Deq: Execute Reply
This is an idle wait event. This wait is noticed when slaves have completed the execution of the SQL statement, and
they send the results of the query back to the QC.
9 Wait events in this section are limited to parallel operations; RAC-specific wait events are discussed in Chapter 17.
10 Metalink Note # 271767.1.