Re: Experience with parallel query option

In article <3me5la$sbh_at_caladan.restena.lu>, marc.feidt_at_eurostat.cec.be says...
>
>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.
>
>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.
>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)".
>
>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.
>
>Questions:
> 1) Does this suggest that the parallel option is only useful for
 huge
>queries (very many rows or long running queries)?
> 2) Is it useful to set the degree to a value higher than the number
 of
>processors on the server?
> 3) Has anyone made other experiences with this option?
> 4) Was this the correct way of testing the option?
>
>Thanx for any comments
>

Marc,

I am working on a project that will be using PQO for a large Decision Support database and I have been rather disappointed with the lack of information available on PQO either from Oracle or via 3rd parties.

Having said that, I was able to get my hands on some information about PQO and I look forward to working with it (we already have several production instances of Oracle and Sybase up and running so these products are not new to me, just PQO).

I hope you don't become offended by my candor in answering your question #4 "Was this the correct way of testing the option?" rather directly but the answer is "definately not." Testing should be as close to "the real thing" as possible. Unless you make a habit of creating cartesian product queries what does using them in test tell you? Test cases should include a baseline (ie no PQO) and go up from there. Try single tables, two table joins, three table joins, etc. Include simple queries and complex ones. Then compare the results. (Graphing these test results would be a good idea, too.)

I realize that a development environment is usually no where near the production envorinment--and that is if you even have one at all! However, you can still improve your test method. But first you need to understand that the key to optimal PQO performance isn't only in the number of engines.  How the table is striped is also critical. If you have four engines and don't stripe your data (ie one device) then those engines won't be of much benefit as they wait on eachother's I/O.

Aside from the query benefit of PQO there are also improvements to be gained in parallel loading and parallel index builds. You should test these too when deciding if PQO is right for your instance.

Good luck.

Ken Received on Fri Apr 14 1995 - 00:00:00 CEST