Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 259

Optimizing Parallel Operations
Functionalities seldom do work out of the box; most functionalities have to be optimized for the type of operation
based on several factors such as size of database, size of data being retrieved, availability of resources, concurrency
of users, and so forth. Parallel operations are also not an exception to this. Some of the basic troubleshooting of the
parallel query is explained following:

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. 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 (parallel query option) is being used. The ROWID hint is one of the internal
methodologies used to process PQO queries.

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.

3. From the session that the query was executed, executing the following statement will, based

on the statistics collected, provide an indication of whether 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.

SQL> SELECT * FROM V$PQ_SESSTAT;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 3
DML Parallelized 0 0
DDL 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

11 rows selected.

From the preceding 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.