FAQ
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.

Search Discussions

  • Desai, Bhavik \(MLITS\) at Sep 10, 2008 at 11:52 am
    Hi Ujang,

    Thanks for the reply.
    I have not observed any deadlock but seen performance degradation(In
    terms of PX processes not being released/ utilized).
    By DEADLOCK word I wanted to say that both, slaves and QC (Or Consumer
    set and producer set) are waiting for each other.

    I would like to know what exactly these PX-Wait event means?

    I am interested in knowing what exactly Slaves and QC are doing in
    CASE-1.

    It seems, Slaves are already done with work assigned by QC. And QC has
    nothing more to be given to slaves. Then, why both of them are in WAIT
    mode?

    Case-2

    If Slaves are done with work given by QC (PX Deq Credit: send blkd),
    and QC is experiencing ' SQL*Net message from client ', why PX slave
    processes exist? (Is that due to 5 min algorithm?)

    Regards,
    Bhavik Desai
    -----Original Message-----
    From: Ujang Jaenudin
    Sent: Wednesday, September 10, 2008 5:10 PM
    To: Desai, Bhavik (MLITS)
    Subject: Re: PX Wait Events...

    bhavik,

    I'm not see any deadlock between QC and slave, due to P_SID (I guess
    it is Parent SID/QC) are different.

    you may try set event 10046 for QC sid and do trcsess due to it will
    produce many files, some slave trace file will be in the bdump.

    --
    thanks and regards
    ujang | oracle dba
    jakarta | http://ora62.wordpress.com

    On Wed, Sep 10, 2008 at 6:12 PM, Desai, Bhavik (MLITS)
    wrote:
    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.

    Merrill Lynch India Technology Services (MLITS),

    GMI - Electronic Trading Data,
    --------------------------------------------------------

    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.
    --------------------------------------------------------
  • Flado at Sep 10, 2008 at 1:17 pm
    Bhavik,

    I couldn't quite follow your Case 1, but Case 2 seems very simple once one
    realizes that an execution plan is not (always) a sequence of steps, each of
    which must be completed before the next one starts. It is just a plan, a
    description of the actions that need to be taken when the client requests
    more data. An analogy might be helpful - the client is a customer sitting at
    a restaurant table and eating his entree while a waiter and several cooks
    are standing by to prepare and serve the main dish as soon as he requests
    it. They all know how to prepare and serve it (they have an execution plan).
    So, in your Case 2 you have a classic idle situation where the query
    coordinator (waiter) is waiting for instructions from the client (customer)
    and the slaves (cooks) are waiting for instructions from the coordinator -
    and nobody can be freed to go home or serve other customers (that's where
    the analogy breaks down, unless the customer is a short-tempered VIP;-) )

    Back to Oracle, though:
    The QC (Query Coordinator) is idle, waiting for the next fetch() call from
    the client (SQL*Net message from client); when this call comes, some of the
    slaves will have to serve it, that is, send the data to the QC which will
    then assemble the response and send it to the client. So, while the cursor
    is still open, the slaves cannot be freed - they spend their time waiting on
    "PX Deq Credit: send blkd".

    HTH,

    Flado

    Adastra
  • Flado at Sep 10, 2008 at 2:07 pm
    Case 3:
    Sure, some are still busy while others are done. Maybe some had less work to
    do than the others. It is not always possible to distribute the work evenly,
    even in simple cases. Take this query:
    select /*+ full(t) parallel(t, 3) */ x from t where y>4;
    How would you go about splitting the full scan between 3 slaves? You would
    give them roughly equal rowid ranges, but you would also give them the
    predicate y>4. It may happen that all rows where y>4 fall in the rowid range
    of slave #1 - the other slaves will be done with scanning after the first
    fetch() while slave #1 will have to serve all the remaining fetch() calls.
    That is not to say that slave #1 will do more direct reads than any of the
    other two - just that the other two will do all their reading at the first
    fetch() (finding nothing), and #1 will have to read a block, serve the
    requested number of rows, and maybe wait for the next fetch() before it
    reads the next block.

    Cheers,
    Flado

    Adastra

    On Wed, Sep 10, 2008 at 3:29 PM, Desai, Bhavik (MLITS)
    wrote:
    *Classic example Flado. Thanks a lot for the detailed explanation.*

    * *

    *Any thoughts on CASE-1?*

    * *

    *One more case*

    * *

    *CASE-3*
    * *

    *SERV X_STATUS X_PID X_SID P_SID OSUSER
    SCHEMANAME CHILD_WAIT PARENT_WAIT*
    *---- ---------- ---------- ---------- ---------- ------------
    -------------------------- ------------------------------
    ------------------------------*

    *P030 IN USE 98 111 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    *P000 IN USE 16 102 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P014 IN USE 42 95 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P024 IN USE 68 90 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    *P028 IN USE 96 83 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    *P001 IN USE 57 176 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P003 IN USE 65 181 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P025 IN USE 55 188 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    *P013 IN USE 67 197 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P029 IN USE 95 205 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    *P031 IN USE 101 213 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    *P015 IN USE 69 221 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P027 IN USE 59 243 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    *P002 IN USE 24 75 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P012 IN USE 32 40 192 gtrade1
    GTRADE_ENTERPRISE_WRITE direct path read PX Deq: Execute
    Reply*

    *P026 IN USE 52 49 192 gtrade1
    GTRADE_ENTERPRISE_WRITE PX Deq: Execution Msg PX Deq: Execute
    Reply*

    * *

    *Does this mean that, ROWID distribution amongst slaves is not even?*

    *Some slaves are done with their work (PX Deq: Execution Msg) and some are
    still reading data (direct path read).*

    *Is that due to fragmentation in table/index?*

    * *

    *Regards,*

    *Bhavik Desai*

    * *
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 10, '08 at 11:12a
activeSep 10, '08 at 2:07p
posts4
users2
websiteoracle.com

2 users in discussion

Desai, Bhavik \(MLITS\): 2 posts Flado: 2 posts

People

Translate

site design / logo © 2022 Grokbase