Re: best way to invoke parallel in DW loads?
Date: Mon, 14 Sep 2009 18:43:04 +0100
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 220.127.116.11
> Solaris 9
> 4x1200mhz sparcv9 cpu
> parallel_max_servers - 70(!)
> 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
> 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
> 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
> 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-lReceived on Mon Sep 14 2009 - 12:43:04 CDT