Several Oracle dynamic performance views provide important information that’s useful for monitoring and tuning parallel SQL execution. The views that you need to be aware of are shown in the following list:
V$PQ_SYSSTAT |
V$PQ_SESSTAT |
V$PQ_SLAVE |
V$PQ_TQSTAT |
V$SYSSTAT |
V$SESSTAT |
V$PX_PROCESS |
V$PX_SESSION |
V$PX_SESSTAT |
V$PX_PROCESS_SYSSTAT |
Several of these views are new with the release of Oracle8i. V$SYSSTAT and V$SESSTAT have been around for a while; they contain general statistics, some of which apply to parallel execution. The other views return information specific to parallel execution. The names of the new parallel execution views all begin with V$PX, while the names of older parallel query views begin with V$PQ. For the most part, the new V$PX views will eventually replace the older V$PQ views. Table 5.1 describes the relationship between these two sets of views.
Table 5-1. Relationship of V$PX Views to V$PQ Views
New in Oracle8i |
Oracle8 |
Comments |
---|---|---|
V$PX_SESSION |
N/A |
— |
V$PX_SESSTAT |
N/A |
— |
N/A |
V$PQ_SESSTAT |
V$PQ_SESSTAT eventually will be dropped. |
V$PX_PROCESS |
V$PQ_SLAVE |
V$PQ_SLAVE eventually will be dropped. |
V$PX_PROCESS_SYSTAT |
V$PQ_SYSSTAT |
V$PQ_SYSSTAT eventually will be dropped. |
N/A |
V$PQ_TQSTAT |
V$PQ_TQSTAT eventually will be renamed V$PX_TQSTAT. |
The following sections explain how to use the information from the dynamic performance views to make parallel execution tuning decisions.
Only the most important information returned by each view is described in this chapter. For detailed information on all the statistics, refer to Oracle Corporation’s Oracle8i Reference Manual.
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 Servers Busy statistic tells you the number of parallel slave processes that are currently busy executing parallel SQL statements. You can use this statistic to adjust the values of PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS.
If the Servers Busy statistic is consistently higher than the value of the PARALLEL_MIN_SERVERS initialization parameter, then you should increase the value of PARALLEL_MIN_SERVERS to match the Servers Busy value. There’s no point in allowing the number of servers to drop below what you are consistently using.
If the Servers Busy statistic is consistently equal to the value of the PARALLEL_MAX_SERVERS initialization parameter, then your parallel execution operations are being constrained by that parameter, and you should consider increasing its value. However, before increasing PARALLEL_MAX_SERVERS, make sure that you have sufficient CPU resources to run the additional parallel slave processes that will result. If your CPU is heavily loaded, increasing the number of parallel slave processes may cause CPU bottlenecks to occur.
The Servers Started statistic indicates the total number of parallel slave processes started on the instance. Likewise, the Servers Shutdown statistic indicates the total number of parallel slave processes shut down on the instance. If the values for these statistics are increasing consistently over time, that’s an indication that Oracle is stopping parallel slave processes that are later going to be needed again. You’ll take a performance hit from all the startup and shutdown activity. In such a case, you should consider increasing the value of the PARALLEL_SERVER_IDLE_TIME initialization parameter. This will cause Oracle to wait longer before stopping idle parallel slave processes and should reduce the constant need to start new ones.
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:
This column shows the name of each statistic.
This column provides the value of the statistic for the previous SQL statement that you executed. If your SQL statement didn’t execute in parallel, this view will return in the LAST_QUERY column for all the statistics. A query to a data dictionary view does not affect the value in the LAST_QUERY column. Therefore, if you query the V$PQ_SESSTAT view twice in succession, you will still get the same result. Also, if you query any other data dictionary view between two queries to this view, the values in the LAST_QUERY column will not be affected.
This column provides the value of each statistic for the entire session. It’s essentially the total of the statistic values from each SQL statement executed from the time the session was created.
Table 5.2 describes the most important of the V$PQ_SESSTAT statistics in terms of monitoring parallel execution.
Table 5-2. Important V$PQ_SESSTAT Statistics
Statistic |
Description |
---|---|
Queries Parallelized |
The number of queries that were run in parallel |
DML Parallelized |
The number of DML operations that were run in parallel |
Server Threads |
The total number of parallel slave processes used |
Allocation Height |
The requested number of slave processes per instance |
Allocation Width |
The requested number of instances |
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.
Table 5-3. V$PQ_SLAVE Statistics
Column |
Description |
---|---|
SLAVE_NAME |
Name of the parallel slave process |
STATUS |
Current status (BUSY or IDLE) of the parallel slave process |
SESSIONS |
Number of sessions that have used this parallel slave process |
IDLE_TIME_CUR |
Amount of time spent idle while processing statements in the current session |
BUSY_TIME_CUR |
Amount of time spent busy while processing statements in the current session |
CPU_SECS_CUR |
Amount of CPU time spent on the current session |
MSGS_SENT_CUR |
Number of messages sent while processing statements for the current session |
MSGS_RCVD_CUR |
Number of messages received while processing statements for the current session |
IDLE_TIME_TOTAL |
Total amount of time this slave process has been idle |
BUSY_TIME_TOTAL |
Total amount of time this slave process has been active |
CPU_SECS_TOTAL |
Total amount of CPU time this slave process has used to process statements |
MSGS_SENT_TOTAL |
Total number of messages this slave process has sent |
MSGS_RCVD_TOTAL |
Total number of messages this slave process has received |
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,
2msgs_sent_total, msgs_rcvd_total
3FROM 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
2FROM v$pq_tqstat
3ORDER 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
2FROM v$pq_tqstat
3ORDER 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
2WHERE statistic# IN (167,168,169)
3GROUP 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#,
2degree, req_degree
3FROM 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:
PARALLEL_MIN_SERVERS |
PARALLEL_MAX_SERVERS |
PARALLEL_SERVER_IDLE_TIME |
See Section 5.2.1 for details.
18.219.239.118