Re: Optimal degree of parallelism

"Saikat Chakraborty" <saikatchak_at_hotmail.com> wrote

> One of my fellow DBA is telling me:
> <QUOTE>
> As the general rule, the optimal degree of parallelism can be safely set
> to n-1 where n is the number of processors. That means 3 not 4.
> </QUOTE>
>
> I serached google, oracle documents and metalink for this "general rule"
> but never found any reference to it.
> Usually I set number of parallel slaves in powers of 2 and never set
> this to a odd value.
> Is it a myth or I am missing something ?

It is more than a myth. It is utter bullshit.

Fact. CPUs determine capacity. If there is sufficient capacity on a single CPU machine, PQ can be used on it.

PQ is nothing but threading the query. Now threads seem to be familiar ground to most. How many threads are there *now* running on your Windows/Linux desktop? Just one 'cause you have a single CPU desktop? Of course not!

So just why in hell do many people seem to think that PQ can only be used if you have more than one CPU and that the number of PQ slaves may not exceed the number of CPUs?

For friggen sake - PQ slaves does *NOT* perform processor binding. Take a look at it on the NT kernel and HP-UX kernel and see for youself.

Google on my rants on this exact subject in this exact forum which I have posted numerous times.

As for not using odd numbers... there is *NO* technical reason I can think of why that should be less favourable than an odd number. I often use odd numbers myself when writing server software that use thread pooling (5 threads in a pool is for some obscure reason what I normally use).

To address the topic. What is the optimal degree of parallelism? There are a couple of factors that govern that.

CPU capacity. How many horses are there? If the CPU (or CPUs) is running at 80%, then it would be unwise to use PQ as that that will redline the CPU (or CPUs).

How many reader threads (i.e. PQs reading data) can you have without bottlenecking the disk i/o subsystem? You will find that for example 5 threads is faster than 7 threads - and when using 10 or more the performance actually goes downs (you will also see an increase in CPU waiting for i/o times).

The cost of serialising. Parallel processing with PQs end in serialising the data output from the PQs. It is slower to serialise 100 PQs than 10 PQs. (but again, the cost of that could be insignificant depending on the total cost)

The cost of parallelising. Oracle needs to determine how to parallelise (i.e. what range of data to give to each process). This is usually not an issue (but can take a second or more for Oracle to perform). What you need to be careful though is that you telling Oracle to use 10 PQs can result in Oracle using 20 PQs - this happens in cases like a "create table nologging as select /*+ parallel(10) */". Oracle creates another 10 PQs to handle the create table part to prevent a serialisation bottleneck with the actual writing the new table's data.

There are more factors too.

Often you will find that some PQs are idle - this often happens when they get a bunch of empty blocks to scan. Thus you may hope that running 10 PQ's on a 10 million table will have each PQ processing a million rows. Instead, you can have one PQ doing 5 million rows, a few others doing nothing, with the other 5 million rows unevely distributed among the 2 or 3 remaining PQs. (think high water mark)

Then there are o/s specific issues and differences that you need to be aware of - e.g. it is more expensive to run PQ on a Unix box than on a Windows box. PQ is a process on Unix and a thread in Windows. The former uses more resources (like duplicating the data and code segments) than the latter.

So to find the optimal degree of parallelism depends on a host of factors. And these are unique to your platform and your data. No magic wands. No magic numbers.

--
Billy