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@ mphasis.comwrot e in message
news:77ccfdb4.0 307220250.2844a 7a4@posting.goo gle.com...
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@ mphasis.comwrot e in message
news:77ccfdb4.0 307220250.2844a 7a4@posting.goo gle.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_automa tic_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
>
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_automa tic_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