In zwei Wochen findet das Expertenseminar "Parallel Execution Masterclass" in Berlin statt, das ich gemeinsam mit der DOAG veranstalte.
Es sind noch ein paar Plätze frei - sollten Sie also Lust und Zeit haben, nach Berlin zu kommen und exklusives Wissen (nicht nur) über das Parallel Execution Feature der Oracle Datenbank zu erfahren, würde ich mich sehr freuen, Sie dort mit den anderen Teilnehmern begrüßen zu dürfen, um gemeinsam mit Ihnen eine gute und produktive Zeit zu verbringen!
Bei Interesse wenden Sie sich bitte an die Ansprechpartner der DOAG, die im Link angegeben sind - dort finden Sie auch eine genauere Beschreibung des Seminars.
Monday, March 30, 2015
Sunday, March 29, 2015
Video Tutorial: XPLAN_ASH Active Session History - Part 4
The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.
More parts to follow.
More parts to follow.
Sunday, March 22, 2015
12c Parallel Execution New Features: Concurrent UNION ALL - Part 3
In the final part of this instalment I want to focus on the possible optimisation of remote access that I outlined in the initial part, which is based on the idea of running multiple concurrent remote branches of a UNION ALL to overcome the query coordinator bottleneck of straightforward remote queries that need to transfer larger amounts of data.
For that purpose I now simply change my sample query to access the serial table T2 via the DB link defined in the setup of the initial part, like that:
set echo on timing on time on
select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
);
which gives me this execution plan:
-------------------------------------------------------------- | Id | Operation | Name | Cost (%CPU)| Inst |IN-OUT| -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 0 (0)| | | | 1 | REMOTE | | | LOOP | R->S | --------------------------------------------------------------Ouch, not exactly what I wanted. Of course it's nice that the optimizer recognizes that this is a statement that can be executed fully remotely, but for my particular purpose I don't want that to happen. So let's add a dummy local branch:
select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where 1 = 0
);
which gives now this plan:
--------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | SORT AGGREGATE | | | | | | 2 | PX COORDINATOR | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | Q1,00 | PCWP | | | 5 | VIEW | | Q1,00 | PCWP | | | 6 | UNION-ALL | | Q1,00 | PCWP | | | 7 | PX SELECTOR | | Q1,00 | PCWP | | | 8 | REMOTE | T2 | Q1,00 | PCWP | | | 9 | PX SELECTOR | | Q1,00 | PCWP | | | 10 | REMOTE | T2 | Q1,00 | PCWP | | | 11 | PX SELECTOR | | Q1,00 | PCWP | | | 12 | REMOTE | T2 | Q1,00 | PCWP | | | 13 | PX SELECTOR | | Q1,00 | PCWP | | | 14 | REMOTE | T2 | Q1,00 | PCWP | | | 15 | PX SELECTOR | | Q1,00 | PCWP | | | 16 | REMOTE | T2 | Q1,00 | PCWP | | | 17 | PX SELECTOR | | Q1,00 | PCWP | | | 18 | REMOTE | T2 | Q1,00 | PCWP | | | 19 | PX SELECTOR | | Q1,00 | PCWP | | | 20 | REMOTE | T2 | Q1,00 | PCWP | | | 21 | PX SELECTOR | | Q1,00 | PCWP | | | 22 | REMOTE | T2 | Q1,00 | PCWP | | | 23 | PX SELECTOR | | Q1,00 | PCWP | | | 24 | REMOTE | T2 | Q1,00 | PCWP | | | 25 | PX SELECTOR | | Q1,00 | PCWP | | | 26 | REMOTE | T2 | Q1,00 | PCWP | | | 27 | PX SELECTOR | | Q1,00 | PCWP | | |* 28 | FILTER | | Q1,00 | PCWP | | | 29 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | ---------------------------------------------------------------------------That is more what I wanted. One funny side effect of the dummy branch is that the automatically derived degree is now 11, since there are 11 branches. Although the optimizer knows that the 11th branch won't be executed (the filter operator ID = 28 is "NULL IS NOT NULL") it gets its own PX SELECTOR assigned, so that's probably the explanation why the calculation arrives at 11. So let's see what happens at runtime using the setup from the previous part where T2 has 2M rows:
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
1| 4485K| | 1| 4| 5|@**** (5) |
2| 45M| | 0| 10| 10|********** (10) |
3| 46M| | 1| 9| 10|@********* (10) |
4| 46M| | 0| 10| 10|********** (10) |
5| 46M| | 0| 10| 10|********** (10) |
6| 46M| | 0| 10| 10|********** (10) |
7| 46M| | 0| 10| 10|********** (10) |
8| 46M| | 0| 10| 10|********** (10) |
9| 46M| | 0| 10| 10|********** (10) |
10| 46M| | 0| 10| 10|********** (10) |
11| 46M| | 0| 10| 10|********** (10) |
12| 46M| | 0| 10| 10|********** (10) |
13| 46M| | 0| 10| 10|********** (10) |
14| 46M| | 0| 10| 10|********** (10) |
15| 46M| | 0| 10| 10|********** (10) |
16| 46M| | 0| 10| 10|********** (10) |
17| 46M| | 0| 10| 10|********** (10) |
18| 46M| | 0| 10| 10|********** (10) |
19| 46M| | 0| 10| 10|********** (10) |
20| 46M| | 0| 10| 10|********** (10) |
21| 46M| | 0| 10| 10|********** (10) |
22| 46M| | 0| 10| 10|********** (10) |
23| 46M| | 0| 10| 10|********** (10) |
24| 46M| | 0| 10| 10|********** (10) |
25| 46M| | 1| 9| 10|@********* (10) |
26| 46M| | 1| 9| 10|@********* (10) |
27| 46M| | 0| 10| 10|********** (10) |
28| 46M| | 0| 10| 10|********** (10) |
29| 46M| | 0| 10| 10|********** (10) |
30| 46M| | 0| 10| 10|********** (10) |
31| 46M| | 0| 10| 10|********** (10) |
32| 46M| | 0| 10| 10|********** (10) |
33| 46M| | 1| 9| 10|@********* (10) |
34| 46M| | 0| 10| 10|********** (10) |
35| 46M| | 1| 9| 10|@********* (10) |
36| 46M| | 0| 10| 10|********** (10) |
37| 46M| | 0| 10| 10|********** (10) |
38| | | 0| 0| 0| (0) |
39| 46M| | 2| 8| 10|@@******** (10) |
40| 46M| | 0| 10| 10|********** (10) |
41| 46M| | 0| 10| 10|********** (10) |
42| 46M| | 0| 10| 10|********** (10) |
43| 46M| | 1| 9| 10|@********* (10) |
44| 46M| | 0| 10| 10|********** (10) |
45| 46M| | 0| 10| 10|********** (10) |
46| 46M| | 0| 10| 10|********** (10) |
47| 46M| | 0| 10| 10|********** (10) |
48| 46M| | 0| 10| 10|********** (10) |
49| 46M| | 0| 10| 10|********** (10) |
50| 46M| | 0| 10| 10|********** (10) |
51| 46M| | 0| 10| 10|********** (10) |
52| 46M| | 0| 10| 10|********** (10) |
53| 46M| | 1| 9| 10|@********* (10) |
54| 46M| | 0| 10| 10|********** (10) |
55| 46M| | 0| 10| 10|********** (10) |
56| 46M| | 0| 10| 10|********** (10) |
57| 46M| | 0| 10| 10|********** (10) |
58| 46M| | 0| 10| 10|********** (10) |
59| 36M| | 0| 8| 8|******** (8) |
60| 4609K| | 0| 1| 1|* (1) |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | | | | | 0:sqlplus.exe(0)[1],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | 0:sqlplus.exe(0)[1],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 2 | PX COORDINATOR | | 12 | 11 | | | | | 0:sqlplus.exe(0)[11],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 11 | 11 | | | | | 0:P00A(0)[1],P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],... |
| 4 | SORT AGGREGATE | | 11 | 11 | | | | | 0:P00A(0)[1],P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],... |
| 5 | VIEW | | 11 | 20M | 43 | 1 | 1 | # | 1:P002(1)[2000K],P00A(0)[2000K],P001(0)[2000K],P003(0)[2000K],P004(0)[2000K],... |
| 6 | UNION-ALL | | 11 | 20M | | | | | 0:P00A(0)[2000K],P001(0)[2000K],P002(0)[2000K],P003(0)[2000K],P004(0)[2000K],... |
| 7 | PX SELECTOR | | 11 | 2000K | | | | | 0:P006(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 8 | REMOTE | T2 | 11 | 2000K | 1 | 59 | 58 | #################### | 3:P006(58)[2000K],P004(1)[0],P007(1)[0],P00A(0)[0],P000(0)[0],... |
| 9 | PX SELECTOR | | 11 | 2000K | | | | | 0:P008(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 10 | REMOTE | T2 | 11 | 2000K | 1 | 58 | 57 | #################### | 1:P008(57)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 11 | PX SELECTOR | | 11 | 2000K | | | | | 0:P00A(0)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 12 | REMOTE | T2 | 11 | 2000K | 1 | 59 | 58 | #################### | 1:P00A(58)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 13 | PX SELECTOR | | 11 | 2000K | | | | | 0:P004(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 14 | REMOTE | T2 | 11 | 2000K | 2 | 58 | 57 | #################### | 1:P004(57)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 15 | PX SELECTOR | | 11 | 2000K | | | | | 0:P007(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 16 | REMOTE | T2 | 11 | 2000K | 2 | 59 | 58 | #################### | 1:P007(58)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 17 | PX SELECTOR | | 11 | 2000K | | | | | 0:P005(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 18 | REMOTE | T2 | 11 | 2000K | 2 | 58 | 57 | #################### | 1:P005(57)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 19 | PX SELECTOR | | 11 | 2000K | | | | | 0:P002(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P003(0)[0],... |
| 20 | REMOTE | T2 | 11 | 2000K | 2 | 58 | 56 | #################### | 1:P002(56)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P003(0)[0],... |
| 21 | PX SELECTOR | | 11 | 2000K | | | | | 0:P009(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 22 | REMOTE | T2 | 11 | 2000K | 2 | 58 | 57 | #################### | 1:P009(57)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 23 | PX SELECTOR | | 11 | 2000K | | | | | 0:P003(0)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 24 | REMOTE | T2 | 11 | 2000K | 2 | 57 | 56 | #################### | 1:P003(56)[2000K],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 25 | PX SELECTOR | | 11 | 2000K | | | | | 0:P001(0)[2000K],P00A(0)[0],P000(0)[0],P002(0)[0],P003(0)[0],... |
| 26 | REMOTE | T2 | 11 | 2000K | 2 | 58 | 57 | #################### | 1:P001(57)[2000K],P00A(0)[0],P000(0)[0],P002(0)[0],P003(0)[0],... |
| 27 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
|* 28 | FILTER | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 29 | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
So in principle this works as desired, with the automatically derived degree all remote branches are active at the same time. If I queried now different chunks of the same remote object to speed up the transfer and maximize throughput this should give me what I want.
If you wonder why the execution took now almost one minute when the processing of the same query in the previous part just took 40-45 seconds: This seems to be a side-effect of running that many concurrent CPU intensive processes on my test system, which nominally has 12 cores / 24 CPUs, but obviously doesn't scale linearly, either due to some architectural issues, or simply because the CPU speed was lowered with that many cores active.
However, look what happens when I change the remote query slightly so that no rows will be returned:
select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') > regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where 1 = 0
);
The runtime profile now looks like this:
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
6| 36M| | 0| 9,2| 9,2|********* (9,2) |
12| 36M| | 0| 11| 11|*********** (11) |
18| 36M| | 0| 11| 11|*********** (11) |
24| 36M| | 0| 11| 11|*********** (11) |
30| 36M| | 0| 11| 11|*********** (11) |
36| 36M| | 0| 11| 11|*********** (11) |
42| 36M| | 0| 9,2| 9,2|********* (9,2) |
48| 36M| | 0| 11| 11|*********** (11) |
54| 36M| | 0| 11| 11|*********** (11) |
60| 32M| | 0| 11| 11|*********** (11) |
66| 9641K| | 0| 11| 11|*********** (11) |
72| 9641K| | 0| 11| 11|*********** (11) |
78| 9641K| | 0| 11| 11|*********** (11) |
84| 9641K| | 0| 11| 11|*********** (11) |
90| 9641K| | 0| 11| 11|*********** (11) |
96| 9641K| | 0| 11| 11|*********** (11) |
102| 9641K| | 0| 9,2| 9,2|********* (9,2) |
108| 9641K| | 0| 11| 11|*********** (11) |
114| 9801K| | 0| 11| 11|*********** (11) |
120|10281K| | 0| 11| 11|*********** (11) |
126|10281K| | 0| 11| 11|*********** (11) |
132|10281K| | 0| 11| 11|*********** (11) |
138|10281K| | 0| 11| 11|*********** (11) |
144|10281K| | 0| 11| 11|*********** (11) |
150|10281K| | 0| 11| 11|*********** (11) |
156|10281K| | 0| 11| 11|*********** (11) |
162|10281K| | 0| 9,2| 9,2|********* (9,2) |
168|10281K| | 0| 11| 11|*********** (11) |
174|10281K| | 0| 11| 11|*********** (11) |
180|10281K| | 0| 11| 11|*********** (11) |
186|10281K| | 0| 11| 11|*********** (11) |
192|10281K| | 0| 11| 11|*********** (11) |
198|10281K| | 0| 11| 11|*********** (11) |
204|10281K| | 0| 11| 11|*********** (11) |
210|10281K| | 0| 11| 11|*********** (11) |
216|10281K| | 0| 11| 11|*********** (11) |
222|10281K| | 0| 9,2| 9,2|********* (9,2) |
228|10281K| | 0| 11| 11|*********** (11) |
234|10281K| | 0| 11| 11|*********** (11) |
240|10281K| | 0| 11| 11|*********** (11) |
246|10281K| | 0| 11| 11|*********** (11) |
252|10281K| | 0| 11| 11|*********** (11) |
258|10281K| | 0| 11| 11|*********** (11) |
264|10281K| | 0| 11| 11|*********** (11) |
270|10281K| | 0| 11| 11|*********** (11) |
276|10281K| | 0| 11| 11|*********** (11) |
282|10281K| | 0| 9,2| 9,2|********* (9,2) |
287|10281K| | 0| 11| 11|*********** (11) |
292|10281K| | 0| 11| 11|*********** (11) |
297|10281K| | 0| 11| 11|*********** (11) |
302|10281K| | 0| 11| 11|*********** (11) |
307|10281K| | 0| 11| 11|*********** (11) |
312|10281K| | 0| 11| 11|*********** (11) |
317|10281K| | 0| 11| 11|*********** (11) |
322|10281K| | 0| 11| 11|*********** (11) |
327|10281K| | 0| 11| 11|*********** (11) |
332|10281K| | 0| 11| 11|*********** (11) |
337|10281K| | 0| 11| 11|*********** (11) |
342|10281K| | 0| 8,8| 8,8|********* (8,8) |
347|10281K| | 0| 11| 11|*********** (11) |
352|10281K| | 0| 11| 11|*********** (11) |
357|10281K| | 0| 11| 11|*********** (11) |
362|10281K| | 0| 11| 11|*********** (11) |
367|10281K| | 0| 11| 11|*********** (11) |
372|10281K| | 0| 11| 11|*********** (11) |
377|10281K| | 0| 11| 11|*********** (11) |
382|10281K| | 0| 11| 11|*********** (11) |
387|10281K| | 0| 11| 11|*********** (11) |
392|10281K| | 0| 11| 11|*********** (11) |
397|10281K| | 0| 11| 11|*********** (11) |
402|10281K| | 0| 8,8| 8,8|********* (8,8) |
407|10281K| | 0| 11| 11|*********** (11) |
412|10281K| | 0| 11| 11|*********** (11) |
417|10281K| | 0| 11| 11|*********** (11) |
422|10281K| | 0| 11| 11|*********** (11) |
427|10281K| | 0| 11| 11|*********** (11) |
432|10281K| | 0| 11| 11|*********** (11) |
437|10281K| | 0| 11| 11|*********** (11) |
442|10281K| | 0| 11| 11|*********** (11) |
447|10281K| | 0| 11| 11|*********** (11) |
452|10281K| | 0| 11| 11|*********** (11) |
457|10281K| | 0| 11| 11|*********** (11) |
462|10281K| | 0| 8,8| 8,8|********* (8,8) |
467|10281K| | 0| 11| 11|*********** (11) |
472|10281K| | 0| 11| 11|*********** (11) |
477|10281K| | 0| 11| 11|*********** (11) |
482|10281K| | 0| 11| 11|*********** (11) |
487|10281K| | 0| 11| 11|*********** (11) |
492|10281K| | 0| 11| 11|*********** (11) |
497|10281K| | 0| 11| 11|*********** (11) |
502|10281K| | 0| 11| 11|*********** (11) |
507|10281K| | 0| 11| 11|*********** (11) |
512|10281K| | 0| 11| 11|*********** (11) |
517|10281K| | 0| 11| 11|*********** (11) |
522|10281K| | 0| 8,8| 8,8|********* (8,8) |
527|10281K| | 0| 11| 11|*********** (11) |
532|10281K| | 0| 11| 11|*********** (11) |
537| 9535K| | 0| 10| 10|********** (10) |
542| 7902K| | 0| 8,4| 8,4|******** (8,4) |
547| 4894K| | 0| 5,2| 5,2|***** (5,2) |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | | | | | 0:sqlplus.exe(0)[1],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | 0:sqlplus.exe(0)[1],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 2 | PX COORDINATOR | | 12 | 11 | | | | | 0:sqlplus.exe(0)[11],P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],... |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 11 | 11 | | | | | 0:P00A(0)[1],P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],... |
| 4 | SORT AGGREGATE | | 11 | 11 | | | | | 0:P00A(0)[1],P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],... |
| 5 | VIEW | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 6 | UNION-ALL | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 7 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 8 | REMOTE | T2 | 11 | 0 | 2 | 59 | 58 | ### | 11:P001(58)[0],P002(58)[0],P004(58)[0],P005(58)[0],P00A(57)[0],... |
| 9 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 10 | REMOTE | T2 | 11 | 0 | 58 | 57 | 56 | ### | 11:P00A(53)[0],P001(53)[0],P002(53)[0],P006(53)[0],P007(53)[0],... |
| 11 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 12 | REMOTE | T2 | 11 | 0 | 111 | 58 | 57 | ### | 11:P008(54)[0],P000(53)[0],P001(53)[0],P002(53)[0],P004(53)[0],... |
| 13 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 14 | REMOTE | T2 | 11 | 0 | 163 | 61 | 60 | ### | 11:P00A(54)[0],P001(54)[0],P004(54)[0],P000(53)[0],P002(53)[0],... |
| 15 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 16 | REMOTE | T2 | 11 | 0 | 216 | 61 | 60 | ### | 11:P00A(55)[0],P000(54)[0],P005(54)[0],P006(54)[0],P001(53)[0],... |
| 17 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 18 | REMOTE | T2 | 11 | 0 | 269 | 65 | 64 | #### | 11:P005(58)[0],P007(57)[0],P00A(56)[0],P000(56)[0],P004(56)[0],... |
| 19 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 20 | REMOTE | T2 | 11 | 0 | 324 | 64 | 63 | #### | 11:P006(55)[0],P00A(53)[0],P000(53)[0],P004(53)[0],P008(53)[0],... |
| 21 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 22 | REMOTE | T2 | 11 | 0 | 376 | 66 | 65 | #### | 11:P007(54)[0],P00A(53)[0],P005(53)[0],P001(52)[0],P003(52)[0],... |
| 23 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 24 | REMOTE | T2 | 11 | 0 | 429 | 67 | 66 | #### | 11:P004(54)[0],P008(54)[0],P00A(53)[0],P000(53)[0],P001(53)[0],... |
| 25 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 26 | REMOTE | T2 | 11 | 0 | 481 | 67 | 66 | ### | 11:P008(54)[0],P000(53)[0],P001(53)[0],P003(53)[0],P009(53)[0],... |
| 27 | PX SELECTOR | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 28 | FILTER | | 11 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
| 29 | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P00A(0)[0],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],... |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ouch, so this took now more than nine times longer, and although we see the concurrent UNION ALL plan shape there is not much concurrency visible from the runtime activity - each branch seems to be executed by all 11 PX servers concurrently, and only then the next branch gets executed again by all 11 PX servers... It more or less looks like the PX SELECTOR wouldn't work correctly and instead of assigning the branch only to one PX server it seems to get executed by all of them - which we've already seen in the previous parts of this series is what actually happens, but for non-remote branches the PX SELECTOR functionality made sure that only one of them actually did something whereas the other ones simply skipped the operation.
We can see this problem confirmed by looking at the SQL execution statistics of the remote queries executed via V$SQLSTATS or DBA_HIST_SQLSTAT:
SQL_ID FETCHES END_OF_FETCH_COUNT EXECUTIONS PARSE_CALLS ROWS_PROCESSED
------------- ---------- ------------------ ---------- ----------- --------------
dtjb3bxg1ysdk 730 10 110 110 20000100
d36r1d00yaunc 110 110 108 108 0
The first SQL_ID corresponds to the remote query used by my first successful example - it already confirms that the remote query was actually executed 110 times (=> 11 PX servers times 10 branches) instead of the expected 10 times. The "ROWS_PROCESSED" and "END_OF_FETCH_COUNT" suggest what seems to have happened: Ten of the executions actually fetched the rows to the end (10 times 2M rows), but 100 of them fetched only a single row and then stopped the processing.
This also explains why my slightly changed query influenced the runtime profile so dramatically: Since no rows were returned by the remote query all 110 executions had to run to the end, since they would only stop early after fetching the first row, but there were no rows to fetch.
This problem is tracked via bug 19565803: INEFFICIENT EXECUTION OF PARALLEL UNION-ALL INVOLVING REMOTE TABLES and is mentioned to be fixed in 12.2, but there doesn't seem to be patch/fix available for 12.1
So in principle my original idea should work, assuming that the remote queries just access different chunks/partitions of the same segment the issue just described shouldn't matter. However, depending on what the remote queries exactly do, some other scenarios might be affected. The possible overhead depends on how much work the remote queries have to perform before they return the first row.
Thursday, March 12, 2015
12c Parallel Execution New Features: Concurrent UNION ALL - Part 2
In the first part of this series I've focused on the parallel degree chosen by the optimizer when dealing with the new concurrent UNION ALL feature.
I've shown that for the variant with serial branches only in the UNION ALL in principle the number of branches dictates the parallel degree determined, even in cases of more complex plans that mix such a serial branch only UNION ALL operator with some other parallel stuff for example via a join.
In this part I'll focus on the runtime behaviour of the feature, but before doing so let me show you what happens if you start mixing serial and parallel branches in the UNION ALL, like that (using the identical table setup as in the previous part):
select count(*) from (
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t_2
where regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t_2
where regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
);
The EXPLAIN PLAN output then looks like this:
-------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | SORT AGGREGATE | | | | | | 2 | PX COORDINATOR | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | Q1,00 | PCWP | | | 5 | VIEW | | Q1,00 | PCWP | | | 6 | UNION-ALL | | Q1,00 | PCWP | | | 7 | PX SELECTOR | | Q1,00 | PCWP | | |* 8 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 9 | PX SELECTOR | | Q1,00 | PCWP | | |* 10 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 11 | PX SELECTOR | | Q1,00 | PCWP | | |* 12 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 13 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |* 14 | TABLE ACCESS FULL| T_2 | Q1,00 | PCWP | | | 15 | PX SELECTOR | | Q1,00 | PCWP | | |* 16 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 17 | PX SELECTOR | | Q1,00 | PCWP | | |* 18 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 19 | PX SELECTOR | | Q1,00 | PCWP | | |* 20 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 21 | PX SELECTOR | | Q1,00 | PCWP | | |* 22 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 23 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |* 24 | TABLE ACCESS FULL| T_2 | Q1,00 | PCWP | | | 25 | PX SELECTOR | | Q1,00 | PCWP | | |* 26 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 27 | PX SELECTOR | | Q1,00 | PCWP | | |* 28 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | | 29 | PX SELECTOR | | Q1,00 | PCWP | | |* 30 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 10 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 12 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 14 - filter( REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 16 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 18 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 20 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 22 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 24 - filter( REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 26 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 28 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) 30 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i')) Note ----- - Degree of Parallelism is 8 because of table propertySo now the concurrent UNION ALL feature got activated automatically (no PQ_CONCURRENT_UNION hint required) as described in the documentation / white paper, as you can tell from the PX SELECTOR operators shown for the serial branches (and the fact these operations are now shown as PCWP). So having at least one parallel branch activates the feature by default, and will even be used if you happen to have parallel branches only, and you would have to use the NO_PQ_CONCURRENT_UNION hint to prevent the feature usage. The notes section now shows a parallel degree of 8, and when I execute this SQL the actual degree used at runtime agrees to that, so in that case here the degree shown seems to be correct. So how does the feature now behave at runtime? For that purpose I've changed the set-up slightly, by increasing the size of the serial table T2 to 2M rows (the initial setup used 200K rows), so that the parallel and serial table have the same number of rows. I've also changed the parallel degree of T_2 to 4 instead of 8 to make some points more obvious in the output:
-- This is the Parallel table
drop table t_2 purge;
drop table t2 purge;
create table t_2
compress
as
select
rownum as id
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;
exec dbms_stats.gather_table_stats(null, 't_2')
alter table t_2 parallel 4;
-- This is the serial table
create table t2
compress
as
select
(rownum * 2) + 1 as id
, mod(rownum, 2000) + 1 as id2
, rpad('x', 100) as filler
from
(select /*+ cardinality(100000) */ * from dual
connect by
level <= 100000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;
exec dbms_stats.gather_table_stats(null, 't2')
So let's execute above query with this slightly modified set-up and look at the output of my XPLAN_ASH script to monitor the execution. These are some snippets from the script output after a couple of seconds:
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
1| | | 0| 0| 0| (0) |
2| 3844K| | 4| 0| 4|@@@@ (4) |
3| 3844K| | 4| 0| 4|@@@@ (4) |
4| 3844K| | 4| 0| 4|@@@@ (4) |
5| 3844K| | 4| 0| 4|@@@@ (4) |
6| 3844K| | 4| 0| 4|@@@@ (4) |
7| 3844K| | 4| 0| 4|@@@@ (4) |
8| 3844K| | 4| 0| 4|@@@@ (4) |
9| 3844K| | 4| 0| 4|@@@@ (4) |
10| 3844K| | 4| 0| 4|@@@@ (4) |
11| 3844K| | 4| 0| 4|@@@@ (4) |
12| 3844K| | 4| 0| 4|@@@@ (4) |
13| 3844K| | 4| 0| 4|@@@@ (4) |
14| 3844K| | 4| 0| 4|@@@@ (4) |
15| 3844K| | 4| 0| 4|@@@@ (4) |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Act | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | SELECT STATEMENT | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 1 | | SORT AGGREGATE | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 2 | | PX COORDINATOR | | 5 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 3 | | PX SEND QC (RANDOM) | :TQ10000 | 4 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 4 | | SORT AGGREGATE | | 4 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 5 | | VIEW | | 4 | 2984K | | | | | 0:P002(0)[753K],P001(0)[745K],P000(0)[744K],P003(0)[742K],sqlplus.exe(0)[0] |
| 6 | | UNION-ALL | | 4 | 2984K | | | | | 0:P002(0)[753K],P001(0)[745K],P000(0)[744K],P003(0)[742K],sqlplus.exe(0)[0] |
| 7 | | PX SELECTOR | | 4 | 753K | | | | | 0:P002(0)[753K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 8 | ==> | TABLE ACCESS FULL| T2 | 4 | 753K | 2 | 14 | 14 | ################### | 1:P002(14)[753K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 9 | | PX SELECTOR | | 3 | 745K | | | | | 0:P001(0)[745K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 10 | ==> | TABLE ACCESS FULL| T2 | 3 | 745K | 2 | 14 | 14 | ################### | 1:P001(14)[745K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 11 | | PX SELECTOR | | 2 | 744K | | | | | 0:P000(0)[744K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 12 | ==> | TABLE ACCESS FULL| T2 | 2 | 744K | 2 | 14 | 14 | ################### | 1:P000(14)[744K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 13 | | PX BLOCK ITERATOR | | 1 | 742K | | | | | 0:P003(0)[742K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 14 | ==> | TABLE ACCESS FULL| T_2 | 20 | 742K | 2 | 14 | 14 | ################### | 1:P003(14)[742K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 15 | | PX SELECTOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 16 | | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 17 | | PX SELECTOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 18 | | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 19 | | PX SELECTOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 20 | | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 21 | | PX SELECTOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 22 | | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 23 | | PX BLOCK ITERATOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 24 | | TABLE ACCESS FULL| T_2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 25 | | PX SELECTOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 26 | | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
For brevity I've omitted some of the columns from the output, and want to focus specifically on the "Execs" and row distribution per Parallel Execution Server. We can see from the "Activity Timeline" that the statement runs on average with four PX servers active as desired (remember I've lowered the degree to 4 for this run), so the work distribution is optimal at present. What we can tell from the "Execs" and "row distribution" output is that the PX servers in principle are assigned in the following way
- the first branch gets executed by all four PX servers but only one is assigned by the PX SELECTOR to actually do something
- the second branch gets executed by the remaining three PX servers but only one is assigned by the PX SELECTOR to actually do something
- the third branch gets executed by the remaining two PX servers but only one is assigned by the PX SELECTOR to actually do something
- the fourth branch gets executed by the remaining PX server
The fourth branch is particularly interesting because it's actually a parallel full table scan that is usually split into granules via the PX BLOCK ITERATOR operator and each granule is assigned to one of the (usually > 1) PX servers working on the operation. However, in this particular case, since there is only one PX server left (at present) actually only this PX server works on this "parallel" full table scan (and gets all the granules assigned), which isn't a problem in terms of parallelism since all four PX servers have something to do but results in a rather unusual distribution profile of this "parallel" full table scan. You can see this confirmed from the "row distribution" shown in the last column where you see in square brackets behind each process the number of rows produced (the number in parenthesis represents the ASH sample count per process and plan operation), and only one of the PX servers produced rows so far for this "parallel" full table scan operation.
Here's the script output some seconds later (45 seconds runtime so far):
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
1| | | 0| 0| 0| (0) |
2| 3844K| | 4| 0| 4|@@@@ (4) |
3| 3844K| | 4| 0| 4|@@@@ (4) |
4| 3844K| | 4| 0| 4|@@@@ (4) |
5| 3844K| | 4| 0| 4|@@@@ (4) |
6| 3844K| | 4| 0| 4|@@@@ (4) |
7| 3844K| | 4| 0| 4|@@@@ (4) |
8| 3844K| | 4| 0| 4|@@@@ (4) |
9| 3844K| | 4| 0| 4|@@@@ (4) |
10| 3844K| | 4| 0| 4|@@@@ (4) |
11| 3844K| | 4| 0| 4|@@@@ (4) |
12| 3844K| | 4| 0| 4|@@@@ (4) |
13| 3844K| | 4| 0| 4|@@@@ (4) |
14| 3844K| | 4| 0| 4|@@@@ (4) |
15| 3844K| | 4| 0| 4|@@@@ (4) |
16| 3844K| | 4| 0| 4|@@@@ (4) |
17| 3844K| | 4| 0| 4|@@@@ (4) |
18| 3844K| | 4| 0| 4|@@@@ (4) |
19| 3844K| | 4| 0| 4|@@@@ (4) |
20| 3844K| | 4| 0| 4|@@@@ (4) |
21| 3844K| | 4| 0| 4|@@@@ (4) |
22| 3844K| | 4| 0| 4|@@@@ (4) |
23| 3844K| | 4| 0| 4|@@@@ (4) |
24| 3844K| | 4| 0| 4|@@@@ (4) |
25| 3844K| | 4| 0| 4|@@@@ (4) |
26| 3844K| | 4| 0| 4|@@@@ (4) |
27| 3844K| | 4| 0| 4|@@@@ (4) |
28| 3844K| | 4| 0| 4|@@@@ (4) |
29| 3844K| | 4| 0| 4|@@@@ (4) |
30| 3844K| | 4| 0| 4|@@@@ (4) |
31| 3844K| | 4| 0| 4|@@@@ (4) |
32| 3844K| | 4| 0| 4|@@@@ (4) |
33| 3844K| | 4| 0| 4|@@@@ (4) |
34| 3844K| | 4| 0| 4|@@@@ (4) |
35| 3844K| | 4| 0| 4|@@@@ (4) |
36| 3844K| | 4| 0| 4|@@@@ (4) |
37| 3844K| | 4| 0| 4|@@@@ (4) |
38| 3844K| | 4| 0| 4|@@@@ (4) |
39| 3844K| | 4| 0| 4|@@@@ (4) |
40| 3844K| | 4| 0| 4|@@@@ (4) |
41| 3844K| | 4| 0| 4|@@@@ (4) |
42| 3844K| | 4| 0| 4|@@@@ (4) |
43| 3844K| | 4| 0| 4|@@@@ (4) |
44| 3844K| | 4| 0| 4|@@@@ (4) |
45| 3844K| | 4| 0| 4|@@@@ (4) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Act | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | SELECT STATEMENT | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 1 | | SORT AGGREGATE | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 2 | | PX COORDINATOR | | 5 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 3 | | PX SEND QC (RANDOM) | :TQ10000 | 4 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 4 | | SORT AGGREGATE | | 4 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 5 | | VIEW | | 4 | 8747K | | | | | 0:P002(0)[2200K],P000(0)[2187K],P003(0)[2180K],P001(0)[2180K],sqlplus.exe(0)[0] |
| 6 | | UNION-ALL | | 4 | 8747K | | | | | 0:P002(0)[2200K],P000(0)[2187K],P003(0)[2180K],P001(0)[2180K],sqlplus.exe(0)[0] |
| 7 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 8 | ==> | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ################### | 1:P002(41)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 9 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 10 | ==> | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 42 | 42 | ################### | 1:P001(42)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 11 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 12 | ==> | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ################### | 1:P000(41)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 13 | | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P003(0)[1889K],P000(0)[37K],P001(0)[37K],P002(0)[37K],sqlplus.exe(0)[0] |
|* 14 | ==> | TABLE ACCESS FULL| T_2 | 52 | 2000K | 2 | 42 | 40 | ################### | 3:P003(39)[1889K],P000(1)[37K],P002(1)[37K],P001(0)[37K],sqlplus.exe(0)[0] |
| 15 | | PX SELECTOR | | 4 | 291K | | | | | 0:P003(0)[291K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 16 | ==> | TABLE ACCESS FULL| T2 | 4 | 291K | 41 | 5 | 5 | ### | 1:P003(5)[291K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 17 | | PX SELECTOR | | 3 | 163K | | | | | 0:P002(0)[163K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 18 | ==> | TABLE ACCESS FULL| T2 | 3 | 163K | 44 | 2 | 2 | # | 1:P002(2)[163K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 19 | | PX SELECTOR | | 2 | 150K | | | | | 0:P000(0)[150K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 20 | ==> | TABLE ACCESS FULL| T2 | 2 | 150K | 44 | 2 | 2 | # | 1:P000(2)[150K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 21 | | PX SELECTOR | | 1 | 143K | | | | | 0:P001(0)[143K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 22 | ==> | TABLE ACCESS FULL| T2 | 1 | 143K | 44 | 2 | 2 | # | 1:P001(2)[143K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 23 | | PX BLOCK ITERATOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 24 | | TABLE ACCESS FULL| T_2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 25 | | PX SELECTOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 26 | | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The work distribution is still perfect, four servers active all the time. Interestingly you can see now that the operations that previously were only executed by less than four PX servers now all show four executions, although it doesn't really change the work performed. So it looks like once a PX server is done with its work it executes the next branch only to find out that nothing is left to do, and immediately going to the next branch, until there's something left to do. This implementation behaviour (together with something that is probably is a bug) will become relevant when dealing with remote branches, as I'll show in the final part.
You can tell from the "row distribution" column for operation ID 14 that obviously some granules were not processed yet by that single PX server working on the "parallel" full table scan so far and some 37K rows each were processed by the other PX servers when they obviously finished their work a bit earlier and finally joined the "parallel" full table scan.
We now have the PX servers working on the next four branches, which are just four serial branches of similar workload, so they should all take around the same time, and now that we already have an idea how this works we can expect all of them around the same time to join the next parallel full table scan following (well, one PX server is a bit ahead of the others, so not really exactly around the same time).
This what things look like after approx. 80 seconds:
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
1| | | 0| 0| 0| (0) |
2| 3844K| | 4| 0| 4|@@@@ (4) |
3| 3844K| | 4| 0| 4|@@@@ (4) |
4| 3844K| | 4| 0| 4|@@@@ (4) |
5| 3844K| | 4| 0| 4|@@@@ (4) |
6| 3844K| | 4| 0| 4|@@@@ (4) |
7| 3844K| | 4| 0| 4|@@@@ (4) |
8| 3844K| | 4| 0| 4|@@@@ (4) |
9| 3844K| | 4| 0| 4|@@@@ (4) |
10| 3844K| | 4| 0| 4|@@@@ (4) |
11| 3844K| | 4| 0| 4|@@@@ (4) |
12| 3844K| | 4| 0| 4|@@@@ (4) |
13| 3844K| | 4| 0| 4|@@@@ (4) |
14| 3844K| | 4| 0| 4|@@@@ (4) |
15| 3844K| | 4| 0| 4|@@@@ (4) |
16| 3844K| | 4| 0| 4|@@@@ (4) |
17| 3844K| | 4| 0| 4|@@@@ (4) |
18| 3844K| | 4| 0| 4|@@@@ (4) |
19| 3844K| | 4| 0| 4|@@@@ (4) |
20| 3844K| | 4| 0| 4|@@@@ (4) |
21| 3844K| | 4| 0| 4|@@@@ (4) |
22| 3844K| | 4| 0| 4|@@@@ (4) |
23| 3844K| | 4| 0| 4|@@@@ (4) |
24| 3844K| | 4| 0| 4|@@@@ (4) |
25| 3844K| | 4| 0| 4|@@@@ (4) |
26| 3844K| | 4| 0| 4|@@@@ (4) |
27| 3844K| | 4| 0| 4|@@@@ (4) |
28| 3844K| | 4| 0| 4|@@@@ (4) |
29| 3844K| | 4| 0| 4|@@@@ (4) |
30| 3844K| | 4| 0| 4|@@@@ (4) |
31| 3844K| | 4| 0| 4|@@@@ (4) |
32| 3844K| | 4| 0| 4|@@@@ (4) |
33| 3844K| | 4| 0| 4|@@@@ (4) |
34| 3844K| | 4| 0| 4|@@@@ (4) |
35| 3844K| | 4| 0| 4|@@@@ (4) |
36| 3844K| | 4| 0| 4|@@@@ (4) |
37| 3844K| | 4| 0| 4|@@@@ (4) |
38| 3844K| | 4| 0| 4|@@@@ (4) |
39| 3844K| | 4| 0| 4|@@@@ (4) |
40| 3844K| | 4| 0| 4|@@@@ (4) |
41| 3844K| | 4| 0| 4|@@@@ (4) |
42| 3844K| | 4| 0| 4|@@@@ (4) |
43| 3844K| | 4| 0| 4|@@@@ (4) |
44| 3844K| | 4| 0| 4|@@@@ (4) |
45| 3844K| | 4| 0| 4|@@@@ (4) |
46| 3844K| | 4| 0| 4|@@@@ (4) |
47| 3844K| | 4| 0| 4|@@@@ (4) |
48| 3844K| | 4| 0| 4|@@@@ (4) |
49| 3844K| | 4| 0| 4|@@@@ (4) |
50| 3844K| | 4| 0| 4|@@@@ (4) |
51| 3844K| | 4| 0| 4|@@@@ (4) |
52| 3844K| | 4| 0| 4|@@@@ (4) |
53| 3844K| | 4| 0| 4|@@@@ (4) |
54| 3844K| | 4| 0| 4|@@@@ (4) |
55| 3844K| | 4| 0| 4|@@@@ (4) |
56| 3844K| | 4| 0| 4|@@@@ (4) |
57| 3844K| | 4| 0| 4|@@@@ (4) |
58| 3844K| | 4| 0| 4|@@@@ (4) |
59| 3844K| | 4| 0| 4|@@@@ (4) |
60| 3844K| | 4| 0| 4|@@@@ (4) |
61| 3844K| | 4| 0| 4|@@@@ (4) |
62| 3844K| | 4| 0| 4|@@@@ (4) |
63| 3844K| | 4| 0| 4|@@@@ (4) |
64| 3844K| | 4| 0| 4|@@@@ (4) |
65| 3844K| | 4| 0| 4|@@@@ (4) |
66| 3844K| | 4| 0| 4|@@@@ (4) |
67| 3844K| | 4| 0| 4|@@@@ (4) |
68| 3844K| | 4| 0| 4|@@@@ (4) |
69| 3844K| | 4| 0| 4|@@@@ (4) |
70| 3844K| | 4| 0| 4|@@@@ (4) |
71| 3844K| | 4| 0| 4|@@@@ (4) |
72| 3844K| | 4| 0| 4|@@@@ (4) |
73| 3844K| | 4| 0| 4|@@@@ (4) |
74| 3844K| | 4| 0| 4|@@@@ (4) |
75| 3844K| | 4| 0| 4|@@@@ (4) |
76| | | 0| 0| 0| (0) |
77| 3844K| | 4| 0| 4|@@@@ (4) |
78| 3844K| | 4| 0| 4|@@@@ (4) |
79| 7688K| | 8| 0| 8|@@@@@@@@ (8) |
80| | | 0| 0| 0| (0) |
81| 3844K| | 4| 0| 4|@@@@ (4) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Act | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | SELECT STATEMENT | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 1 | | SORT AGGREGATE | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 2 | | PX COORDINATOR | | 5 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 3 | | PX SEND QC (RANDOM) | :TQ10000 | 4 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 4 | | SORT AGGREGATE | | 4 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 5 | | VIEW | | 4 | 16M | 65 | 1 | 1 | # | 1:P002(1)[3927K],P001(0)[3901K],P000(0)[3900K],P003(0)[3897K],sqlplus.exe(0)[0] |
| 6 | | UNION-ALL | | 4 | 16M | | | | | 0:P002(0)[3927K],P001(0)[3901K],P000(0)[3900K],P003(0)[3897K],sqlplus.exe(0)[0] |
| 7 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 8 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ########### | 1:P002(41)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 9 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 10 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 42 | 42 | ########### | 1:P001(42)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 11 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 12 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ########### | 1:P000(41)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 13 | | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P003(0)[1889K],P000(0)[37K],P001(0)[37K],P002(0)[37K],sqlplus.exe(0)[0] |
|* 14 | | TABLE ACCESS FULL| T_2 | 52 | 2000K | 2 | 42 | 40 | ########### | 3:P003(39)[1889K],P000(1)[37K],P002(1)[37K],P001(0)[37K],sqlplus.exe(0)[0] |
| 15 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P003(0)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 16 | ==> | TABLE ACCESS FULL| T2 | 4 | 2000K | 41 | 41 | 39 | ########### | 1:P003(40)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 17 | | PX SELECTOR | | 4 | 1890K | | | | | 0:P002(0)[1890K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 18 | ==> | TABLE ACCESS FULL| T2 | 4 | 1890K | 44 | 38 | 35 | ########## | 1:P002(36)[1890K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 19 | | PX SELECTOR | | 3 | 1863K | | | | | 0:P000(0)[1863K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 20 | ==> | TABLE ACCESS FULL| T2 | 3 | 1863K | 44 | 38 | 36 | ########## | 1:P000(37)[1863K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 21 | | PX SELECTOR | | 2 | 1864K | | | | | 0:P001(0)[1864K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 22 | ==> | TABLE ACCESS FULL| T2 | 2 | 1864K | 44 | 38 | 36 | ########## | 1:P001(37)[1864K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 23 | | PX BLOCK ITERATOR | | 1 | 7932 | | | | | 0:P003(0)[7932],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 24 | | TABLE ACCESS FULL| T_2 | 1 | 7932 | | | | | 0:P003(0)[7932],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 25 | | PX SELECTOR | | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 26 | | TABLE ACCESS FULL| T2 | 0 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
We still have a perfect work distribution (ignore the small glitch in ASH instrumentation in the last couple of seconds), and the first of the four serial branches is completed and this PX server has just started to work on the "parallel" full table scan following, the other three are just finishing their serial full table scan.
Again forty seconds later:
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
2| 3844K| | 2| 0| 2|@@ (2) |
4| 3844K| | 4| 0| 4|@@@@ (4) |
6| 3844K| | 4| 0| 4|@@@@ (4) |
8| 3844K| | 4| 0| 4|@@@@ (4) |
10| 3844K| | 4| 0| 4|@@@@ (4) |
12| 3844K| | 4| 0| 4|@@@@ (4) |
14| 3844K| | 4| 0| 4|@@@@ (4) |
16| 3844K| | 4| 0| 4|@@@@ (4) |
18| 3844K| | 4| 0| 4|@@@@ (4) |
20| 3844K| | 4| 0| 4|@@@@ (4) |
22| 3844K| | 4| 0| 4|@@@@ (4) |
24| 3844K| | 4| 0| 4|@@@@ (4) |
26| 3844K| | 4| 0| 4|@@@@ (4) |
28| 3844K| | 4| 0| 4|@@@@ (4) |
30| 3844K| | 4| 0| 4|@@@@ (4) |
32| 3844K| | 4| 0| 4|@@@@ (4) |
34| 3844K| | 4| 0| 4|@@@@ (4) |
36| 3844K| | 4| 0| 4|@@@@ (4) |
38| 3844K| | 4| 0| 4|@@@@ (4) |
40| 3844K| | 4| 0| 4|@@@@ (4) |
42| 3844K| | 4| 0| 4|@@@@ (4) |
43| 3844K| | 4| 0| 4|@@@@ (4) |
44| 3844K| | 4| 0| 4|@@@@ (4) |
45| 3844K| | 4| 0| 4|@@@@ (4) |
46| 3844K| | 4| 0| 4|@@@@ (4) |
47| 3844K| | 4| 0| 4|@@@@ (4) |
48| 3844K| | 4| 0| 4|@@@@ (4) |
49| 3844K| | 4| 0| 4|@@@@ (4) |
50| 3844K| | 4| 0| 4|@@@@ (4) |
51| 3844K| | 4| 0| 4|@@@@ (4) |
52| 3844K| | 4| 0| 4|@@@@ (4) |
53| 3844K| | 4| 0| 4|@@@@ (4) |
54| 3844K| | 4| 0| 4|@@@@ (4) |
55| 3844K| | 4| 0| 4|@@@@ (4) |
56| 3844K| | 4| 0| 4|@@@@ (4) |
57| 3844K| | 4| 0| 4|@@@@ (4) |
58| 3844K| | 4| 0| 4|@@@@ (4) |
59| 3844K| | 4| 0| 4|@@@@ (4) |
60| 3844K| | 4| 0| 4|@@@@ (4) |
61| 3844K| | 4| 0| 4|@@@@ (4) |
62| 3844K| | 4| 0| 4|@@@@ (4) |
63| 3844K| | 4| 0| 4|@@@@ (4) |
64| 3844K| | 4| 0| 4|@@@@ (4) |
65| 3844K| | 4| 0| 4|@@@@ (4) |
66| 3844K| | 4| 0| 4|@@@@ (4) |
67| 3844K| | 4| 0| 4|@@@@ (4) |
68| 3844K| | 4| 0| 4|@@@@ (4) |
69| 3844K| | 4| 0| 4|@@@@ (4) |
70| 3844K| | 4| 0| 4|@@@@ (4) |
71| 3844K| | 4| 0| 4|@@@@ (4) |
72| 3844K| | 4| 0| 4|@@@@ (4) |
73| 3844K| | 4| 0| 4|@@@@ (4) |
74| 3844K| | 4| 0| 4|@@@@ (4) |
75| 3844K| | 4| 0| 4|@@@@ (4) |
76| | | 0| 0| 0| (0) |
77| 3844K| | 4| 0| 4|@@@@ (4) |
78| 3844K| | 4| 0| 4|@@@@ (4) |
79| 7688K| | 8| 0| 8|@@@@@@@@ (8) |
80| | | 0| 0| 0| (0) |
81| 3844K| | 4| 0| 4|@@@@ (4) |
82| 3844K| | 4| 0| 4|@@@@ (4) |
83| 3844K| | 4| 0| 4|@@@@ (4) |
84| 3844K| | 4| 0| 4|@@@@ (4) |
85| 3844K| | 4| 0| 4|@@@@ (4) |
86| 3844K| | 4| 0| 4|@@@@ (4) |
87| 3844K| | 4| 0| 4|@@@@ (4) |
88| 3844K| | 4| 0| 4|@@@@ (4) |
89| 3844K| | 4| 0| 4|@@@@ (4) |
90| 3844K| | 4| 0| 4|@@@@ (4) |
91| 3844K| | 4| 0| 4|@@@@ (4) |
92| 3844K| | 4| 0| 4|@@@@ (4) |
93| 3844K| | 4| 0| 4|@@@@ (4) |
94| 3844K| | 4| 0| 4|@@@@ (4) |
95| 3844K| | 4| 0| 4|@@@@ (4) |
96| 3844K| | 4| 0| 4|@@@@ (4) |
97| 961K| | 1| 0| 1|@ (1) |
98| 961K| | 1| 0| 1|@ (1) |
99| 961K| | 1| 0| 1|@ (1) |
100| 961K| | 1| 0| 1|@ (1) |
101| 961K| | 1| 0| 1|@ (1) |
102| 961K| | 1| 0| 1|@ (1) |
103| 961K| | 1| 0| 1|@ (1) |
104| 961K| | 1| 0| 1|@ (1) |
105| 961K| | 1| 0| 1|@ (1) |
106| 961K| | 1| 0| 1|@ (1) |
107| 961K| | 1| 0| 1|@ (1) |
108| 961K| | 1| 0| 1|@ (1) |
109| 961K| | 1| 0| 1|@ (1) |
110| 961K| | 1| 0| 1|@ (1) |
111| 961K| | 1| 0| 1|@ (1) |
112| 961K| | 1| 0| 1|@ (1) |
113| 961K| | 1| 0| 1|@ (1) |
114| 961K| | 1| 0| 1|@ (1) |
115| 961K| | 1| 0| 1|@ (1) |
116| 961K| | 1| 0| 1|@ (1) |
117| 961K| | 1| 0| 1|@ (1) |
118| 961K| | 1| 0| 1|@ (1) |
119| 961K| | 1| 0| 1|@ (1) |
120| 961K| | 1| 0| 1|@ (1) |
121| 961K| | 1| 0| 1|@ (1) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Act | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | SELECT STATEMENT | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 1 | | SORT AGGREGATE | | 1 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 2 | | PX COORDINATOR | | 5 | 0 | | | | | 0:P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 3 | | PX SEND QC (RANDOM) | :TQ10000 | 4 | 3 | | | | | 0:P000(0)[1],P001(0)[1],P003(0)[1],P002(0)[0],sqlplus.exe(0)[0] |
| 4 | | SORT AGGREGATE | | 4 | 3 | | | | | 0:P000(0)[1],P001(0)[1],P003(0)[1],P002(0)[0],sqlplus.exe(0)[0] |
| 5 | | VIEW | | 4 | 19M | 65 | 1 | 1 | # | 1:P002(1)[5904K],P001(0)[4505K],P003(0)[4499K],P000(0)[4498K],sqlplus.exe(0)[0] |
| 6 | | UNION-ALL | | 4 | 19M | | | | | 0:P002(0)[5904K],P001(0)[4505K],P003(0)[4499K],P000(0)[4498K],sqlplus.exe(0)[0] |
| 7 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 8 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ####### | 1:P002(41)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 9 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 10 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 42 | 42 | ####### | 1:P001(42)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 11 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 12 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ####### | 1:P000(41)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 13 | | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P003(0)[1889K],P000(0)[37K],P001(0)[37K],P002(0)[37K],sqlplus.exe(0)[0] |
|* 14 | | TABLE ACCESS FULL| T_2 | 52 | 2000K | 2 | 42 | 40 | ####### | 3:P003(39)[1889K],P000(1)[37K],P002(1)[37K],P001(0)[37K],sqlplus.exe(0)[0] |
| 15 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P003(0)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 16 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 41 | 43 | 41 | ######## | 1:P003(42)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 17 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 18 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 44 | 42 | 39 | ####### | 1:P002(40)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 19 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 20 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 44 | 43 | 41 | ######## | 1:P000(42)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 21 | | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 22 | | TABLE ACCESS FULL| T2 | 4 | 2000K | 44 | 43 | 41 | ######## | 1:P001(42)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 23 | | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P003(0)[610K],P001(0)[468K],P000(0)[461K],P002(0)[461K],sqlplus.exe(0)[0] |
|* 24 | | TABLE ACCESS FULL| T_2 | 52 | 2000K | 84 | 13 | 13 | ### | 4:P003(13)[610K],P001(10)[468K],P000(10)[461K],P002(10)[461K],sqlplus.exe(0)[0] |
| 25 | | PX SELECTOR | | 4 | 1406K | | | | | 0:P002(0)[1406K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 26 | ==> | TABLE ACCESS FULL| T2 | 4 | 1406K | 96 | 26 | 26 | ##### | 1:P002(26)[1406K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Something significant has happened now to the work distribution, starting from around second 97 of the execution only a single PX server is left active, all others are idle. We can see that the second parallel full table scan (operation 23 + 24) is now done, and as expected, it was executed by all four servers to a rather similar degree, although one of the four did more work than the others. It's also obvious that this operation took only approx. 13 seconds, whereas the previous scans all took approx. 42 seconds, so this operation was significantly quicker due to the fact that really multiple processes worked concurrently on the operation. Since now only a single operation is left to process, only a single process can be active concurrently which explains the work distribution "skew" observed.
This is the final script output after completion:
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
2| 3844K| | 2| 0| 2|@@ (2) |
4| 3844K| | 4| 0| 4|@@@@ (4) |
6| 3844K| | 4| 0| 4|@@@@ (4) |
8| 3844K| | 4| 0| 4|@@@@ (4) |
10| 3844K| | 4| 0| 4|@@@@ (4) |
12| 3844K| | 4| 0| 4|@@@@ (4) |
14| 3844K| | 4| 0| 4|@@@@ (4) |
16| 3844K| | 4| 0| 4|@@@@ (4) |
18| 3844K| | 4| 0| 4|@@@@ (4) |
20| 3844K| | 4| 0| 4|@@@@ (4) |
22| 3844K| | 4| 0| 4|@@@@ (4) |
24| 3844K| | 4| 0| 4|@@@@ (4) |
26| 3844K| | 4| 0| 4|@@@@ (4) |
28| 3844K| | 4| 0| 4|@@@@ (4) |
30| 3844K| | 4| 0| 4|@@@@ (4) |
32| 3844K| | 4| 0| 4|@@@@ (4) |
34| 3844K| | 4| 0| 4|@@@@ (4) |
36| 3844K| | 4| 0| 4|@@@@ (4) |
38| 3844K| | 4| 0| 4|@@@@ (4) |
40| 3844K| | 4| 0| 4|@@@@ (4) |
42| 3844K| | 4| 0| 4|@@@@ (4) |
44| 3844K| | 4| 0| 4|@@@@ (4) |
46| 3844K| | 4| 0| 4|@@@@ (4) |
48| 3844K| | 4| 0| 4|@@@@ (4) |
50| 3844K| | 4| 0| 4|@@@@ (4) |
52| 3844K| | 4| 0| 4|@@@@ (4) |
54| 3844K| | 4| 0| 4|@@@@ (4) |
56| 3844K| | 4| 0| 4|@@@@ (4) |
58| 3844K| | 4| 0| 4|@@@@ (4) |
60| 3844K| | 4| 0| 4|@@@@ (4) |
62| 3844K| | 4| 0| 4|@@@@ (4) |
64| 3844K| | 4| 0| 4|@@@@ (4) |
66| 3844K| | 4| 0| 4|@@@@ (4) |
68| 3844K| | 4| 0| 4|@@@@ (4) |
70| 3844K| | 4| 0| 4|@@@@ (4) |
72| 3844K| | 4| 0| 4|@@@@ (4) |
74| 3844K| | 4| 0| 4|@@@@ (4) |
76| 3844K| | 2| 0| 2|@@ (2) |
78| 3844K| | 4| 0| 4|@@@@ (4) |
79| 7688K| | 8| 0| 8|@@@@@@@@ (8) |
80| | | 0| 0| 0| (0) |
81| 3844K| | 4| 0| 4|@@@@ (4) |
82| 3844K| | 4| 0| 4|@@@@ (4) |
83| 3844K| | 4| 0| 4|@@@@ (4) |
84| 3844K| | 4| 0| 4|@@@@ (4) |
85| 3844K| | 4| 0| 4|@@@@ (4) |
86| 3844K| | 4| 0| 4|@@@@ (4) |
87| 3844K| | 4| 0| 4|@@@@ (4) |
88| 3844K| | 4| 0| 4|@@@@ (4) |
89| 3844K| | 4| 0| 4|@@@@ (4) |
90| 3844K| | 4| 0| 4|@@@@ (4) |
91| 3844K| | 4| 0| 4|@@@@ (4) |
92| 3844K| | 4| 0| 4|@@@@ (4) |
93| 3844K| | 4| 0| 4|@@@@ (4) |
94| 3844K| | 4| 0| 4|@@@@ (4) |
95| 3844K| | 4| 0| 4|@@@@ (4) |
96| 3844K| | 4| 0| 4|@@@@ (4) |
97| 961K| | 1| 0| 1|@ (1) |
98| 961K| | 1| 0| 1|@ (1) |
99| 961K| | 1| 0| 1|@ (1) |
100| 961K| | 1| 0| 1|@ (1) |
101| 961K| | 1| 0| 1|@ (1) |
102| 961K| | 1| 0| 1|@ (1) |
103| 961K| | 1| 0| 1|@ (1) |
104| 961K| | 1| 0| 1|@ (1) |
105| 961K| | 1| 0| 1|@ (1) |
106| 961K| | 1| 0| 1|@ (1) |
107| 961K| | 1| 0| 1|@ (1) |
108| 961K| | 1| 0| 1|@ (1) |
109| 961K| | 1| 0| 1|@ (1) |
110| 961K| | 1| 0| 1|@ (1) |
111| 961K| | 1| 0| 1|@ (1) |
112| 961K| | 1| 0| 1|@ (1) |
113| 961K| | 1| 0| 1|@ (1) |
114| 961K| | 1| 0| 1|@ (1) |
115| 961K| | 1| 0| 1|@ (1) |
116| 961K| | 1| 0| 1|@ (1) |
117| 961K| | 1| 0| 1|@ (1) |
118| 961K| | 1| 0| 1|@ (1) |
119| 961K| | 1| 0| 1|@ (1) |
120| 961K| | 1| 0| 1|@ (1) |
121| 961K| | 1| 0| 1|@ (1) |
122| 961K| | 1| 0| 1|@ (1) |
123| 961K| | 1| 0| 1|@ (1) |
124| 961K| | 1| 0| 1|@ (1) |
125| 961K| | 1| 0| 1|@ (1) |
126| 961K| | 1| 0| 1|@ (1) |
127| 961K| | 1| 0| 1|@ (1) |
128| 961K| | 1| 0| 1|@ (1) |
129| 961K| | 1| 0| 1|@ (1) |
130| 961K| | 1| 0| 1|@ (1) |
131| 961K| | 1| 0| 1|@ (1) |
132| 961K| | 1| 0| 1|@ (1) |
133| 961K| | 1| 0| 1|@ (1) |
134| 961K| | 1| 0| 1|@ (1) |
135| 961K| | 1| 0| 1|@ (1) |
136| 961K| | 1| 0| 1|@ (1) |
137| 961K| | 1| 0| 1|@ (1) |
138| 961K| | 1| 0| 1|@ (1) |
139| 961K| | 1| 0| 1|@ (1) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | | | | | 0:sqlplus.exe(0)[1],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | 0:sqlplus.exe(0)[1],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] |
| 2 | PX COORDINATOR | | 5 | 4 | | | | | 0:sqlplus.exe(0)[4],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4 | 4 | | | | | 0:P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],sqlplus.exe(0)[0] |
| 4 | SORT AGGREGATE | | 4 | 4 | | | | | 0:P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],sqlplus.exe(0)[0] |
| 5 | VIEW | | 4 | 20M | 65 | 1 | 1 | # | 1:P002(1)[6498K],P001(0)[4505K],P003(0)[4499K],P000(0)[4498K],sqlplus.exe(0)[0] |
| 6 | UNION-ALL | | 4 | 20M | | | | | 0:P002(0)[6498K],P001(0)[4505K],P003(0)[4499K],P000(0)[4498K],sqlplus.exe(0)[0] |
| 7 | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 8 | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ###### | 1:P002(41)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 9 | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 10 | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 42 | 42 | ####### | 1:P001(42)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 11 | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 12 | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ###### | 1:P000(41)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 13 | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P003(0)[1889K],P000(0)[37K],P001(0)[37K],P002(0)[37K],sqlplus.exe(0)[0] |
|* 14 | TABLE ACCESS FULL| T_2 | 52 | 2000K | 2 | 42 | 40 | ####### | 3:P003(39)[1889K],P000(1)[37K],P002(1)[37K],P001(0)[37K],sqlplus.exe(0)[0] |
| 15 | PX SELECTOR | | 4 | 2000K | | | | | 0:P003(0)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 16 | TABLE ACCESS FULL| T2 | 4 | 2000K | 41 | 43 | 41 | ####### | 1:P003(42)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 17 | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 18 | TABLE ACCESS FULL| T2 | 4 | 2000K | 44 | 42 | 39 | ####### | 1:P002(40)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 19 | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 20 | TABLE ACCESS FULL| T2 | 4 | 2000K | 44 | 43 | 41 | ####### | 1:P000(42)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 21 | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 22 | TABLE ACCESS FULL| T2 | 4 | 2000K | 44 | 43 | 41 | ####### | 1:P001(42)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 23 | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P003(0)[610K],P001(0)[468K],P000(0)[461K],P002(0)[461K],sqlplus.exe(0)[0] |
|* 24 | TABLE ACCESS FULL| T_2 | 52 | 2000K | 84 | 13 | 13 | ### | 4:P003(13)[610K],P001(10)[468K],P000(10)[461K],P002(10)[461K],sqlplus.exe(0)[0] |
| 25 | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 26 | TABLE ACCESS FULL| T2 | 4 | 2000K | 96 | 44 | 44 | ####### | 1:P002(44)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
So for the last forty seconds only one process was active which represents the final operation and all operations have now been executed by all PX servers.
We can conclude a number of things from these monitoring results:
1. At the end all operations are executed by all PX servers in a concurrent UNION ALL operation
2. The PX SELECTOR operator assigns only one of them to non-parallel branches
3. Depending on the sequence and kind of branches (non-parallel, parallel) you might end up with some unusual execution profiles for parallel branches
4. If you have non-parallel branches towards the end not all of the PX servers might end up doing something when that part gets processed
The last point suggests that it's advisable to move parallel branches towards the end of the UNION ALL to make most out of the parallel execution.
If I re-arrange above statement in such a way that the two parallel branches are executed last, the final monitoring output looks like that:
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | |
| | | | | AVERAGE|AVERAGE |
| | | | | ACTIVE|ACTIVE SESSIONS |
DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|GRAPH |
-------------|------|------|----------|----------|----------|----------------------------|
2| 3322K| | 2,5| 0| 2,5|@@@ (2,5) |
4| 3844K| | 4| 0| 4|@@@@ (4) |
6| 3844K| | 4| 0| 4|@@@@ (4) |
8| 3844K| | 4| 0| 4|@@@@ (4) |
10| 3844K| | 4| 0| 4|@@@@ (4) |
11| 3844K| | 4| 0| 4|@@@@ (4) |
12| 3844K| | 4| 0| 4|@@@@ (4) |
13| 3844K| | 4| 0| 4|@@@@ (4) |
14| 3844K| | 4| 0| 4|@@@@ (4) |
15| 3844K| | 4| 0| 4|@@@@ (4) |
16| 3844K| | 4| 0| 4|@@@@ (4) |
17| 3844K| | 4| 0| 4|@@@@ (4) |
18| 3844K| | 4| 0| 4|@@@@ (4) |
19| 3844K| | 4| 0| 4|@@@@ (4) |
20| 3844K| | 4| 0| 4|@@@@ (4) |
21| 3844K| | 4| 0| 4|@@@@ (4) |
22| 3844K| | 4| 0| 4|@@@@ (4) |
23| 3844K| | 4| 0| 4|@@@@ (4) |
24| 3844K| | 4| 0| 4|@@@@ (4) |
25| 3844K| | 4| 0| 4|@@@@ (4) |
26| 3844K| | 4| 0| 4|@@@@ (4) |
27| 3844K| | 4| 0| 4|@@@@ (4) |
28| 3844K| | 4| 0| 4|@@@@ (4) |
29| 3844K| | 4| 0| 4|@@@@ (4) |
30| 3844K| | 4| 0| 4|@@@@ (4) |
31| 3844K| | 4| 0| 4|@@@@ (4) |
32| 3844K| | 4| 0| 4|@@@@ (4) |
33| 3844K| | 4| 0| 4|@@@@ (4) |
34| 3844K| | 4| 0| 4|@@@@ (4) |
35| 3844K| | 4| 0| 4|@@@@ (4) |
36| 3844K| | 4| 0| 4|@@@@ (4) |
37| 3844K| | 4| 0| 4|@@@@ (4) |
38| 3844K| | 4| 0| 4|@@@@ (4) |
39| 3844K| | 4| 0| 4|@@@@ (4) |
40| 3844K| | 4| 0| 4|@@@@ (4) |
41| 3844K| | 4| 0| 4|@@@@ (4) |
42| 3844K| | 4| 0| 4|@@@@ (4) |
43| 3844K| | 4| 0| 4|@@@@ (4) |
44| 3844K| | 4| 0| 4|@@@@ (4) |
45| 3844K| | 4| 0| 4|@@@@ (4) |
46| 3844K| | 4| 0| 4|@@@@ (4) |
47| 3844K| | 4| 0| 4|@@@@ (4) |
48| 3844K| | 4| 0| 4|@@@@ (4) |
49| 3844K| | 4| 0| 4|@@@@ (4) |
50| 3844K| | 4| 0| 4|@@@@ (4) |
51| 3844K| | 4| 0| 4|@@@@ (4) |
52| 3844K| | 4| 0| 4|@@@@ (4) |
53| 3844K| | 4| 0| 4|@@@@ (4) |
54| 3844K| | 4| 0| 4|@@@@ (4) |
55| 3844K| | 4| 0| 4|@@@@ (4) |
56| 3844K| | 4| 0| 4|@@@@ (4) |
57| 3844K| | 4| 0| 4|@@@@ (4) |
58| 3844K| | 4| 0| 4|@@@@ (4) |
59| 3844K| | 4| 0| 4|@@@@ (4) |
60| 3844K| | 4| 0| 4|@@@@ (4) |
61| 3844K| | 4| 0| 4|@@@@ (4) |
62| 3844K| | 4| 0| 4|@@@@ (4) |
63| 3844K| | 4| 0| 4|@@@@ (4) |
64| 3844K| | 4| 0| 4|@@@@ (4) |
65| 3844K| | 4| 0| 4|@@@@ (4) |
66| 3844K| | 4| 0| 4|@@@@ (4) |
67| 3844K| | 4| 0| 4|@@@@ (4) |
68| 3844K| | 4| 0| 4|@@@@ (4) |
69| 3844K| | 4| 0| 4|@@@@ (4) |
70| 3844K| | 4| 0| 4|@@@@ (4) |
71| 3844K| | 4| 0| 4|@@@@ (4) |
72| 3844K| | 4| 0| 4|@@@@ (4) |
73| 3844K| | 4| 0| 4|@@@@ (4) |
74| 3844K| | 4| 0| 4|@@@@ (4) |
75| 3844K| | 4| 0| 4|@@@@ (4) |
76| 3844K| | 4| 0| 4|@@@@ (4) |
77| 3844K| | 4| 0| 4|@@@@ (4) |
78| 3844K| | 4| 0| 4|@@@@ (4) |
79| 3844K| | 4| 0| 4|@@@@ (4) |
80| 3844K| | 4| 0| 4|@@@@ (4) |
81| 3844K| | 4| 0| 4|@@@@ (4) |
82| 3844K| | 4| 0| 4|@@@@ (4) |
83| 3844K| | 4| 0| 4|@@@@ (4) |
84| 3844K| | 4| 0| 4|@@@@ (4) |
85| 3844K| | 4| 0| 4|@@@@ (4) |
86| 3844K| | 4| 0| 4|@@@@ (4) |
87| 3844K| | 4| 0| 4|@@@@ (4) |
88| 3844K| | 4| 0| 4|@@@@ (4) |
89| 3844K| | 4| 0| 4|@@@@ (4) |
90| 3844K| | 4| 0| 4|@@@@ (4) |
91| 3844K| | 4| 0| 4|@@@@ (4) |
92| 3844K| | 4| 0| 4|@@@@ (4) |
93| 3844K| | 4| 0| 4|@@@@ (4) |
94| 3844K| | 4| 0| 4|@@@@ (4) |
95| 3844K| | 4| 0| 4|@@@@ (4) |
96| 3844K| | 4| 0| 4|@@@@ (4) |
97| | | 0| 0| 0| (0) |
98| 3844K| | 4| 0| 4|@@@@ (4) |
99| 3844K| | 4| 0| 4|@@@@ (4) |
100| 3844K| | 4| 0| 4|@@@@ (4) |
101| 3844K| | 4| 0| 4|@@@@ (4) |
102| 3844K| | 4| 0| 4|@@@@ (4) |
103| 3844K| | 4| 0| 4|@@@@ (4) |
104| 3844K| | 4| 0| 4|@@@@ (4) |
105| 1922K| | 2| 0| 2|@@ (2) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 1 | 1 | 1 | # | 1:sqlplus.exe(1)[1],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | 0:sqlplus.exe(0)[1],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] |
| 2 | PX COORDINATOR | | 5 | 4 | | | | | 0:sqlplus.exe(0)[4],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4 | 4 | | | | | 0:P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],sqlplus.exe(0)[0] |
| 4 | SORT AGGREGATE | | 4 | 4 | | | | | 0:P000(0)[1],P001(0)[1],P002(0)[1],P003(0)[1],sqlplus.exe(0)[0] |
| 5 | VIEW | | 4 | 20M | | | | | 0:P000(0)[5035K],P002(0)[5002K],P003(0)[4994K],P001(0)[4969K],sqlplus.exe(0)[0] |
| 6 | UNION-ALL | | 4 | 20M | | | | | 0:P000(0)[5035K],P002(0)[5002K],P003(0)[4994K],P001(0)[4969K],sqlplus.exe(0)[0] |
| 7 | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 8 | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ######## | 1:P001(41)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 9 | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 10 | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ######## | 1:P000(41)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 11 | PX SELECTOR | | 4 | 2000K | | | | | 0:P003(0)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 12 | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 42 | 42 | ######## | 1:P003(42)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 13 | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 14 | TABLE ACCESS FULL| T2 | 4 | 2000K | 2 | 41 | 41 | ######## | 1:P002(41)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 15 | PX SELECTOR | | 4 | 2000K | | | | | 0:P000(0)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 16 | TABLE ACCESS FULL| T2 | 4 | 2000K | 43 | 40 | 40 | ######### | 1:P000(40)[2000K],P001(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 17 | PX SELECTOR | | 4 | 2000K | | | | | 0:P001(0)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 18 | TABLE ACCESS FULL| T2 | 4 | 2000K | 43 | 41 | 41 | ######### | 1:P001(41)[2000K],P000(0)[0],P002(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 19 | PX SELECTOR | | 4 | 2000K | | | | | 0:P002(0)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
|* 20 | TABLE ACCESS FULL| T2 | 4 | 2000K | 43 | 41 | 41 | ######### | 1:P002(41)[2000K],P000(0)[0],P001(0)[0],P003(0)[0],sqlplus.exe(0)[0] |
| 21 | PX SELECTOR | | 4 | 2000K | | | | | 0:P003(0)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
|* 22 | TABLE ACCESS FULL| T2 | 4 | 2000K | 44 | 40 | 40 | ######### | 1:P003(40)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],sqlplus.exe(0)[0] |
| 23 | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P000(0)[534K],P003(0)[501K],P002(0)[497K],P001(0)[468K],sqlplus.exe(0)[0] |
|* 24 | TABLE ACCESS FULL| T_2 | 52 | 2000K | 83 | 11 | 11 | ### | 4:P000(11)[534K],P003(10)[501K],P002(10)[497K],P001(10)[468K],sqlplus.exe(0)[0] |
| 25 | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P002(0)[505K],P000(0)[501K],P001(0)[501K],P003(0)[493K],sqlplus.exe(0)[0] |
|* 26 | TABLE ACCESS FULL| T_2 | 52 | 2000K | 94 | 12 | 11 | ### | 4:P000(11)[501K],P003(11)[493K],P002(10)[505K],P001(10)[501K],sqlplus.exe(0)[0] |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Notice how I managed in this particular case to reduce the execution duration by more than 30 seconds simply by re-arranging, which ensured that the work distribution was optimal all the time.
In the final part of series I'll focus on the runtime behaviour of the concurrent UNION ALL feature when dealing with remote branches.
Subscribe to:
Posts (Atom)
