Monitoring the Use of Oracle Parallel Query
Oracle Tips by Burleson Consulting
There are several STATSPACK tables and v$
views that can be used to monitor the activity of the parallel query
background processes. Unfortunately, parallel query activity is only
measured at the database level, and you cannot find the specific
tables that are the target of the parallel query. Let’s begin by
looking at the v$pq_tqstat view, and then we'll see the
STATSPACK methods for measuring parallel query activity.
Monitoring Parallel Execution Activity
Oracle provides a v$ view that can be
used to show the behavior of individual parallel query processes.
The v$pq_tqstat view shows the execution pattern for the
parallel query and the use of parallel query slave processes for the
Here is the SQL statement to display the most
recent parallel query execution details.
'Prod', 0, 'Cons', 1, 3)
Here is some sample output that was retrieved
immediately following a parallel query:
SERVER_TYP PROCESS NUM_ROWS
---------- ---------- ---------- ----------
0 Producer P003 173
0 Producer P001 188
0 Producer P004 219
0 Producer P002 197
0 Producer P000 777
0 Consumer QC 796
This listing shows five parallel query
“Producer” processes, which translate into parallel query slaves,
with the OS process names P000 through P004. In this case, we know
that the P000 process is the parallel query coordinator because it
is labeled a “Consumer” and because it contains the sum of the rows
retrieved by processes P001 through P004. The TQ_ID column refers to
the parallel execution step. In the case of this query, we had a
single full-table scan, so all values are zero. In a more complex
query with a parallel full-table scan, we would see multiple steps
in the TQ_ID column.
As a general rule, we look for an equal
number of rows processed by each parallel query slave. If a single
parallel query slave receives a disproportional amount of rows, then
the entire query will take longer to complete. A disproportional
distribution can sometimes occur when the CBO statistics are not
current because the tables and indexes have changes since last being
analyzed. You might also see an uneven distribution of rows when the
target table is partitioned and the partitions are of unequal sizes.
The v$pq_tqstat view is most useful
when you suspect that a parallelized query is not optimized, and it
will provide sufficient detail to show the exact operations within
the parallel row extraction process.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.