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 (Continued)

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

 Next

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