Oracle Parallel Processing

Dynamic Performance Views

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.

Tip

Only the most important information returned by each view is described ...