Monitoring Oracle Parallel Query with V$ Views

Many Oracle professionals are not aware that Oracle provides system views to show the internal state of parallel queries.



To see how many parallel query servers are busy at any given time, the following

query can be issued against the v$pq_sysstat view:





select
statistic,
value
from
v$pq_sysstat
where
statistic = 'Servers Busy';

STATISTIC VALUE
--------- -----
Servers Busy 30



In this case, we see that 30 parallel servers are busy at this moment. Do not be misled by this number. Parallel query servers are constantly accepting work or returning to idle status, so it is a good idea to issue the query many times over a one-hour period to get an accurate reading of parallel query activity. Only then will you receive a realistic measure of how many parallel query servers are being used.



There is one other method for observing parallel query from inside Oracle. If you are running Oracle on UNIX, you can use the ps command to see the parallel query background processes in action:



ps -ef|grep "ora_p"



In sum, Oracle has several tools that you can use to watch the Oracle parallel query processes in action. This information is very useful when you want to see if your parallelism is stressing your database server.