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

Possible causes for this wait event are

1. The PQ slave OS process was killed or died. Until PMON has not cleaned up the internal

structures, a new QC assumes they are still alive. When a new QC tries to get slaves, he
sends a message to the slave to join the slave set; but we get no acknowledge message
back. Check if someone killed an idle slave or a slave process has died. Compare the
entries in V$PX_PROCESS and on OS level the name of the started PQ slaves.

2. Another possible cause is when SGA memory is so depleted that it is possible that the

slave may not be able to establish its response channel to the QC to acknowledge
the join message, force the slave to die so that the QC can be notified via PMON cleanup,
and so forth.

PX Deq: Signal ACK
This is an idle wait event. It indicates that time waited by the QC to get acknowledgment receipts for the control
messages sent by the QC to the slaves.

PX Deq: Parse Reply
This is a non-idle wait. This wait event indicates the time waited when the slaves parse their SQL statements. Only
one slave will do the hard parse on an instance; which instance in the cluster is performing the hard parse cannot be
determined from this wait event. Once the slaves have completed the hard parse operation, the processing starts.
The reason for this wait event is due to library cache contention or improper sizing of library cache.

PX Deq: Execute Reply
This is an idle wait event. The QC is expecting a response and is expecting to dequeue data from the producer
slave set. This occurs when the slaves have finished execution of the SQL statement and they send the results
back to the QC.

Troubleshooting Using Oracle Event Interface
The event interface provided by Oracle helps look at the behavior of any specific operation within Oracle kernel.
For example, as mentioned in Chapter 6, event 10053 will help you trace through the behavior of Oracle's optimizer.
Event 10046 will help you analyze the execution plans generated by Oracle's optimizer. Similar to these events, there
are those that could help debug the parallel query operations.

Event 10391 @ Level 128
Event 10391 will list the instantiation of slaves processes. It's useful to troubleshoot the distribution of slaves across
multiple instances in a RAC cluster. Similar to other events, event 10391 can also be invoked at the system level or at
the session level. The following will invoke the collection of this information at the session level:

alter session set events '10391 trace name context forever, level 128';