It’s easy to make mistakes, or overlook defects, when constructing parallel queries – especially if you’re a developer who hasn’t been given the right tools to make it easy to test your code. Here’s a little trap I came across recently that’s probably documented somewhere, which could be spotted easily if you had access to the OEM SQL Monitoring screen, but would be very easy to miss if you didn’t check the execution plan very carefully. I’ll start with a little script to generate some data:
create table t1 nologging as select * from all_objects where rownum <= 50000 ; insert /*+ append */ into t1 select * from t1; commit; insert /*+ append */ into t1 select * from t1; commit; insert /*+ append */ into t1 select * from t1; commit; insert /*+ append */ into t1 select * from t1; commit; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); end; / create table t2 as select * from t1; alter table t2 add id number(10,0);
All I’ve done is create some data – 800,000 rows – and then create a table to copy it to; and while I copy it I’m going to add a temporary id to the rows, which I’ll do with a call to rownum; and since there’s a lot of data I’ll use parallel execution:
alter session enable parallel dml; insert /*+ parallel(t2 3) */ into t2 select /*+ parallel(t1 4) */ t1.* , rownum from t1;
For the purposes of experiment and entertainment I’ve done something a little odd by supplying two possible degrees of parallelism, but this lets me ask the query: will this statement run parallel 3, parallel 4, both of the above, or neither ? (You may assume that I have parallel execution slaves available when the statement runs.)
The answer is both – because that rownum does something nasty to the execution plan (I didn’t include the 50,000 limit in my first test, which is why the plan reports 993K rows instead of 800,000):
-------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 993K| 92M| 1076 (1)| 00:00:13 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ20001 | 993K| 92M| 1076 (1)| 00:00:13 | Q2,01 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | T2 | | | | | Q2,01 | PCWP | | | 4 | PX RECEIVE | | 993K| 92M| 1076 (1)| 00:00:13 | Q2,01 | PCWP | | | 5 | PX SEND ROUND-ROBIN | :TQ20000 | 993K| 92M| 1076 (1)| 00:00:13 | | S->P | RND-ROBIN | | 6 | COUNT | | | | | | | | | | 7 | PX COORDINATOR | | | | | | | | | | 8 | PX SEND QC (RANDOM)| :TQ10000 | 993K| 92M| 1076 (1)| 00:00:13 | Q1,00 | P->S | QC (RAND) | | 9 | PX BLOCK ITERATOR | | 993K| 92M| 1076 (1)| 00:00:13 | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| T1 | 993K| 92M| 1076 (1)| 00:00:13 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------
See that “P->S” (parallel to serial) at operation 8. The select statement runs in parallel (degree 4) to scan the data, and then sends it all to the query co-ordinator to supply the rownum; then the query co-ordinator re-distributes the data (including rownum) to another set of slave (S->P) to do the parallel (degree 3) insert. The P->S at line 2 shows the parallel execution slaves passing details to the query co-ordinator of the private segments that they have created so that the query co-ordinator can stitch the segments together into a single data segment for the table. (If you watch closely you’ll see the query co-ordinator doing a few local writes as it tidies up the header blocks in those segment blocks.)
There are two threats to this rownum detail. The first, of course, is that the operation essentially serialises through the query co-ordinator so it’s going to take longer than you might expect; secondly an accident of this type is typically going to allocate twice as many parallel execution slaves as you might have expected – the select and the insert are two separate data flow operations (note how the Name column shows TQ1xxxx and TQ2xxxx), each gets its own slave sets, and both sets of slaves are held for the duration of the statement. If this statement is demanding twice the slaves it should be using, then you may find that other statements that start running at the same time get their degree of parallelism downgraded because you’ve run out of PX slaves. Although the rownum solution is nice and clean – it require no further infrastructure – you probably need to introduce a sequence (with a large cache) to get the same effect without losing parallelism.
If you look at v$pq_tqstat after running this statement the results are a little disappointing – there are a few problems connecting lines from the plan with rows in the view – here’s my original output (and you’ll now see why I chose to have two different degrees of parallelism):
DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS BYTES WAITS TIMEOUTS AVG_LATENCY
---------- ---------- --------------- -------- --------------- ---------- ---------- ---------- ---------- -----------
1 0 Consumer 1 P000 331330 39834186 74 71 0
1 P001 331331 39844094 75 72 0
1 P002 330653 39749806 74 71 0
1 Producer 1 P000 1 131 2263 396 0
1 P001 1 131 2238 417 0
1 P002 1 131 2182 463 0
2 0 Producer 1 P003 247652 28380762 13 0 0
1 P004 228857 26200574 13 1 0
1 P005 267348 30496182 14 0 0
1 P006 249457 28401982 13 0 0
1 QC 993314 119428086 4294967269 4294967286 0
Consumer 1 QC 993314 113479500 125 65 0
1 Consumer 1 QC 3 393 2 1 0
The first problem is that the DFO_number reported in the view doesn’t match with the :TQ1xxxx and :TQ2xxxx reported in the plan – the parallel 4 bit is the select, which is covered by :TQ1000, but it’s listed under DFO_Number = 2 in the view, and the insert is the parallel 3 bit, which is covered by :TQ2000 and :TQ20001 but listed under DFO_Number = 1.
More confusingly, potentially, is that the all appearances of the query coordinator have been assigned to DFO_Number = 2. Ignoring the fact that the DFO_Number column switches the 1 and 2 from the plan, what we should see is as follows:
- The consumer at line 16 is consuming from the 4 producers at lines 11 – 14.
- The producer at line 15 is producing FOR the 3 consumers at lines 3 – 5
- The consumer at line 18 is consuming from the producers at lines 7 – 9
Ideally (including the correction for the DFO_Number) I think the view content should be as follows:
DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS BYTES WAITS TIMEOUTS AVG_LATENCY
---------- ---------- --------------- -------- --------------- ---------- ---------- ---------- ---------- -----------
1 0 Producer 1 P003 247652 28380762 13 0 0
1 P004 228857 26200574 13 1 0
1 P005 267348 30496182 14 0 0
1 P006 249457 28401982 13 0 0
Consumer 1 QC 993314 113479500 125 65 0
2 0 Producer 1 QC 993314 119428086 4294967269 4294967286 0
Consumer 1 P000 331330 39834186 74 71 0
1 P001 331331 39844094 75 72 0
1 P002 330653 39749806 74 71 0
1 Producer 1 P000 1 131 2263 396 0
1 P001 1 131 2238 417 0
1 P002 1 131 2182 463 0
Consumer 1 QC 3 393 2 1 0
Just don’t ask me why the waits and timeouts for the QC as producer seem to be counting backwards from 2^32.
