best way to invoke parallel in DW loads?
Date: Mon, 14 Sep 2009 09:21:41 -0400
Message-ID: <ae2c46ba0909140621s429cc761n2024d46742146df4_at_mail.gmail.com>
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?
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 14 2009 - 08:21:41 CDT