RE: [SPAM] Re: Partitioning best practices
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <tboss@xxxxxxxxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 5 Sep 2006 10:22:47 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
tboss@xxxxxxxxxxxxxxxxxx
Sent: Friday, September 01, 2006 3:18 PM
To: jonathan@xxxxxxxxxxxxxxxxxx
Cc: anuragdba@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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.
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: [SPAM] Re: Partitioning best practices
