Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: [SPAM] Re: Partitioning best practices
Well, I don't have the insights Jonathan does, but I'll take a shot
here, with regards to parallelism and when to parallelize.
In my opinion, parallelism is something you use when you are dealing with vast amounts of data, and you really have no choice but to chomp through it all, regardless of whether it's a load or a query. Also, as you discovered, parallelism is something that does not play nice with resources, and sharing them. It, by design, says "I'm going to throw everything I can at this problem and get it done." It's not really interested in playing nice or sharing resources.
Now, some thoughts on how to use parallelism effectively, and avoid the resource crunch. First, how many CPUs does your box have? Of those, how many are you willing to dedicate to parallel processing? (Assume that this number of CPUs will NOT be available to other users for other work.) Look at the parallel_max_servers parameter. Try setting it to the number of CPUs you want to dedicate to parallel processing. Next, look at your DOP (degree of parallelism). To be safe, your DOP should be 1/2 of parallel_max_servers, since there are cases where (depending on the parallel access path invoked) that you'll get Oracle trying to create up to 2 slaves per degree of parallelism.
Finally, consider that all the above assumes only one session is attempting to parallelize it's work. Running multiple parallel sessions is generally a recipe for disaster, unless you've got endless resources available. (And if you had endless resources available, I suspect you wouldn't have asked the question in the first place...;-))
Hope that helps,
-Mark
PS Jonathan (or anyone else) anything to add, subtract, or correct?
-- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning "A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects." --Robert A. Heinlein -----Original Message----- From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of tboss_at_bossconsulting.com Sent: Friday, September 01, 2006 3:18 PM To: jonathan_at_jlcomp.demon.co.uk Cc: anuragdba_at_gmail.com; oracle-l_at_freelists.org Subject: [SPAM] Re: Partitioning best practices Importance: Low JL We think along the same lines :-) My 2 cents on indexes: we depend on using alter table exchange partition to load data in, and the existence of global indexes increases the time it takes this command to execute from 5-10 seconds to 17-20 minutes. We've eliminated all global indexes as a result (even where they may be useful) and gone with local indexes (mostly bitmaps) in all cases. Hasn't really affected performance too badly. Followup question for you JL in re parallelism: we're having a hell of a time deciding how to use parallelsm in regular operations. When we have it turned on, any query immediately pops up 8 parallel engines and completely hogs all the CPU and i/o channels to the data, effectively serializing database access and killing overall performance. Is the only way to really figure out what the best solution is to just do trial and error with degrees and options til you find a workable solution? Or do you even use parallel server outside of loading? bossReceived on Tue Sep 05 2006 - 09:22:47 CDT
>
>
> First decide
> a) why you want to partition
> b) how any particular form of partitioning gets you a benefit.
>
> e.g. If it's for ease of loading and maintenance, then a typical
> strategy will be range partitioning with local indexes so that you can
> partition by time and use partition exchange and drop.
>
> If it's for performance, then you partition according to the most
> critical queries, introduce global indexes where necessary, worry
> about options for (full and partial) partition-wise joins; and take
> the hit on loading, exchanging and dropping partitions.
>
> In both cases you try to work out how a suitable degree of parallelism
> will benefit you.
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l
![]() |
![]() |