Re: PX Wait Events...

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) <Bhavik_Desai_at_ml.com>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