Re: Parallel DML on a 2 CPU box

You don't give a version number for Oracle, but I suspect it was not 8.1

Earlier versions has a problem with distinct() in parallel queries. Your execution path was probably to select all rows, then distribute ALL the rows by ranges of (a,b) across
a second layer of slaves - resulting in
millions of messages, with continuous
TQ blocking - followed by the sorting
at the higher level do the distinct.

Newer versions of Oracle are able to
handle some of the grouping and distinct at the lower level, resulting in much less message passing, contention and task
switching.

When testing parallel query, start a new session, run the query, then look at
v$pq_tqstat - (see my website for further details).

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Heikki Siltala wrote in message ...


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