6.9 Troubleshooting parallel processing

---

Some of the basic troubleshooting of the parallel query is explained below.

1. One of the first steps to determine if parallel query is doing any kind of parallel operation is to check the output of an explain plan. If there are no details of parallelism, then parallel query may not be used. The output from the explain plan would indicate if the optimizer has decided on a parallel plan. The PLAN_TABLE in the schema where the explain plan was performed contains a column called OTHER. Checking to determine whether this column contains the SQL generated for use by the slave processes indicates if the parallel query is executing as expected. For example, if this SQL contains hints such as /*)ROWID (PRODUCT)*/, and these hints are clearly not in the original code, then it is likely that PQO is being used. The ROWID hint is one of the internal methodologies used to process PQO queries.

   Note 

   The ROWID hint has a special meaning as it bypasses the buffer cache. In fact, it causes the buffers to be flushed to disk so that direct path I/O can be performed on the base data files.

   Oracle provides two scripts to format the query on the PLAN_TABLE, utlxpls.sql and utlxplp.sql. One formats the output for a serial plan and the other can be used to format a parallel plan. Both of these scripts can be found in $ORACLE_HOME/ rdbms/admin directory.

2. From the session that the query was executed, executing the following statement will, based on the statistics collected, provide an indication of whether or not the parallel query was executed.

   SELECT * FROM V$PQ_SESSTAT;

   The output of this query will indicate if the last query that ran under this session was parallelized.

   STATISTIC               LAST_QUERY    SESSION_TOTAL --------------------- ------------- --------------- Queries Parallelized       0            3 DML Parallelized           0            0 DFO Trees                  0            3 Server Threads             0            0 Allocation Height          0            0 Allocation Width           0            0 Local Msgs Sent            0          50578 Distr Msgs Sent            0            0 Local Msgs Recv'd          0          50570 Distr Msgs Recv'd          0            0

   From the above output, the LAST_QUERY column indicates the last query execution statistics and the SESSION_TOTAL column indicates the values that all queries executed in the current session.

   The relevant statistics from the above query are:

   1. ''Queries Parallelized'' indicates the number of SELECT statements that were executed in parallel. In the above scenario a total of three queries were executed in the current session.

   2. 'DML Parallelized'' indicates the number of DML statements that were executed in parallel. In the current session there were no DML statements executed.

   3. 'DFO Trees'' indicates the number of times a serial execution plan was converted to a parallel plan. In the above scenario a total of three queries were converted from a serial plan to a parallel plan.

   4. ''Server Threads'' indicates the number of parallel query slaves used.

   5. ''Allocation Height'' indicates the degree of parallelism for the instance.

   6. ''Allocation Width'' indicates the number of instances involved in this operation.

3. Yet another troubleshooting opportunity is to check the V$ views for slave activity. Executing the following statement several times will indicate if slaves are running:

   SELECT     SLAVE_NAME,     STATUS,     CPU_SECS_TOTAL FROM V$PQ_SLAVE;

   If the query returns no rows, this would indicate that there are no slaves running. If there were no difference in CPU usage between the two runs, this would indicate that there has been no CPU activity for the sampling period.

---