Hi All,
This thread is to clear confusion on PX wait events.
I am observing that both the consumer sets (or QC-Parent
process) and producer slave sets (px slaves), both are in wait stat.
CASE-1
SERV X_STATUS X_PID X_SID P_SID OSUSER
SCHEMANAME CHILD_WAIT PARENT_WAIT
---- ---------- ---------- ---------- ----------
------------ -------------------------- ------------------------------
------------------------------
P000 IN USE 26 73 62 gtrade1
GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq:
Execute Reply
P001 IN USE 27 213 62 gtrade1
GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq:
Execute Reply
P002 IN USE 32 103 62 gtrade1
GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq:
Execute Reply
P003 IN USE 33 207 62 gtrade1
GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq:
Execute Reply
P004 IN USE 34 94 62 gtrade1
GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq:
Execute Reply
According to Oracle documentation, 'PX Deq: Execution
Msg' wait observed when the PQ slave (Producer slaves-Child) are waiting
to be told what to do.
And 'PX Deq: Execute Reply' wait is observed when the QC
is expecting a response (acknowledgement) 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.
If this is the case, it seems a deadlock happen where
both the producer and consumer are waiting for each other.
CASE-2
SERV X_STATUS X_PID X_SID P_SID OSUSER
SCHEMANAME CHILD_WAIT PARENT_WAIT
---- ---------- ---------- ---------- ----------
------------ -------------------------- ------------------------------
------------------------------
P000 IN USE 8 114 117 rptadm2
GTRADE_PRESENTATION_READ PX Deq Credit: send blkd SQL*Net
message from client
P003 IN USE 23 193 117 rptadm2
GTRADE_PRESENTATION_READ PX Deq Credit: send blkd SQL*Net
message from client
P001 IN USE 27 239 117 rptadm2
GTRADE_PRESENTATION_READ PX Deq Credit: send blkd SQL*Net
message from client
P002 IN USE 16 56 117 rptadm2
GTRADE_PRESENTATION_READ PX Deq Credit: send blkd SQL*Net
message from client
In this case, QC (SID 117) has assigned work to px
slave, but according to the waits being observed by procuder slaves (PX
Deq Credit: send blkd), they are waiting for the QC or the consumer
slave.
Can I have explanation to this? I suspect, I am missing
something somewhere in interpreting PX wait events.
Regards,
Bhavik A.Desai
O R A C L E D B A
Merrill Lynch India Technology Services (MLITS),
GMI - Electronic Trading Data,
VoIP(Full): +91 215 377 5548
VoIP(Short): 65548
--------------------------------------------------------
This message w/attachments (message) may be privileged, confidential or proprietary, and if you are not an intended recipient, please notify the sender, do not use or share it and delete it. Unless specifically indicated, this message is not an offer to sell or a solicitation of any investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Merrill Lynch. Subject to applicable law, Merrill Lynch may monitor, review and retain e-communications (EC) traveling through its networks/systems. The laws of the country of each sender/recipient may impact the handling of EC, and EC may be archived, supervised and produced in countries other than the country in which you are located. This message cannot be guaranteed to be secure or error-free. This message is subject to terms available at the following link: http://www.ml.com/e-communications_terms/. By messaging with Merrill Lynch you consent to the foregoing.
