Re: Experience with parallel query option

Marc FEIDT (marc.feidt_at_eurostat.cec.be) wrote:
: I have played around a little bit with the parallel query option of ORACLE
: 7.1.4.
: I found out that for short queries there is a performance decrease when using
: the parallel queries. This seems logical as there must be some overhead for
: splitting the query over several processors.

Particularly if you don't set PARALLEL_MIN_SERVERS high enough (which I suspect you didn't).

: The query I used was very simple:
 

: select count(*) from table,table;
 

: I used table,table in order to get the cartesian product. This allowed me to
: have a big result set without having to have very big tables.

But does not model a likely real-world scenario and is therefore likely to give misleading results (as I believe it did).

: I executed this query and then I gradually incremented the number of rows in
: table. In SQL*PLUS with "set timing on" I measured the times once without
: parallel once with parallel (degree 2 as my server has 2 processors). To
: change to parallel I used "alter table table parallel (degree 2)".

How many query slaves did you get? Nine? You would get four scanners plus one coordinator, but I am not sure whether you would get four joiners or two joiners (and I don't have an instance handy to test with).

: As I said before at the beginning the queries were faster without the parallel
: query option. From the approx. count(*)=12000000 on, the parallel query
: started to be faster than the normal one.

I find that surprisingly high. I suspect you had unrealistically short rows. The breakeven point would be more meaningfully measured in blocks rather than rows.

You had four scans going on (presumably) one spindle! Was your disk up to it? For SELECT COUNT(*) I believe number of spindles is more important than number of cpus (assuming your tables are bigger than your cache usage). I'll bet you got much better disk service times on your non-parallel test.

: Questions:
: 1) Does this suggest that the parallel option is only useful for huge
: queries (very many rows or long running queries)?

Right conclusion (assuming that "parallel option" does not encompass Parallel Server Option). Dubious method of arrival, however.

: 2) Is it useful to set the degree to a value higher than the number of
: processors on the server?

I believe there may be cases where this is useful. I don't yet have a good understanding of what they are, however.

: 3) Has anyone made other experiences with this option?

Surely! :-)

: 4) Was this the correct way of testing the option?

Too simplistic IMHO.

: Thanx for any comments

--
graeme
--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.