Introduction

Architecture

Instance Configuration

Instance Configuration II

Dictionary Views

Dictionary Views II

Monitoring Queries

Session Tracing and Wait Events

Conclusion

Other Resources

Please note this page has moved to http://oracledoug.com/px5.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

The easiest approach to high-level real-time performance monitoring is to use data dictionary views. There is some information in the standard views, such as V$SYSSTAT, which I'll come to later. First, though, let's take a look at the PX specific views. These begin with either V$PQ or V$PX, reflecting the change in Oracle's terminology over time. Typically, the V$PX_ views are the more recent and Oracle change the views that are available reasonably frequently so it's always worth using the query below to find out what views are available on the version that you're using.

SELECT table_name 
FROM dict 
WHERE table_name LIKE 'V%PQ%' 
OR table_name like 'V%PX%‘;
TABLE_NAME
------------------------------
V$PQ_SESSTAT
V$PQ_SYSSTAT
V$PQ_SLAVE
V$PQ_TQSTAT
V$PX_BUFFER_ADVICE
V$PX_SESSION
V$PX_SESSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT

V$PQ_SESSTAT

V$PQ_SESSTAT shows you PX statistics for your current session.

SELECT * FROM v$pq_sesstat;
STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             2
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             2
Server Threads                          7             0
Allocation Height                       7             0
Allocation Width                        1             0
Local Msgs Sent                       491           983
Distr Msgs Sent                         0             0
Local Msgs Recv'd                     491           983
Distr Msgs Recv'd                       0             0

It’s a nice easy way to confirm that your queries are being parallelised and also gives you a taste of the amount of messaging activity that’s required even for a fairly straightforward task.

V$PQ_SYSSTAT

This view is useful for getting an instance-wide overview of how PX slaves are being used and is particularly helpful in determining possible changes to parallel_max_servers and parallel_min_servers. For example if ‘Servers Started’ and ‘Servers Shutdown’ were constantly changing, maybe it would be worth increasing parallel_min_servers to reduce this activity. (V$PX_PROCESS_SYSSTAT contains similar information.)

SELECT * FROM v$pq_sysstat WHERE statistic LIKE ‘Servers%’;
STATISTIC                           VALUE
------------------------------ ----------
Servers Busy                            0
Servers Idle                            0
Servers Highwater                       3
Server Sessions                         3
Servers Started                         3
Servers Shutdown                        3
Servers Cleaned Up                      0

V$PQ_SLAVE and V$PX_PROCESS

These two views allow us to track whether individual slaves are in use or not and track down their associated session details.

SELECT * FROM v$px_process;
SERV STATUS           PID SPID                SID    SERIAL#
---- --------- ---------- ------------ ---------- ----------
P001 IN USE            18 7680                144         17
P004 IN USE            20 7972                146         11
P005 IN USE            21 8040                148         25
P000 IN USE            16 7628                150         16
P006 IN USE            24 8100                151         66
P003 IN USE            19 7896                152         30
P007 AVAILABLE         25 5804                
P002 AVAILABLE         12 6772

Previous