Re: Use of Parallel Query Servers

If you are firing that many queries you better be using bind variables and parsing the query once and rebinding, and executing many times and NOT closing the cursor. Doing that will help you immensely lower CPU usage. You are sending the queries sychronsly and the type of queries you are sending are not suited for doing parallel queries on (" single row from the a table and uses the primary key in its WHERE predicate"). Parallel queries are more suited for full table scan or large sorts. Using parallel query in your case will make each query take longer.

The reason you see a cpu get busy is because you are sending the queries one after another and not at the same time. I think you would dramatically speed things up by using bind variables etc. as I have stated before.(odds are over 90% that you are not using bind variables given your high CPU usage)
Jim

"Shekar Kamath" <shekar.kamath_at_mphasis.com> wrote in message news:77ccfdb4.0307220250.2844a7a4_at_posting.google.com...
> Hi,
>
> We are attempting to switch on the parallel query servers option in
> Oracle,So far we have not succeeded in doing.Was just wondering
> whether its helpful in our case. Following is the configuration of
> the machine on which the Oracle server is installed.
>
> Dual CPU Intel Xeon P4 1.8 GHz,
> 1 GB RAM, SCSI HDD.
>
> Our application is firing multiple queries one after the other in
> sequence, each of the individual queries is not complicated and is
> required to retrieve a single row from the a table and uses the
> primary key in its WHERE predicate. Total number of such queries being
> fired is close to 46000 in a sigle sequential operation.
>
> What we notice is that the server is only making use of a single CPU,
> the other CPU is essentially idle. The CPU getting used reaches close
> to 90% utilisation and the other CPU remians idle during this entire
> duration.
>
> We tried switching on the parallel query option by setting the
> parallel_automatic_tuning parameter in init.ora to true. However even
> this is not helping. The server is still utilizing only a single CPU.
> The outputs from v$pq_slave, v$pq_tqstat returns zero rows.
>
> Any clues on what we may be doing wrong?
>
> -Shekar
Received on Tue Jul 22 2003 - 15:07:30 CEST