How can I tell the actual DOP used for my Para ...

At the end of my presentation this week, when I asked for questions, a lady from the middle of the hall piped up to ask something along the lines of.

"How can I tell the actual DOP used for my Parallel Query?" (or words to that effect)

It was in the context of me saying that you need to know the number of process' trace files that have been consolidated by trcsess.

A gentleman who I remember from last year's Symposium also came along to my presentation this year and repeated a similar question later. Before we go any further, all of this is Oracle 10.2.0.2 but should apply equally well to other versions as far as I know.

My personal approach when writing papers and running tests has always been to use the V$PQ_TQSTAT dictionary view after my query has run because that contains reasonably detailed information about the Data Flow Operations, rows processed etc. However, that's not particularly useful for the type of 'was my query down-graded?' question that most are interested in during the day to day running of their applications (or maybe it is - we'll see later).

At work, I've tended to use the 'Parallel operations downgraded ...' statistics in V$SYSSTAT in a Statspack report as Jeff Moss does in the first part of this blog. This works well as a system wide check and possibly during a batch run when the number of concurrent jobs is smaller and it's easier to identify a particular problem.

However, if you have multiple users or applications running parallel queries throughout the day (or even in that batch schedule), it could be very useful to have a post-run log of the actual DOP used, if you could instrument your code accordingly. A few people standing in the same group suggested using the DEGREE and REQ_DEGREE columns of V$PX_SESSION. Jeff has an interesting twist on this approach in the second half of the afore-mentioned blog as he captured the contents of this view in a table. However, even though this could indicate specific sessions (via the QCSID column that you could use to tie the rows to a specific Query Coordinator), I don't think it meets the original requirement for a simple reason. You must catch the information while the query is running or it will disappear. To illustrate :-

$ sqlplus testuser/testuser

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 9 23:33:49 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select /*+ parallel(test_tab1) */
  2     mod(pk_id, 2), count(*)
  3  from test_tab1
  4  group by mod(pk_id, 2);

MOD(PK_ID,2)   COUNT(*)
------------ ----------
           1   32768000
           0   32768000

SQL> select * from v$px_session;

no rows selected

The important thing to note here is that I ran the second statement immediately after the first. (If I run the same check in a seperate session whilst the main PX query is running, I can see the information I'm looking for but, once the main PX query completes, the relevant information disappears)

Now, lest you think that maybe the query didn't run in parallel for some reason, I'll immediately run another query - against V$PQ_TQSTAT. (One that Jonathan Lewis 'loaned me' a while back - I'll give it back soon.)

SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
  2  FROM v$pq_tqstat
  3  ORDER BY dfo_number DESC, tq_id, server_type DESC , process
  4  /

DFO_NUMBER      TQ_ID SERVER_TYPE                    PROCESS    NUM_ROWS      BYTES
---------- ---------- ------------------------------ -------- ---------- ----------
         1          0 Producer                       P008              2        191
         1          0 Producer                       P009              2        191
         1          0 Producer                       P010              2        191
         1          0 Producer                       P011              2        191
         1          0 Producer                       P012              2        191
         1          0 Producer                       P013              2        191
         1          0 Producer                       P014              2        191
         1          0 Producer                       P015              2        191
         1          0 Consumer                       P000              0        160
         1          0 Consumer                       P001              0        160
         1          0 Consumer                       P002              8        288
         1          0 Consumer                       P003              0        160
         1          0 Consumer                       P004              0        160
         1          0 Consumer                       P005              8        280
         1          0 Consumer                       P006              0        160
         1          0 Consumer                       P007              0        160
         1          1 Producer                       P000              0         20
         1          1 Producer                       P001              0         20
         1          1 Producer                       P002              1         30
         1          1 Producer                       P003              0         20
         1          1 Producer                       P004              0         20
         1          1 Producer                       P005              1         29
         1          1 Producer                       P006              0         20           
         1          1 Producer                       P007              0         20
         1          1 Consumer                       QC                2        179

25 rows selected.

So there are two sets of slaves being used for this query, one set of which are acting as both consumers and producers, and I can see that a DOP of 8 is being used. You could capture this information in a different way for a possible application log entry.

SQL> select count(distinct process)
  2  from v$pq_tqstat
  3  where process like 'P%';

COUNT(DISTINCTPROCESS)
----------------------
                    16

Rather than the actual DOP, this shows the number of slave processes used - DOP * 2 - (8 * 2 in this case), but you can manipulate that easily if you want. However, I might argue that the DOP isn't so significant as the number of slaves used, particularly bearing in mind that the reduced DOP could be caused by a lack of available slaves. Whatever you choose, this is clearly a possibility although I have seen the odd case (and Jonathan L. has told me in the past that he's seen more) where the contents of V$PQ_TQSTAT can be unreliable.

Here's another possible approach using the 'Parallel operations ...' statistics, but for this session. It lacks the detail of DOP used, but I think what many people will be interested in is 'Was my DOP reduced' and this would help with that.

SQL> l
  1  select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4* and n.name like 'Parallel%'
SQL> /

NAME                                              VALUE
-------------------------------------------- ----------
Parallel operations not downgraded                    1
Parallel operations downgraded to serial              0
Parallel operations downgraded 75 to 99 pct           0
Parallel operations downgraded 50 to 75 pct           0
Parallel operations downgraded 25 to 50 pct           0
Parallel operations downgraded 1 to 25 pct            0

6 rows selected.

In due course, when he's worked his way through his probably much bigger pile of questions from the two seminars he's presented at this week, I expect Jonathan Lewis will pop his head around the metaphorical door and offer a definitive answer. I haven't asked him about that, I just have a feeling he might;-)

The gentleman who was asking me the question yesterday told me he'd be staying until Sunday so hopefully I'll bump into him (and remember his name!!!) or he'll read this blog and I certainly hope the original questioner (can't help you with a name there either!) will see this.