RE: PDML and PQ tunning
Date: Sat, 19 Nov 2011 16:36:28 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4454BB00C_at_LITIGMBCRP02.Corp.Acxiom.net>
Dimitre,
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 SELECT?
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 operations?
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Radoulov, Dimitre
Sent: Friday, November 18, 2011 5:02 AM
To: oracle-l_at_freelists.org
Subject: PDML and PQ tunning
Hi all,
HP-UX B.11.11 U 9000/800
Oracle Database EE 9.2.0.4.0
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:
Bug 6952166: DIRECT LOAD INSERT LONG WAIT FOR "PX QREF LATCH" (bug status: 33 - Suspended, Req'd Info not Avail).
Anyone with similar experience and possible solutions/workarounds?
Best regards
Dimitre
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 19 2011 - 10:36:28 CST