Re: Parallel query performance worse than without it

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 2 May 2002 23:50:53 +0100
Message-ID: <1020379836.19934.0.nnrp-13.9e984b29@news.demon.co.uk>

Your test case is too simple - it is getting all the overheads of PQ with none of the benefit.

Although PQ is accessing the table in parallel, all the rows ultimately are being passed through the single pipeline of the front_end process.

Try something like:

select column_with_few_values, count(*) from table group by column_with_few_values.

The target is

    parallel scanning
    parallel number crunching
    small number of messages to pass around

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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



Andy wrote in message <8d4033cd.0205021424.61cdbccb_at_posting.google.com>...


>Oracle V806.

>

>I am doing some testing with parallel query:

>

>optimizer_mode=rule, parallel_max_servers = 8, parallel_min_servers =

>4 in the init.ora file.

>The server has 4 cpus.

>

>These are the results:

>

>select * from cptrancopy;

>281079 rows selected

>

>             No Parallel Parallel=4

>No statistics     02:10.3 02:38.3

>Statistics=10%     01:57.0 02:14.8

>Statistics=50%     01:55.2 02:22.7

>Compute statistics  02:35.1 02:39.2

>

>Explain plan extract for parallel queries:

>

>Execution Plan

>----------------------------------------------------------

>   0      SELECT STATEMENT Optimizer=RULE (Cost=1679 Card=281079 Bytes

>          =78702120)

>

>   1    0   TABLE ACCESS* (FULL) OF 'CPTRANCOPY' (Cost=1679 Card=28107

>:Q28000

>          9 Bytes=78702120)

>

>

>

>   1 PARALLEL_TO_SERIAL            SELECT /*+ ROWID(A1) */

>A1."PTRAN_KEY",A1."P

>

>TR",A1."POSTFL",A1."CLNO",A1."ACT",A

>

>

>The Oracle manual states "Parallel execution is useful for operations

>that access a large amount of data by way of large table scans."

>

>So, why the worse performance? It's a straightforward test, I expected

>it to work.

>

>Thanks

Received on Thu May 02 2002 - 17:50:53 CDT