RE: PDML and PQ tunning

  • From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
  • To: "cichomitiko@xxxxxxxxx" <cichomitiko@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 19 Nov 2011 16:36:28 +0000


Normally increasing PEMS from the default to 8192 or 16384 is done when you've 
got large, frequent parallel operations.  What you'll see is a drop in waits on 
various "PX Deq*" waits as a result, at least when you were receiving high 
waits for these events before the change.

As for your specific situation, you haven't shared a lot of detail.  For PQs, 
are the underlying tables large enough to warrent parallelization?  Smaller 
tables can actually run slower with parallelization, due to all the overhead of 
starting x number of PX slaves, dividing up the workload, communication between 
PX slaves and the QC, then producing the results and cleanup.  "Smaller" is 
obviously relative but from what I've seen in working on DWs, any table smaller 
than 150 MB to 200 MB usually should be left at degree 1.

Have you compared xplans between serial and parallel executions?  Were the 
serial executions using full scans?  Was more than 1 table joined and if so 
were both large and had a parallel degree set?

As for PDML, were you working with CTAS operations?  Or INSERT...SELECT?  For 
the latter were you attempting to have the INSERT run in parallel or just the 

For both PQ and PDML, were the PX qref latch waits the highest of all waits?  
If not, what were the top 5 waits before and after the change for both types of 

Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988 
1501 Opus Pl, Downers Grove, IL 60515, USA

The information contained in this communication is confidential, is intended 
only for the use of the recipient named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited. If you have received this communication in error, 
please resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Radoulov, Dimitre
Sent: Friday, November 18, 2011 5:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: PDML and PQ tunning

Hi all,

HP-UX B.11.11 U 9000/800
Oracle Database EE

Trying to run some statements in parallel (2, 4 on 4CPU host with
70-90% total CPU idle).
Parallel PDML and PQ processing result much slower (like 2 - 3 times)
than serial processing with significant waits for PX *qref latch* (no
more than 2.5% CPU per process during execution).

I've searched and found some old threads here on oracle-l where
*parallel_execution_message_size* is mentioned (currently it's set to
2152, I believe this is the default on 9i).
I don't believe that setting *parallel_execution_message_size* to a
higher value could have a _significant_ impact on the execution time
and reduce those waits.
Of course, I could be wrong (i.e higher value of
*parallel_execution_message_size* could significantly reduce the
execution time)?
I cannot test a different value because this parameter is static and
bouncing the database is not feasible right now.

I also found this on MOS:

status: 33 - Suspended, Req'd Info not Avail).

Anyone with similar experience and possible solutions/workarounds?

Best regards


Other related posts: