RE: best way to invoke parallel in DW loads?
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 16 Sep 2009 11:04:23 -0500
>> 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
For what it's worth, my point was about partitioning concerning UPDATEs
and DELETEs, not INSERT. Yes, you can force or enable parallel DML and
issue INSERTs, UPDATEs, DELETEs all you want, partitioned or
non-partitioned. But, for UPDATEs and DELETEs, your main gain with
parallel DML is with partitioned objects. At least that was the case in
9i, as I believe the doc explicitly listed this (of course my quick
check didn't confirm this, so may be full of it).
David C. Herring | DBA, Acxiom Database Services
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
-----Original Message-----
From: Greg Rahn [mailto:greg@xxxxxxxxxxxxxxxxxx]
Sent: Wednesday, September 16, 2009 10:13 AM
To: Herring Dave - dherri
Cc: nkodner@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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/initparam
s174.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
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
