Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Parallel DML on a 2 CPU box

Re: Parallel DML on a 2 CPU box

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 27 Jul 2001 14:13:12 +0100
Message-ID: <996239421.18917.0.nnrp-13.9e984b29@news.demon.co.uk>

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!
Received on Fri Jul 27 2001 - 08:13:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US