Re: best way to invoke parallel in DW loads?

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
http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/chapter1.htm#FEATURENO08700

> 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.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams174.htm#REFRN10156

> 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
PQ_DISTRIBUTE hint.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/hintsref.htm#PFGRF95147

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


-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l


Other related posts: