Re: Optimal degree of parallelism

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0309021109.24b40979_at_posting.google.com>...

> Which book is that Noons? I have not seen any literature that says
> "thou cannot run PQ to achieve performance improvements on a single
> CPU platfom" myself.

Better ask the "fellow DBA" of the OP. I've heard this one myself in a number of forms. One of them was the recommendation of "someone at a IOUG meeting" many years ago when PQ started. Had to fight that one many times at a site a while ago where the local DBAs got stuck into me because I wanted to run queries with more "parallelism" than CPUs...

I don't have all the books out there (there are some publishers that I refuse to spend $$$ on anyways), but I'll bet there will be one somewhere that has a recommendation for PQ degree along these lines. These myths usually come out of context and quoted without any qualification. It might even be the case the source for it made sense at the time, while nowadays it doesn't. But it gets perpetuated anyway.

Many years ago I remember having this same chat with Barry Mathews from Oracle Australia, who had been doing some testing of PQ on his own in single CPU SUN boxes. He was one of the few people at the time maintaining the degree of PQ was not directly related to the number of CPUs and it was perfectly kosher to use PQ in single CPU boxes. In which we agreed 100%.

>
> Here are the stats (PQ and NON-PQ in elapsed seconds):
>
> RUN# PQ NON-PQ
> 1 29.06 36.01
> 2 15.08 21.09
> 3 16.04 23.08
> 4 15.05 23.00

Have you tried this with older versions? There used to be all sorts of "features" with load balancing on PQ. But once a good patch set was found, it was very much like the above.

>
> BTW.. not shabby. A 1.4 million table FTS'ed and aggregated in 15
> seconds. And this on an older desktop platform...

Hey! It's an AMD!... :D
PS: as an aside, for a long time I've maintained that CPUs with larger caches are eminently suitable to database servers, particularly with products of very large working set like Oracle. Always had objections from the CPU mob.

It was quite rewarding to hear the folks from Intel at the recent AUSOUG yearly bash confirm this officially: apparently, their latest chip design can cope with 8Mb and more of L2 cache. Which makes Oracle positively fly in that platform.

About time someone got away from the 128Kb cache models and such. AMD showed the way a few years ago.
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed Sep 03 2003 - 01:04:22 CDT