Parallel Execution Wait Events in Oracle

Oracle Parallel Execution can help utilize the power of your hardware and yet remains under-used. 

It is an interesting technology that is particularly suited to data warehousing, in that it allows a single user (or small set of users) to effectively soak up all of the server resources to satisfy a particular query. According to AskTom article by Tom Kyte on parallel query that said that this isn't always what you want - you wouldn't for example want individual OTLP users soaking up all resource for regular transactions or queries - but parallel query is an excellent way to effectively use up all the available CPUs and disk units when you've got a particularly big warehouse query.

Oracle documentation states that these are main wait/idle events because they indicate the normal behavior of a process waiting for another process to do its work:

• PX Deq: Table Q Normal 
• PX Deq: Execute Reply 
• PX Deq Credit: send blkd 

PX Deq: Table Q Normal

Indicates that the slave wait for data to arrive on its input table queue. In a parallel execution environment we have a producer-consumer model. One slave set works on the data ( e.g. read data from disk , do a join ) called the producer slave set and the other slave set waits to get the data so 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 so they can start their work. 

PX Deq: Execute Reply 

The QC is expecting a response (acknowledgment) to a control message from the slaves or is expecting to dequeue 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. 

PX Deq Credit: send blkd 

The wait events "PX Deq Credit: need buffer" and "PX Deq Credit: send blkd" are occur when data or messages are exchanged between process that are part of a px query. 

There is an another event: The PX qref latch event can often mean that the Producers are producing data quicker than the Consumers can consume it. On this particular system, very high degrees of parallelism were being used during an overnight batch run so a great deal of messaging was going on. Maybe we could increase parallel_execution_message_size to try to eliminate some of these waits or we might decrease the DOP(Degree of Parallelism).