The V$PQ_SYSSTAT view provides instance-wide statistics related to parallel execution. You can use these statistics to help determine appropriate values for the PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS initialization parameters.

The following example shows the types of statistics that you can get from the V$PQ_SYSSTAT view:

SQL> SELECT * FROM v$pq_sysstat;

STATISTIC                          VALUE
------------------------------ ---------
Servers Busy                           4
Servers Idle                           0
Servers Highwater                      5
Server Sessions                        5
Servers Started                       10
Servers Shutdown                       6
Servers Cleaned Up                     0
Queries Initiated                      5
DML Initiated                          0
DFO Trees                              5
Sessions Active                        1
Local Msgs Sent                      163
Distr Msgs Sent                        0
Local Msgs Recv'd                    505
Distr Msgs Recv'd                      0

15 rows selected.

The most useful of the statistics returned from this view are the Servers Busy, Servers Started, and Servers Shutdown statistics. These can be used to optimize the settings for the PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS, and PARALLEL_SERVER_IDLE_TIME initialization parameters.

The V$PQ_SESSTAT view provides summary statistics about the parallel statements executed in your session. The following example shows the statistics returned when you query this view:

SQL> SELECT * FROM v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          4             0
Allocation Height                       4             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                        18            18
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                      18            18

10 rows selected.

Columns have the following meanings:

Table 5.2 describes the most important of the V$PQ_SESSTAT statistics in terms of monitoring parallel execution.

These statistics provide information on the number of queries and DML statements that have been parallelized in your session, the number of parallel slave processes requested per instance, and the number of instances requested. After executing a parallel SQL statement, you can query the V$PQ_SESSTAT view to determine whether the statement ran in parallel. Assuming that it did run in parallel, you then can see the number of parallel slave processes that were requested and used.

If you expect a SQL statement to run in parallel, and it doesn’t do so, you need to find out why parallelism wasn’t used. Refer to Chapter 4, to learn about restrictions on the use of parallel execution with various types of SQL statements and about the conditions that force a parallel SQL statement to run serially. If the statement doesn’t execute with the expected degree of parallelism, you need to check the hints or parallel clauses in the statement and/or in the definition of the associated tables and indexes.

The V$PQ_SLAVE dynamic performance view returns one row for each parallel query slave process currently active for an instance. Various statistics are returned on each slave process, including the status, busy time, idle time, CPU used, and so on. Table 5.3 describes each column in this view.

The important statistics returned by the V$PQ_SLAVE view are SESSIONS, CPU_SECS_TOTAL, MSGS_SENT_TOTAL, and MSGS_RCVD_TOTAL. Together, these statistics indicate how evenly the processing load is being distributed when parallel execution is being used.

Here’s an example of the output that you’ll get when you query the V$PQ_SLAVE view:

SQL> SELECT slave_name, status, sessions, cpu_secs_total, 
  2  msgs_sent_total, msgs_rcvd_total
  3  FROM  v$pq_slave;

SLAV STAT   SESSIONS CPU_SECS_TOTAL MSGS_SENT_TOTAL MSGS_RCVD_TOTAL
---- ---- ---------- -------------- --------------- ---------------
P000 BUSY         53              0            2351            4627
P001 BUSY         53              0            2351            4627
P002 BUSY         25              0              73             153
P003 BUSY         25              0              73             153

The values for the four statistics returned by this query should not vary too much between processes. Too much variation indicates an unbalanced workload among the slave processes. Some processes are doing more work than others, which lowers your overall efficiency.

One possible cause of an unbalanced workload is a mismatch between the degree of parallelism used for a DML statement and the number of partitions accessed by that statement. When DML statements access data in a partitioned table or index, a parallel slave process is assigned to each partition. The number of slave processes is limited by the degree of parallelism in effect for the statement. If the degree of parallelism is greater than the number of partitions, then the extra slave processes remain idle throughout the processing of the statement. If there are fewer processes than there are partitions, then some processes will have to process two or more partitions. To balance the workload properly, you should choose a degree of parallelism such that the number of partitions is a multiple of the degree of parallelism. Otherwise, you’ll have an imbalance that results in a loss of efficiency.

For example, consider what happens if a table has four partitions, but the degree of parallelism for a DML statement against that table is only 3. Three slave processes will be assigned to the statement, each process working on one partition. The slave process that finishes processing its assigned partition first will be assigned to the fourth partition. That means one slave process will have to handle two partitions. While that one process is working with the fourth partition, the other two slave processes will be sitting idle. This wastes resources. Therefore, it’s important to choose carefully the degree of parallelism used for a statement.

The V$PQ_TQSTAT dynamic performance view provides detailed statistics on each parallel slave process currently running for an instance. This view shows the producer/consumer relationship between the parallel slave processes and the query coordinator. In cases in which inter-operational parallelism is being used, this view also shows the producer/consumer relationship between different sets of parallel slave processes. The important statistics to note from this view are NUM ROWS (number of rows) and BYTES (number of bytes), processed by each of the parallel slave processes.

As discussed in Chapter 3, the query coordinator divides the work of executing a query into multiple tasks and assigns each task to a parallel slave process. This complete process structure, consisting of the query coordinator and associated parallel slave processes, is called a data flow operation (DFO). Oracle assigns a DFO number to each data flow operation. That DFO number shows up in the V$PQ_TQSTAT view’s DFO_NUMBER column.

The slave processes act as producers and the query coordinator acts as a consumer. The following example shows some useful information from V$PQ_TQSTAT for a parallel SELECT statement:

SQL> SELECT /*+ PARALLEL(emp,4) */ * FROM emp;

    EMPNO ENAME      JOB             MGR HIREDATE       SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- --------
     7654 MARTIN     SALESMAN       7698 28-SEP-81     1250      1400        30
     7698 BLAKE      MANAGER        7839 01-MAY-81     2850                  30
        .
        .
        .

7168 rows selected.

SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
  2  FROM v$pq_tqstat
  3  ORDER BY dfo_number, tq_id, server_type;

DFO_NUMBER     TQ_ID SERVER_TYP PROCESS     NUM_ROWS     BYTES
---------- --------- ---------- ---------- --------- ---------
         1         0 Consumer   QC              7168    329464
         1         0 Producer   P001            1777     81658
         1         0 Producer   P003            1804     82941
         1         0 Producer   P002            1804     82935
         1         0 Producer   P000            1783     81930

In this example, the query was executed using four parallel slave processes. As you can see from the SERVER_TYP column, the parallel slave processes act as producers, and the query coordinator acts as the consumer. The producers and consumers communicate using a queue known as a table queue (TQ). Table queues are identified by ID numbers that are returned in the TQ_ID column. The NUM_ROWS and BYTES columns provide information on the number of rows and bytes handled by each process.

When inter-operational parallelism is used, one set of slave processes acts as producers to a second set of consumer slave processes. The processes in that second set then act as producers to the query coordinator. Thus, you can have multiple layers of producers and consumers. Each layer is represented by a unique TQ_ID value.

The following example shows useful information from the V$PQ_TQSTAT view for a parallel SELECT statement involving inter-operational parallelism:

SQL> SELECT /*+ PARALLEL(emp_test,4) */ * FROM emp_test ORDER BY ename;

    EMPNO ENAME      JOB             MGR HIREDATE       SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- --------
     7698 BLAKE      MANAGER        7839 01-MAY-81     2850                  30
     7654 MARTIN     SALESMAN       7698 28-SEP-81     1250      1400        30
        .
        .
        .

14 rows selected.

SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
  2  FROM v$pq_tqstat
  3  ORDER BY dfo_number, tq_id, server_type;

DFO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS      BYTES
---------- ---------- ---------- ---------- ---------- ----------
         1          0 Consumer   P003                4        197
         1          0 Consumer   P002                3        159
         1          0 Consumer   P001                3        165
         1          0 Consumer   P000                4        208
         1          0 Producer   P007                0         20
         1          0 Producer   P006                0         20
         1          0 Producer   P005                0         20
         1          0 Producer   P004               14        657
         1          0 Ranger     QC                 14       1446
         1          1 Consumer   QC                 14        717
         1          1 Producer   P003                4        194
         1          1 Producer   P000                4        205
         1          1 Producer   P001                3        162
         1          1 Producer   P002                3        156

14 rows selected.

In the preceding output, layer (TQ_ID = 0) is responsible for the table scan, and layer 1 (TQ_ID = 1) is responsible for the sort. After the table is scanned by four slave processes (P004, P005, P006, and P007), the output is distributed among the other parallel slave processes (P000, P001, P002, and P003) for sorting. The query coordinator divides the sort operation into four ranges and assigns one range to each parallel slave process. Each slave process then takes the input that falls within its range and sorts it. The role of dividing the sort into four ranges is called “Ranger” (note this under SERVER_TYP in the previous output). In the output you can see the query coordinator (QC) taking the role of Ranger in layer 0.

In a given layer, if the producers process too many rows as compared to the consumers, that indicates inefficient query processing. This may be caused by a badly written query or by an uneven distribution of data. For example, if you are using a GROUP BY operation and the number of rows processed for one distinct value for the columns in the GROUP BY clause differ widely from the number of rows processed for another distinct value, then you will observe a large variance.

The V$SYSSTAT and V$SESSTAT views provide a large number of generic statistics on database performance. Some of those statistics apply to parallel execution. V$SYSSTAT provides these statistics for the entire system, whereas V$SESSTAT provides them for the current session. The statistics to look at that are relevant to parallel execution all have the string “parallel” somewhere in their names. You can retrieve them using a query like the one shown in this example, which queries V$SYSSTAT:

SQL> SELECT * FROM v$sysstat WHERE name LIKE '%parallel%';

STATISTIC# NAME                                  CLASS      VALUE
---------- ------------------------------------- ---------- ----------
       167 queries parallelized                   32         97
       168 DML statements parallelized            32          0
       169 DDL statements parallelized            32          0

While V$SYSTAT returns statistics by name, V$SESSTAT returns them only by number. As the output from V$SYSSTAT shows, the parallel execution statistics are numbers 167, 168, and 169. These represent, respectively, the number of queries parallelized, the number of DML statements parallelized, and the number of DDL statements parallelized. Using the numbers, you can query V$SESSTAT as follows:

SQL> SELECT statistic#, sum(value) from v$sesstat 
  2  WHERE statistic# IN (167,168,169)
  3  GROUP BY statistic#;

STATISTIC#      VALUE
---------- ----------
       167         60
       168          0
       169          0

3 rows selected.

The information provided by these views is useful for informational purposes but doesn’t really lead to any tuning activity.

The V$PX_PROCESS view provides one row of information for each parallel slave process. The information returned includes the session ID, the serial number, and the status of each of those processes. Here is a sample of the output that you’ll get when querying V$PX_PROCESS:

SQL> SELECT * FROM v$px_process;

SERV STATUS          PID SPID            SID   SERIAL#
---- --------- --------- --------- --------- ---------
P001 IN USE           15 119              10       146
P000 IN USE           14 223              15        35
P003 IN USE           17 236              14       215
P002 IN USE           16 64               13       990

This view will replace V$PQ_SLAVE in a future release. As of the current release of Oracle (release 8.1.6), the V$PX_PROCESS view does not provide all of the information provided by V$PQ_SLAVE. We expect more information to be added to V$PX_PROCESS before V$PQ_SLAVE is made obsolete.

The V$PX_SESSION dynamic performance view provides real-time information on busy parallel slave processes in the instance. This view provides the session ID and the serial number of the query coordinator and of the parallel slave processes. This view also provides information about the requested and actual degrees of parallelism. Here is an example of a query against V$PX_SESSION:

SQL> SELECT sid, serial#, qcsid, qcserial#, server#,
  2  degree, req_degree 
  3  FROM v$px_session;

      SID   SERIAL#     QCSID QCSERIAL#   SERVER#    DEGREE REQ_DEGREE
--------- --------- --------- --------- --------- --------- ----------
       11      7275        11
       14       211        11      7275         1         4          4
        9         9        11      7275         2         4          4
       10       135        11      7275         3         4          4
       13       669        11      7275         4         4          4

When you look at the output from this view, check the requested versus the actual degrees of parallelism. If they don’t match, you should check the number of parallel slave processes available, the maximum number of parallel slave processes allowed (PARALLEL_MAX_SERVERS), and the PARALLEL_MIN_PERCENT setting.

The V$PX_SESSTAT view provides session statistics for all sessions using parallel execution. This view is a join between V$PX_SESSION and V$SESSTAT. V$PX_SESSTAT provides the session identifier, statistics number, and value for the parallel coordinator and for each of the busy parallel slave processes. Here is an example of a query against V$PX_SESSTAT:

SQL> SELECT sid, serial#, qcsid, qcserial#, qcinst_id, degree, req_degree,
 statistic#, value FROM v$px_sesstat;

SID SERIAL# QCSID QCSERIAL# QCINST_ID DEGREE REQ_DEGREE STATISTIC#  VALUE
--- ------- ----- --------- --------- ------ ---------- ---------- ------
 10      43    12        41         1      4          4         22      0
 10      43    12        41         1      4          4         21 144048
 10      43    12        41         1      4          4         20 144048
 10      43    12        41         1      4          4         19      0
 10      43    12        41         1      4          4         18      0
.
.
.

The statistics provided by this view include the amount of CPU time used by each session, the number of DDL and DML statements parallelized for each session, and more. You can use the statistics provided by V$PX_SESSTAT in the same way you use the statistics from V$SESSTAT.

The V$PX_PROCESS_SYSSTAT view provides statistics on parallel execution. Some of these statistics are the same as those provided by V$PQ_SYSSTAT. The following example shows the results of a query against V$PX_PROCESS_SYSSTAT:

SQL> SELECT * FROM v$px_process_sysstat;

STATISTIC                          VALUE
------------------------------ ---------
Servers In Use                         4
Servers Available                      0
Servers Started                       10
Servers Shutdown                       6
Servers Highwater                      5
Servers Cleaned Up                     0
Server Sessions                        5
Memory Chunks Allocated                1
Memory Chunks Freed                    0
Memory Chunks Current                  1
Memory Chunks HWM                      1
Buffers Allocated                     88
Buffers Freed                         76
Buffers Current                       12
Buffers HWM                           16

15 rows selected.

Most of the useful information provided by this view corresponds to the information returned by the V$PQ_SYSSTAT view and can be used to tune the settings for the following initialization parameters:

See Section 5.2.1 for details.