Parallel DML on a 2 CPU box

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:<3b5a4f0e_at_usenet.per.paradox.net.au>...
>
> I've actually had a query *slow down* by specifying a degree of parallelism
> on a single CPU box. And that wasn't a fantasy.
>
> HJR
>

I've have seen the same on HP's UNIX box that had 2 CPU's.

In my case the table was divided into four partitions over four different disks. The query was like
> select a, b, count(distinct c) from test_table group by a, b;
and the table had over 13 000 000 rows. Oracle optimizer decided to use four processes to process the query, one process for one partition. But when I ran it using only one process it ran over three times faster!

I was unable to find the reason for this behavior. The partitions were on different disks and the disks were using dedicated fast wide LVD SCSI busses. I checked the logical volume manager and each logical disk corresponded to one physical disk, as expected. Maybe something wrong in "init.ora" parameters, I don't know.

--
Heikki Siltala
http://www.heikkisiltala.com
Note: replies to "peaceprogress_at_yahoo.com" are ignored due spamming!