RE: PX Wait Events...
Date: Wed, 10 Sep 2008 16:42:33 +0530
Message-ID: <0F1649C56734D641B14FC527029E0E4D01D8DA45@MLMUM203MB.amrs.win.ml.com>
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 ---- ---------- ---------- ---------- ----------Execute Reply
------------ -------------------------- ------------------------------
------------------------------
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:
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 ---- ---------- ---------- ---------- ----------message from client
------------ -------------------------- ------------------------------
------------------------------
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
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 10 2008 - 06:12:33 CDT