RE: PX Wait Events...

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 [mailto:ujang.jaenudin_at_gmail.com] 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)
<Bhavik_Desai_at_ml.com> 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.
--------------------------------------------------------
--
http://www.freelists.org/webpage/oracle-l