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