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
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@xxxxxxxxxx
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@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 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
