Re: 11gR2 Data Pump question - parallel setting
Date: Fri, 27 Jul 2012 08:06:43 -0500
Message-ID: <CAPZQniWQNg9EYaQ5iRb237S3MvZrHx5e1xiyOXpqCQ0AN-httg_at_mail.gmail.com>
The datapump serialization of index, constraints and stats has bugged me for a long time. I talked to a product manager a few years ago (Carol Palmer, Roy Swonger, related to SR 2-4046444, SR 6635927.993) and suggested that these operations be spread out in multiple worker/slave processes like the data portion, but it saddens me that this has not yet happened. We have resorted to exactly what Greg mentioned, and have been using that for the past 3 years. I am very curious, what reason does Oracle give for not incorporating Greg's and Raj's 'solution'? Is there a good reason why those DDL statements cannot truly parallelized in multiple worker processes within datapump?
References:
http://www.freelists.org/post/oracle-l/Script-to-build-indexes-in-parallel,5
On Fri, Jul 27, 2012 at 6:21 AM, rjamya <rjamya_at_gmail.com> wrote:
> Jon,
>
> index/constraints etc will run parallel only if they had parallel
> setting at the time of expdp. parallelism is mainly for
> creating/loading tables, rest of metadata is always handled by a
> single thread and so is index/constraint creation etc.In fact you can
> split index creation scripts by table and them submit them in parallel
> via different sessions and then do same for constraints if applicable.
>
> If you want to do real parallelism, do what Greg suggested, create
> script and then tweak it to create indexes in parallel (and then alter
> to reset parallelism if you need to). Based on requirements, you may
> be able to speed up constraint creation by using novalidate (ymmv).
>
> Broadly speaking the parallelism doesn't affect
> index/constraint/metadata part (not sure of 11203 or 12c yet). Also
> for loading data I am not really sure if having parallel=x where x >>
> number of your dumpfiles will benefit greatly. However x <
> number_of_datafiles will slow things down a bit in my experience.
>
> Raj
>
> On Thu, Jul 26, 2012 at 6:14 PM, CRISLER, JON A <JC1706_at_att.com> wrote:
> > Is there any benefit to setting a Data Pump import to parallel=12 if the
> export was run with parallel=8 ? It seems to accept higher values, and
> spawns 12 workers. My goal is to get the items like index creation,
> constraints etc. to run faster. The source system has 8 cpu's, the target
> system 24 cpus.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Charles Schultz -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 27 2012 - 08:06:43 CDT