Re: best way to invoke parallel in DW loads?

  • From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • To: Dave.Herring@xxxxxxxxxx
  • Date: Wed, 16 Sep 2009 08:12:33 -0700

Comments in line.

On Wed, Sep 16, 2009 at 5:52 AM, Herring Dave - dherri
<Dave.Herring@xxxxxxxxxx> wrote:
> PARALLEL_AUTOMATIC_TUNING -> set this to TRUE to allow default parallelism 
> settings to work, along with having PX messages run through the large pool 
> instead of the shared pool.

I would recommend the default of false for this *deprecated*
parameter.  PARALLEL_ADAPTIVE_MULTI_USER may be used but the trade-off
is less predictable response time due to slave allocation.  I
personally prefer to use PARALLEL_ADAPTIVE_MULTI_USER=false and use
Resource Manager to control the active session limit or DOP if need
be.  In 11.2 the best way to control a parallel workload is Auto DOP
in conjunction with Parallel Statement Queuing

> PARALLEL_EXECUTION_MESSAGE_SIZE -> under 10g this isn't as much of a factor 
> but under 9i I'd make sure to set this to AT LEAST 8192 and if you see this 
> threshold being reach perhaps increase to 16384.

For most systems the default is too small given the large amounts of
memory on servers.  I would recommend 16384 and would note that in
11.2 the default becomes 16384.

> Normally only an INSERT /*+ APPEND */ will get you parallel benefits, in a 
> non-partitioned environment.

This is not true.  Parallelism can be used for insert append for both
partitioned and non-partitioned tables.  In fact, it is generally
better when using a partitioned table and inserting into multiple
partitions vs a single table/partition as the slaves are loading into
extents in a number of segments versus all slaves loading into extents
in 1 segment.  This has to do with using a merge load vs the brokered
load mechanism.  In 11.2 more optimizations have been made to these
operations and one can further control it if necessary with the

Also, don't forget to enable parallel dml for your session when using
insert append!

Greg Rahn

Other related posts: