Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partitioning best practices

Re: Partitioning best practices

From: Christian Antognini <>
Date: Tue, 5 Sep 2006 17:44:24 +0200
Message-ID: <>

Hi Mark

Just some comments...

> 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.

Yes. If other possibilities exist they take precedence over parallelism.

> 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.

IMHO the problem is not resource sharing itself, in fact you can, to some extent, plan it. In this area I hope there will be interesting enhancements in 11g...

The major problem, especially if you are dealing with online queries (typically reports), is to guarantee specific performance. Sometimes is better to decrease the DOP, i.e. sacrificing top performance, but to be sure to have at least some PX slaves.

> 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.

Today CPU power is seldom a problem. Most systems using PX are I/O bound. If 5 years ago I used to setup parallel_max_servers in the range 2*cpu_count and 4*cpu_count, today I'll go, at least, with 4*cpu_count to 8*cpu_count. Of course sufficient I/O bandwidth must be available! How much? I'll say, on a good system, 100MB/s for each cpu.

> 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.

IMHO the DOP should be much smaller than parallel_max_servers. I'll say, at least, 4-8 times smaller. Only exception is when it's known there's a single PX operation running concurrently.

> 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.

As I said, IMHO, DOP should be much smaller than parallel_max_servers. Therefore it's not a problem to start many PX operations concurrently.

This is also important because, sometimes, also with few PX slaves you can dramatically increase the performance. E.g. I had the following case a couple of weeks ago with a FTS (note that difference between serial and DOP=2):

- serial: 1750 sec.
- DOP=2: 450 sec.
- DOP=4: 250 sec.
- DOP=8: 150 sec.


Received on Tue Sep 05 2006 - 10:44:24 CDT

Original text of this message