Wait Event "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 - but it may be a symptom of excessively large queries choosing an inappropriate execution path.

"send blocked" is listed in the 9i documentation as an idle wait event. I understand from the post that the producers are too quick for the consumer. What exactly are these producers and what exactly is the consumer.

Ian MacGregor
Stanford Linear Acclerator Center
ian_at_SLAC.Stanford.edu

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Thursday, May 16, 2002 7:03 PM
To: Multiple recipients of list ORACLE-L

Nancy,

Hope this helps - it is some information I have saved over time.

If someone else can help clarify this I would find it helpful too.

>From Jonathan Lewis in a Metalink discussion.
"The most useful giveaway is often the 'send blocked' wait - this indicates a degree of contention as multiple producers all want to feed a given consumer at the same time. "

and from Anjo's wait event paper:

Event Name 			P1 		P2 		P3
parallel query dequeue wait 	queue/reason 	sleeptime 	passes

2.75 parallel query dequeue wait

The process is waiting for a message during a parallel execute

Wait time
The wait time depends on how quickly the message arrives. So wait times may vary, but it will normally be a short period of time.

Parameters
queue/reason
Before 7.2, it was queue which indicated the process queue to dequeue. With 7.2 and higher it shows the reason for dequeueing. Check the fixed table x$kxfpsds for the current list of reasons for your release.

sleeptime/senderid
If sleeptime greater than 0x10000000, the lower sixteen bits indicate the slave number number on the remote instance indicated by the higher sixteen bits of the first 32 bits. Look at P2RAW to get the best information.

loop
The number of times we have waited sofar.

Advise
The init.ora parameter _parallel_server_sleep_time determines how long you will sleep on this event. The default is 100 msec if there are no credits and 2000 msec if there are. You can specificy the init.ora _parallel_server_sleep_time twice in your init.ora file. The first entry will set the sleep time for if there are credits. The second entry will set the sleep time for if there are no credits.

saved from a Metalink forum at 1 stage.
"

Here is some information I was able to find on these parameters. Documentation bug, 1077684, has been filed requesting the description of these and other missing wait_events.

PX Deq:Table Q Normal

No description available
Parameters:

P1: sleeptime/senderid 
P2: passes 
P3: 

PX Deq:Execution Msg
Definition:PQ slave is waiting to be told what to do. This is one of the main events used in a parallel query dialogue as the slave is told to parse / execute / fetch etc.. Parameters: P1: sleeptime/senderid
P2: passes

PX Deq: Execute Reply
Definition: QC is waiting for a reply
Parameters: P1: sleeptime/senderid
P2: passes

PX Deq Credit: send blkd
Definition: Waiting for the CREDIT
Parameters: P1: sleeptime/senderid
P2: passes
P3: qref
This Wait Event replaces Parallel_Query_Dequeue, the name was changed in Oracle8i.

PX Deq Credit: need buffer
Definition: Waiting for the CREDIT so we can fill a buffer to send data. Parameters: P1: sleeptime/senderid
P2: passes
P3: qref
This Wait Event replaces Parallel_Query_Dequeue, the name changed in Oracle8i.

PX Deq Credit: free buffer
Definition: Wait for credit to free a null message Parameters: P1: sleeptime/senderid
P2: passes
P3: qref
This Wait Event replaces Parallel_Query_Dequeue, the name changed in Oracle8i.

Hope this helps, if you are having a specific problem, please post details.

Reem Munakash
Enterprise Server Analyst
"

-----Original Message-----
Sent: Friday, 17 May 2002 10:53

Hello,

I am hoping someone can help me with an elusive wait event called 'PX Deq: Execution Msg'. As you can see from the statspack "Top 5 Wait Events" this event accounts for almost 75% of the wait time. I have been looking in past Oracle-L posts, Google, Oracle FAQ, OTN, Metalink, Oracle manuals, etc. I can find almost nothing about this event. What I did find leads me to believe may be an idle event and that it is the second event 'PX Deq Credit: send blkd' that may be the more troublesome one. However I am still confused about both. Can someone help set me straight or tell me where there might be some more information about these 2 events?

Thanks,
Nancy

Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     %
Total
Event                                               Waits  Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------ -----
--
PX Deq: Execution Msg                              42,144    8,639,803
74.48
PX Deq Credit: send blkd                           15,339    2,878,443
24.81
log file parallel write                            41,934       30,787
.27
db file parallel write                              8,776       25,046
.22
db file sequential read                            24,767       18,255

.16[@more@]