Re: Use of Parallel Query Servers

shekar.kamath_at_mphasis.com (Shekar Kamath) 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

Parallel Query applies to full table scans, sorts, sort joins, and hash joins. Index retrieval is never parallelized. Also Parallel Query is basically useless if your table using full table scan isn't striped one way or another over as much disks as you have CPUs.
Just 'switching on' Parallel Query is not going to help, if you didn't take Parallel Query into account when you designed database and server.

Sybrand Bakker
Senior Oracle DBA Received on Tue Jul 22 2003 - 16:06:45 CEST