Home Page

Back to Papers


Introduction

Architecture

Instance Configuration

Instance Configuration II

Dictionary Views

Dictionary Views II

Monitoring Queries

Session Tracing and Wait Events

Conclusion

Other Resources

Data Dictionary Views

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

 Next

Technical Papers Utilities and Scripts Book Reviews Links
My Resume Fun & Games Email Home