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/px6.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

V$PQ_TQSTAT

V$PQ_TQSTAT shows you table queue statistics for the current session and you must have used parallel execution in the current session for this view to be accessible. I like the way that it shows the relationships between slaves and the query coordinator very effectively. For example, after running this query against the 25,481 row attendance table: -

SELECT /*+ PARALLEL (attendance, 4) */ *
FROM attendance;

The contents of V$PQ_SYSSTAT look like this

break on dfo_number on tq_id 
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC , process;
DFO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS      BYTES
---------- ---------- ---------- ---------- ---------- ----------
         1          0 Producer   P000             6605     114616
                      Producer   P001             6102     105653
                      Producer   P002             6251     110311
                      Producer   P003             6523     113032
                      Consumer   QC              25481     443612

We can see here that four slave processes have been used acting as row Producers, each processing approximately 25% of the rows, which are all consumed by the QC to return the results to the user. Whereas for the following query

SELECT /*+ PARALLEL (attendance, 4) */ *
FROM attendance
ORDER BY amount_paid;

We’ll see something more like this.

break on dfo_number on tq_id
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC, process;
DFO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS      BYTES
---------- ---------- ---------- ---------- ---------- ----------
         1          0 Ranger     QC                372      13322
         	      Producer   P004             5744     100069
         	      Producer   P005             6304     110167
                      Producer   P006             6303     109696
                      Producer   P007             7130     124060
                      Consumer   P000            15351     261380
                      Consumer   P001            10129     182281
                      Consumer   P002                0        103
                      Consumer   P003                1        120
                    1 Producer   P000            15351     261317
                      Producer   P001            10129     182238
                      Producer   P002                0         20
                      Producer   P003                1         37
                      Consumer   QC              25481     443612

There are a few new things going on here

• The QC acts as a Ranger, which works out the range of values that each PX slave should be responsible for sorting. By implication, it also lets the Producer slaves that will be reading the table know which sorting Consumer slave is the correct ‘destination’ for a row.
• P004, P005, P006 and P007 are scanning 25% of the blocks each.
• P0001, P002, P003 and P004 act as Consumers of the rows being produced by P004-P007 and perform the sorting activity
• They also act as Producers of the final sorted results for the QC to consume (now that it’s finished it’s other activities.

What is a little worrying from a performance point of view is that P000 and P001 seem to be doing a lot more work than P002 and P003 which means that they will run for much longer and we’re not getting the full benefit of a degree 4 parallel sort. It’s a good idea to look at the range of values contained in the sort column.

SELECT amount_paid, COUNT(*)
FROM attendance
GROUP BY amount_paid
ORDER BY amount_paid
/
AMOUNT_PAID   COUNT(*)
----------- ----------
        200          1
        850          1
        900          1
       1000          7
       1150          1
       1200      15340
       1995      10129
       4000          1

This indicates where the problem lies. We have extremely skewed data because the vast majority of rows have one of only two values, so it’s very difficult to parallelise a sort on this column!

V$PX_SESSTAT

This view is a bit like V$SESSTAT but also includes information about which QC and which Slave Set each session belongs to, which allows us to see a given statistic (e.g. Physical Reads) for all steps of an operation.

SELECT stat.qcsid, stat.server_set, stat.server#, nam.name, stat.value
FROM v$px_sesstat stat, v$statname nam
WHERE stat.statistic# = nam.statistic#
AND nam.name LIKE ‘physical reads%’
ORDER BY 1,2,3
     QCSID SERVER_SET    SERVER# NAME                      VALUE
---------- ---------- ---------- -------------------- ----------
       145          1          1 physical reads                0
       145          1          2 physical reads                0
       145          1          3 physical reads                0
       145          2          1 physical reads               63
       145          2          2 physical reads               56
       145          2          3 physical reads               61

Previous