Oracle Database real-time monitoring feature enables you to monitor the performance of SQL statements while they are executing. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time for a single execution. See Oracle Database SQL Tuning Guide for more details.
After your system has run for a few days, you should monitor parallel execution performance statistics to determine whether your parallel processing is optimal. Do this using any of the views discussed in this section.
In Oracle Real Application Clusters, global versions of the views described in this section aggregate statistics from multiple instances. The global views have names beginning with
G, such as
V$FILESTAT, and so on.
V$PX_BUFFER_ADVICE view provides statistics on historical and projected maximum buffer usage by all parallel queries. You can consult this view to reconfigure SGA size in response to insufficient memory problems for parallel queries.
V$PX_SESSION view shows data about query server sessions, groups, sets, and server numbers. It also displays real-time data about the processes working on behalf of parallel execution. This table includes information about the requested degree of parallelism (DOP) and the actual DOP granted to the operation.
V$PX_SESSTAT view provides a join of the session information from
V$PX_SESSION and the
V$SESSTAT table. Thus, all session statistics available to a standard session are available for all sessions performed using parallel execution.
V$PX_PROCESS view contains information about the parallel processes, including status, session ID, process ID, and other information.
V$PX_PROCESS_SYSSTAT view shows the status of query servers and provides buffer allocation statistics.
V$PQ_SESSTAT view shows the status of all current server groups in the system such as data about how queries allocate processes and how the multiuser and load balancing algorithms are affecting the default and hinted values.
You might need to adjust some parameter settings to improve performance after reviewing data from these views. In this case, refer to the discussion of "Tuning General Parameters for Parallel Execution". Query these views periodically to monitor the progress of long-running parallel operations.
For many dynamic performance views, you must set the parameter
TRUE in order for Oracle Database to collect statistics for each view. You can use the
SESSION statements to turn
TIMED_STATISTICS on and off.
As a simple example, consider a hash join between two tables, with a join on a column with only two distinct values. At best, this hash function has one hash value to parallel execution server A and the other to parallel execution server B. A DOP of two is fine, but, if it is four, then at least two parallel execution servers have no work. To discover this type of deviation, use a query similar to the following example:
SELECT dfo_number, tq_id, server_type, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type, process;
The best way to resolve this problem might be to choose a different join method; a nested loop join might be the best option. Alternatively, if one join table is small relative to the other, a
BROADCAST distribution method can be hinted using
PQ_DISTRIBUTE hint. The optimizer considers the
BROADCAST distribution method, but requires
OPTIMIZER_FEATURES_ENABLE set to 9.0.2 or higher.
Now, assume that you have a join key with high cardinality, but one value contains most of the data, for example, lava lamp sales by year. The only year that had big sales was 1968, and the parallel execution server for the 1968 records is overwhelmed. You should use the same corrective actions as described in the previous paragraph.
V$PQ_TQSTAT view provides a detailed report of message traffic at the table queue level.
V$PQ_TQSTAT data is valid only when queried from a session that is executing parallel SQL statements. A table queue is the pipeline between query server groups, between the parallel execution coordinator and a query server group, or between a query server group and the coordinator. The table queues are represented explicitly in the operation column by
PX SEND <partitioning type> (for example,
PX SEND HASH) and
V$PQ_TQSTAT has a row for each query server process that it reads from or writes to in each table queue. A table queue connecting 10 consumer processes to 10 producer processes has 20 rows in the view. Total the bytes column and group by
TQ_ID, and the table queue identifier, to obtain the total number of bytes sent through each table queue. Compare this to the optimizer estimates; large variations might indicate a need to analyze the data using a larger sample.
Compute the variance of bytes grouped by
TQ_ID. Large variances indicate workload imbalances. You should investigate large variances to determine whether the producers start out with unequal distributions of data, or whether the distribution itself is skewed. If the data itself is skewed, this might indicate a low cardinality, or low number of distinct values.
V$RSRC_CONS_GROUP_HISTORY view displays a history of consumer group statistics for each entry in
V$RSRC_PLAN_HISTORY that has a non-NULL plan, including information about parallel statement queuing.
V$RSRC_CONSUMER_GROUP view displays data related to currently active resource consumer groups, including information about parallel statements.
V$RSRC_PLAN view displays the names of all currently active resource plans, including the state of parallel statement queuing.
V$RSRC_PLAN_HISTORY displays a history of when a resource plan was enabled, disabled, or modified on the instance. The history includes the state of parallel statement queuing
V$RSRC_SESSION_INFO view displays resource manager statistics per session, including parallel statement queue statistics. Columns include
PQ_SERVERS column of the
V$RSRC_SESSION_INFO view contains the number of active parallel servers if the session is active and running the parallel query. If the query is queued, the number of parallel servers that this query is trying to run with is shown.
PQ_STATUS column maintains the reason that a parallel statement is queued
Oracle Database Reference for information about the
V$RSRCMGRMETRIC view displays statistics related to parallel statement queuing.
Statistics related to parallel statement queuing are added to the resource manager metrics that takes statistics for a given one-minute window and retains them for approximately one hour.
Oracle Database Reference for information about the