RE: PX Wait Events...
- From: "Desai, Bhavik \(MLITS\)" <Bhavik_Desai@xxxxxx>
- To: "Ujang Jaenudin" <ujang.jaenudin@xxxxxxxxx>
- Date: Wed, 10 Sep 2008 17:22:01 +0530
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@xxxxxxxxx]
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@xxxxxx> 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.
--------------------------------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
