{"id":1231,"date":"2007-03-10T12:00:00","date_gmt":"2007-03-10T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1231"},"modified":"2007-03-10T12:00:00","modified_gmt":"2007-03-10T12:00:00","slug":"how-can-i-tell-the-actual-dop-used-for-my-parallel-query","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2007\/03\/10\/how-can-i-tell-the-actual-dop-used-for-my-parallel-query\/","title":{"rendered":"How can I tell the actual DOP used for my Parallel Query?"},"content":{"rendered":"

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.<\/p>\n

“How can I tell the actual DOP used for my Parallel Query?<\/i>” (or words to that effect)<\/p>\n

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. <\/p>\n

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.<\/p>\n

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<\/i>?’ question that most are interested in during the day to day running of their applications (or maybe it is – we’ll see later).<\/p>\n

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<\/a>. 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. <\/p>\n

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 :-<\/p>\n

$ sqlplus testuser\/testuserSQL*Plus: Release 10.2.0.2.0 - Production on Fri Mar 9 23:33:49 2007Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> 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   32768000SQL> select * from v$px_session;no rows selected<\/pre>\n
<\/p>\n
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)<\/p>\n
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.)<\/p>\n
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        17925 rows selected.<\/pre>\n
So there are two sets <\/i>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.<\/p>\n
SQL> select count(distinct process)  2  from v$pq_tqstat  3  where process like 'P%';
COUNT(DISTINCTPROCESS)----------------------                    16<\/p><\/pre>\n
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.<\/p>\n
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.<\/p>\n
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                    1Parallel operations downgraded to serial              0Parallel operations downgraded 75 to 99 pct           0Parallel operations downgraded 50 to 75 pct           0Parallel operations downgraded 25 to 50 pct           0Parallel operations downgraded 1 to 25 pct            06 rows selected.<\/pre>\n
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;-)<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading How can I tell the actual DOP used for my Parallel Query?<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1231","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1319,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g\/","url_meta":{"origin":1231,"position":0},"title":"Parallel Query and 11g","date":"September 23, 2007","format":false,"excerpt":"I've been playing around with 11g on and off this week, re-running some of the tests in this document (PDF file).The main edits required to the scripts were simple ones to correct the locations of trace files, for example (where $ORACLE_BASE is \/ora on the 10g system) rm \/ora\/admin\/TEST1020\/udump\/*.trc and\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":835,"url":"http:\/\/orcldoug.com\/blog\/2006\/03\/14\/px-and-the-magic-of-2\/","url_meta":{"origin":1231,"position":1},"title":"PX and “the Magic of 2”","date":"March 14, 2006","format":false,"excerpt":"Now that I've had time to sit back and relax and absorb things a little, I thought I should fix a couple of outright bugs in the recent PX paper (Later update - link to the paper) and revisit the conclusions before moving on to testing different aspects. First, the\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":902,"url":"http:\/\/orcldoug.com\/blog\/2005\/11\/18\/hotsos-symposium-2006\/","url_meta":{"origin":1231,"position":2},"title":"Hotsos Symposium 2006","date":"November 18, 2005","format":false,"excerpt":"My goodness, nearly a week without a blog posting and my review of Jonathan's book still hasn't appeared. It's been a crazy week at work and what little spare time I've had has been spent on getting my server installed. It looks like I won't be able to get wireless\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1363,"url":"http:\/\/orcldoug.com\/blog\/2007\/12\/04\/murali-vallath\/","url_meta":{"origin":1231,"position":3},"title":"Murali Vallath","date":"December 4, 2007","format":false,"excerpt":"When I blogged about Openworld, I decided not to mention one presentation at the time. I attended Murali Vallath's \"Oracle Parallel Query Feature in a RAC environment\" on Sunday afternoon. I almost didn't because I know the subject pretty well, but I thought I might learn something or see a\u2026","rel":"","context":"With 42 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1513,"url":"http:\/\/orcldoug.com\/blog\/2009\/07\/30\/awr-differences-report\/","url_meta":{"origin":1231,"position":4},"title":"AWR Differences Report","date":"July 30, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.This morning I had an opportunity to use one of my favourite AWR tools, the differences report. Our system has a fairly involved overnight batch schedule consisting of multiple concurrent job streams that starts at 02:00 and usually completes at about\u2026","rel":"","context":"With 12 comments","img":{"alt_text":"","src":"\/serendipity\/uploads\/awrdd2.png","width":350,"height":200},"classes":[]},{"id":1559,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/09\/advert-symposium-countdown\/","url_meta":{"origin":1231,"position":5},"title":"Advert: Symposium Countdown","date":"February 9, 2010","format":false,"excerpt":"I submitted my slides last Friday so the Hotsos Symposium countdown has really started now - I'll be presenting in just under 4 weeks time. I'm looking forward to it even more than I was for a couple of reasons.1) I noticed from Kerry Osborne's blog post that he's now\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1231","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1231"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1231\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1231"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}