Re: best way to invoke parallel in DW loads?

On Wed, Sep 16, 2009 at 5:52 AM, Herring Dave - dherri <Dave.Herring_at_acxiom.com> 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