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?

boss




> 

> 

> 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