Re: Optimal degree of parallelism

wizofoz2k_at_yahoo.com.au (Noons) wrote

> Better ask the "fellow DBA" of the OP. I've heard this one
> myself in a number of forms.

Ditto. Have yet to see this in black-and-white from something that can be considered an official or knowledgable source.

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

All too true.

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

Last I did this was on a 486DX2-66 using Oracle 7.3.2 for NT back in the mid 90's. The results were pretty similar, though the "VLT" was a mere 32 thousand rows or so (with similar elapsed times). :-)

> There used
> to be all sorts of "features" with load balancing on
> PQ.

I cannot recall any... but then I was very much a rookie back then (and very few of the postings in this group made any sense to me).

Load balancing is usually done by the operating system - or should & must be done by the o/s IMO. The reason for me ranting against binding processes to a specific CPU (been there, done that and shot myself in the foot repeatedly until the shotgun was empty).

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

Of course. Intel inside? Nah. AMD instead! ;-)

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

When I posted those results last night I wanted to add something like "cluster dreams"... This AMD system is cheap. HP-UX class L server is not. You take 25% of the purchasing cost and you buy yourself an AMD cluster... and I won't be surprised if that kicks L-class butt.

The downside is of course the risk. I have not so fond memories of running SQL-Server db on a 4 CPU NT box having all kinds of problems... which were eventually traced to the Milex driver for the SCSI array. Nothing to do with either SQL-Server or NT that performed pretty darn well under the circumstances. That's the risk of using PC-like hardware as a server-type platform. It is not as mature IMO as the Unix server hardware. But this is changing.

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

And technology advances like this puts a new meaning to something like grid computing. Methinks that Oracle recognised what oppertunities there are in the future and is gearing their core db product for it.

--
Billy