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

PX qref latch
In an earlier section, “Parallel Query Architecture,” we discussed how the producers, consumers, and the query
coordinators are interconnected via queues to exchange data and messages. The PX qref latch helps protect these
queues; and this latch usually indicates that the sender is faster than the receiver, indicating there is a communication
overhead. Under such circumstances, Oracle recommends that increasing the PARALLEL_EXECUTION_MESSAGE_SIZE
could ease the situation a bit. Also, if the system workload is really high, decreasing the DOP could also help.
PX Queuing: Statement Queue
This is a new event introduced in Oracle Database 11g Release 2 with the new parallel processing features. It indicates
that there aren't sufficient parallel processes available to start execution servers, and the statement is queued until
such time that parallel execution servers are available. This event also indicates that the statement is next in the queue
to be selected for processing. Oracle follows the FIFO method when statements are placed into queue. The wait time
indicates the time the statement is waiting since the statement was selected to be processed.
enq: JX SQL Statement Queue
Contrary to the PX queueing: statement queue event, this event indicates that the statement is in the queue;
however, it has not been selected to be released yet. More statements in this queue indicate insufficient DOP or high
allocation of DOP to other statements such that all statements do not get sufficient DOPs to start processing.
PX Deq: reap credit 11
This wait event indicates that we are doing a non-blocking test to see if any channel has returned a message. We should
see that although there are a high number of these events, the time accumulated to it should be zero (or really low).
Depending on which process is doing this, it could indicate that either the consumers are starved for data
(IO bound perhaps) or the producers are waiting on the consumers to “return” the flow control credit (consumers
may be compute or I/O bound). You should investigate query plans, check for data skew, I/O contention (table or tmp
segment), and so forth, to get a deeper understanding of the symptoms.
PX Deq: Join ACK 12
This is a non-idle wait. When a statement is executed in parallel, based on the DOP value, the QC has to build the
required slave sets. In this process, the QC first sends join messages to each slave in the set being obtained and then
waits for a join acknowledgement message to be returned from each slave in the set. This operation is repeated again
if the parallel operation requires a second slave set. Memory is required for communication between 1.) Slave to slave
and 2.) Slave to QC. The wait indicates that the QC sends a message to a slave and waits until memory is allocated and
gets a message back from the slave.
11 Metalink Note # 250947.1.
12 Metalink Note # 250960.1.