Very Computer

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

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