PDML and PQ tunning

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Fri, 18 Nov 2011 12:01:59 +0100
Message-ID: <CAGJBphT0TKsp8=U0ncxLtB6w1Fn4PZb6tqn7vD+FwA2-SmVpOA_at_mail.gmail.com>

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:

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

Received on Fri Nov 18 2011 - 05:01:59 CST

Original text of this message