Re: Optimal degree of parallelism

"Noons" <wizofoz2k_at_yahoo.com.au> wrote

> > So just why in hell do many people seem to think that PQ can only be
> > used if you have more than one CPU and that the number of PQ slaves
> > may not exceed the number of CPUs?
>
> Because "the book" says so? And whatever "the book" says
> shall not be questioned?

Which book is that Noons? I have not seen any literature that says "thou cannot run PQ to achieve performance improvements on a single CPU platfom" myself.

> Well, I'd say there is no such thing as a "fixed" optimal degree
> of anything. There is a middle ground value that should be clamped at,
> depending on the nature of the load, and left alone. Anything over that is
> entering the realm of micro-management. Under that and we're into
> underuse territory. It's a simple engineering principle too.

Yes. And just to show that I ain't simply talking.

AMD 22+ Single CPU Platform. O/S: WinXP Pro. Oracle 9.2.

Partioned table called FOO of 1.4 million rows and 10 partitions (data generated mostly with dbms_random). Which means that the partitions are not evenly loaded.

Table analyzed. No indexes. No constraints.

Then the following query:
SQL> select
  2 count(*)
  3 from (
  4 select
  5 /*+ full(foo) parallel(foo,2) */
  6 substr( product,1,1) CODE1,
  7 substr( product,2,1) CODE2,
  8 sum( amount ) TOTAL,
  9 avg( amount ) AVG,
 10 count(seq) UNITS
 11 from foo
 12 group by
 13 substr( product,1,1),
 14 substr( product,2,1)
 15 )
 16 /

And the same query with the parallel clause removed. Excuse the SQL but I was trying to make it as slow as possible (not that it really worked in slowing it down).

The two queries processes were run for 4 iterations simultanuously (two separate SQL*Plus sessions) to simulate a normal working environment (where two or more processes vie for the same resources and data).

Here are the stats (PQ and NON-PQ in elapsed seconds):

RUN#     PQ          NON-PQ
1        29.06       36.01
2        15.08       21.09
3        16.04       23.08
4        15.05       23.00

After the first run loaded the cache, the timings become very consistent for both. Using 2 PQs resulted in over a 30% improvement in performance according to the last 3 runs.

BTW.. not shabby. A 1.4 million table FTS'ed and aggregated in 15 seconds. And this on an older desktop platform...

--
Billy