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 property
So 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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.