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.
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);
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
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%';
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.
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%'
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.
Tracked: Jan 05, 08:51
Tracked: Jan 05, 08:54
Hard to resist a bit of name-dropping. See:
"_px_trace" might be the most convenient way.
Metalink doc# 444164.1
Thanks you - that looks very interesting. I'm not sure it meets the original requirement in the question though, because they wanted to see the information after the event had occurred.
However, definitely interesting, so thanks.