Re: best way to invoke parallel in DW loads?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 14 Sep 2009 18:43:04 +0100
Message-ID: <7765c8970909141043w4eedecbfo22b3c042db77253f_at_mail.gmail.com>

Not an expert view by any means. Check out doug burns on px oracledoug.com. However do check out what you think parallel max servers is for, against the doc, it's a system wide parameter. I suspect you are over inhibiting the system. That all said px usually works best with lots of small distinct chunks in a large workload so partitioning, bitmap indexes and the rest. It can in very bad on ok style schemas

On 9/14/09, Neil Kodner <nkodner_at_gmail.com> wrote:
> I've taken over a 9ir2 data warehouse and its loading process.
> Here are some 'highlights' of the current 9i warehouse environment(I didn't
> do this!)
>
> Oracle 9.2.0.7
> Solaris 9
> 4x1200mhz sparcv9 cpu
> parallel_max_servers - 70(!)
> parallel_adaptive_multi_user=false
> many, many, many PARALLEL and RULE hints in the warehouse load procedures.
> NO table statistics.
>
> The warehouse was designed years ago by someone very well-versed in RBO.
> Management has insisted we leave it alone in its oddly-configured state
> because well, it works. It might not be efficient, and we've had moments
> with concurrency, but the thing works. It might take all weekend to process
> the load, but it runs, and it runs consistently. It is important to note
> that the production warehouse is NOT on a dedicated server. It's odd to
> see up to 30 parallel slaves on a 4-cpu machine but that's just the way it
> is.
>
> My first task is to prepare the data load for a 10g migration. To this end,
> I've created the 10g development environment and copied all of the
> procs/data. I gather statistics on the tables and the procs seem to work
> fine with current statistics and the RULE hints removed.
>
> In the past, the users have used PARALLEL hints to force parallel loading,
> often with 6 or 8 slaves on a 4-cpu box, even with multiple processes
> running.
>
> My question lies with the best practices for invoking parallel. We will be
> moving the production warehouse to a machine with at least 8 CPU. In our
> development environment, I set
>
> parallal_adaptive_multi_user=TRUE
> parallel_max_servers=8
> cpu_count=8
>
> I'm wondering where to invoke parallel processing. If I compile the procs
> without hints and execute, they run without parallel. If I ALTER SESSION
> FORCE PARALLEL DML, I get terrific results. I'm not sure if that's the best
> way to enable parallel processing (and let parallel_adaptive_multi_user do
> its thing), or if I should be setting the degree of each and every segment.
> I'm sure that in this 10g and above world, parallel hints are not the way
> to go.
>
> Is there a 'best practice' for handling this?
>

-- 
Sent from Google Mail for mobile | mobile.google.com

Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 14 2009 - 12:43:04 CDT