Re: What is Parallel DML?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Thu, 26 Jul 2001 09:43:50 +0200
Message-ID: <9johq6$g0n$1@ctb-nnrp1.saix.net>

"Howard J. Rogers" <howardjr_at_www.com> wrote

> Most people are not going to be working in a Parallel
> Server environment, using MIPS processors. Rather more people
> *are* going to be running in dedicated server mode on Intel chips.

Disagree. For one, PQ in OPS is no different than PQ on non-OPS. The reason is that PQ in both environments can run on a single CPU or SMP boxes. So simply because it is OPS does not change the fact that PQ runs, and runs well, on a single CPU platform.

Secondly, the vast majority of Oracle server platforms out there are not Intel-based. Not that the type of processor (Intel, MIPS, Sparc, whatever) has much to do with PQ - you can not even get a rough indication of what the capacity would be like based on CPU make and model alone. You need to look at the rest of the hardware, the operating system, the applications and services, all TOGETHER to make the call on performance.. and decide if PQ can solve performance problems or not.. and usually it will be more than likely that PQ CAN solve some of these performance problems.

I find it strange just you simply discard PQ as a valid option for a single CPU box. PQ one of the perofrmance solving tools - use it.

> Apparently there you found a degree of parallelism of 2 able to cripple your
> machine.
> I rest my case.

Permit me to take your feet from your mouth. I did not say that. I said that two PQ's on Oracle 7.1 on NT 3.5 on a Pentium 200 MMX is FASTER!!! than not using PQ. Given the limited performance and processing capacity of a Pentium 200, this proves BEYOND A SHADOW OF A DOUBT that PQ works, and works WELL, on a single CPU platform.

Do you want me to go in the details? Why not. Even though it has been more than a couple of years, my memory is surving better than my hairline.

I loaded the Oracle 7.1 table with a bunch of detailed claim rows for a specific medical aid plan, for a specific month. I choose the smallest of the plans - where about 35,000 claims are received per month. The easiest way to test PQ is a simply SELECT COUNT(*). This uses a full table scan. The SELECT COUNT(*) without PQ took as I recall, about 40 to 60 seconds to complete. Shutdown and

reboot the machine. Then I ran the SELECT COUNT(*) with 2 PQ's. The response was
within a few seconds. After that I experimented with more PQ's - running NT's
Performance Manager to monitor perfomance. Conclusion : 2 PQ's on that specific

single CPU platform (a friggen 200mhz Pentium!) provided the best performance when doing this table scan. Now tell me again that PQ does not work on a single CPU platform.

> Jonathan's post was not just a description of the pros and cons of PQ: it
> explained very nicely that whilst you *might* get PQ producing benefits on a
> single CPU box, *it would all depend*. It requires thought, and testing,
> and measurement as to whether to enable it at all.

So..? What is so earth shattering about that? So is the question to use partitioning, clusterering, bitmap indexes, stored procs vs. plain sql, etc. etc. Hell, I would think that some thought and testing should go into all design and development efforts (which is probably news for the new generation and new age developers and DBAs).

However Howard, you DID say that PQ is pointless on a single CPU platform. Again, THAT is what I take exception too. PQ IS NOT POINTLESS ON A SINGLE CPU PLATFORM. Period.

Anyway, this pointless bickering is making me thirsty. You buying or what..?

--
Billy

Received on Thu Jul 26 2001 - 02:43:50 CDT