Concurrent Execution of UNION and UNION ALL Branches!

Hi there,

 

I got an interesting question during a SQL performance and tuning training session from one student.

In this simple query

select name from A
union all
select name from b

Is it possible to make it in parallel? I mean one general consumer, the union all and two slaves consumers with n workers on each tables.

Here is a small test
drop table T1 purge;
drop table T2 purge;
drop table T3 purge;

create table T1 as select * from dba_objects;
insert into T1 select * from T1;
insert into T1 select * from T1;
insert into T1 select * from T1;
commit;

create table T2 as select * from T1;
insert into T2 select * from dba_objects;
insert into T2 select * from dba_objects;
commit;
create table T3 as select * from T2;
insert into T3 select * from dba_objects;
insert into T3 select * from dba_objects;
insert into T3 select * from dba_objects;
commit;

select /*+ parallel (V 4) */ count (Object_name)
from
(
select object_name from T1 where object_type = ‘INDEX’
union all
select object_name from T2 where object_type = ‘SYNONYM’
union all
select object_name from T3 where object_type = ‘JAVA CLASS’
) V;
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL));

COUNT(OBJECT_NAME)
——————
624749

PLAN_TABLE_OUTPUT
————————————-
SQL_ID 4kn3rjj9q2u9p, child number 0
————————————-
select /*+ parallel (V 4) */ count (Object_name) from ( select
object_name from T1 where object_type = ‘INDEX’ union all select
object_name from T2 where object_type = ‘SYNONYM’ union all select
object_name from T3 where object_type = ‘JAVA CLASS’ ) V

Plan hash value: 4046276374

——————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
——————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 2620 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 66 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 66 | | | Q1,00 | PCWP | |
| 5 | VIEW | | 576K| 36M| 2620 (1)| 00:00:32 | Q1,00 | PCWP | |
| 6 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 54762 | 4117K| 688 (1)| 00:00:09 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| T1 | 54762 | 4117K| 688 (1)| 00:00:09 | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 252K| 18M| 850 (1)| 00:00:11 | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL| T2 | 252K| 18M| 850 (1)| 00:00:11 | Q1,00 | PCWP | |
| 11 | PX BLOCK ITERATOR | | 269K| 19M| 1082 (1)| 00:00:13 | Q1,00 | PCWC | |
|* 12 | TABLE ACCESS FULL| T3 | 269K| 19M| 1082 (1)| 00:00:13 | Q1,00 | PCWP | |
——————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

8 – access(:Z>=:Z AND :Z<=:Z)
filter(“OBJECT_TYPE”=’INDEX’)
10 – access(:Z>=:Z AND :Z<=:Z)
filter(“OBJECT_TYPE”=’SYNONYM’)
12 – access(:Z>=:Z AND :Z<=:Z)
filter(“OBJECT_TYPE”=’JAVA CLASS’)

Note
—–
– dynamic sampling used for this statement (level=2)

41 rows selected

We can see that we have only one consumer!

So T1 is processed first, then T2 and the T3. THe result is given to the consumer.

Technically, I can understand the potential complexity to synchronize the stuff when there is a join conditions between two tables but I don’t see the complexity to do the stuff in parallel when things are unrelated like the given example . This would help a lot some queries I think.

An example with financial stuff
select /*+ parallel (V) */ amountA, amountB, amountC
from
(
select amountA, 0 amountB, 0 amountC from T1 where account=154682
union all
select 0 amountA, amountB, 0 amountC from T2 where account=154682
union all
select 0 amountA, 0 amountB, amountC from T3 where account=154682
) V;

I woul call that “async parallel join” 🙂

 

Ok, thanks to my Colleague @ludovicocaldara I saw that this feature is implemented in 12c and it is named “Concurrent Execution of UNION and UNION ALL Branches”

So I have tested the stuff on 12c and I does not work as I expect.

Here is the execution plan I have

COUNT(OBJECT_NAME)
——————
811080

PLAN_TABLE_OUTPUT
——————————————
SQL_ID 2uqs8068u55wc, child number 0
————————————-
select /*+ parallel (V 4) */ count (Object_name) from ( select
object_name from T1 where object_type = ‘INDEX’ union all select
object_name from T2 where object_type = ‘SYNONYM’ union all select
object_name from T3 where object_type = ‘JAVA CLASS’ ) V

Plan hash value: 4046276374

——————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
——————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 3581 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 66 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 66 | | | Q1,00 | PCWP | |
| 5 | VIEW | | 780K| 49M| 3581 (1)| 00:00:01 | Q1,00 | PCWP | |
| 6 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 38268 | 2952K| 923 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| T1 | 38268 | 2952K| 923 (1)| 00:00:01 | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 394K| 29M| 1156 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL| T2 | 394K| 29M| 1156 (1)| 00:00:01 | Q1,00 | PCWP | |
| 11 | PX BLOCK ITERATOR | | 347K| 26M| 1502 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 12 | TABLE ACCESS FULL| T3 | 347K| 26M| 1502 (1)| 00:00:01 | Q1,00 | PCWP | |
——————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

8 – access(:Z>=:Z AND :Z<=:Z)
filter(“OBJECT_TYPE”=’INDEX’)
10 – access(:Z>=:Z AND :Z<=:Z)
filter(“OBJECT_TYPE”=’SYNONYM’)
12 – access(:Z>=:Z AND :Z<=:Z)
filter(“OBJECT_TYPE”=’JAVA CLASS’)

Note
—–
– dynamic statistics used: dynamic sampling (level=2)

41 rows selected

Nothing is happening…

So I try to test this case

alter table T1 parallel 4;
select count (Object_name)
from
(
select object_name from T1 where object_type = ‘INDEX’
union all
select object_name from T2 where object_type = ‘SYNONYM’
union all
select object_name from T3 where object_type = ‘JAVA CLASS’
) V;
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL));

COUNT(OBJECT_NAME)
——————
811080

PLAN_TABLE_OUTPUT
————————————–
SQL_ID 4c0jygzb93tbc, child number 0
————————————-
select count (Object_name) from ( select object_name from T1 where
object_type = ‘INDEX’ union all select object_name from T2 where
object_type = ‘SYNONYM’ union all select object_name from T3 where
object_type = ‘JAVA CLASS’ ) V

Plan hash value: 630922537

——————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
——————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 10499 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 66 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 66 | | | Q1,00 | PCWP | |
| 5 | VIEW | | 780K| 49M| 10499 (1)| 00:00:01 | Q1,00 | PCWP | |
| 6 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 38268 | 2952K| 923 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| T1 | 38268 | 2952K| 923 (1)| 00:00:01 | Q1,00 | PCWP | |
| 9 | PX SELECTOR | | | | | | Q1,00 | PCWP | |
|* 10 | TABLE ACCESS FULL| T2 | 394K| 29M| 4164 (1)| 00:00:01 | Q1,00 | PCWP | |
| 11 | PX SELECTOR | | | | | | Q1,00 | PCWP | |
|* 12 | TABLE ACCESS FULL| T3 | 347K| 26M| 5412 (1)| 00:00:01 | Q1,00 | PCWP | |
——————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

8 – access(:Z>=:Z AND :Z<=:Z)
filter(“OBJECT_TYPE”=’INDEX’)
10 – filter(“OBJECT_TYPE”=’SYNONYM’)
12 – filter(“OBJECT_TYPE”=’JAVA CLASS’)

Note
—–
– dynamic statistics used: dynamic sampling (level=2)

39 rows selected

Now it seems to work.

Why it’s not working by specifying the degree at the main level?

Cheers

jko

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

4 thoughts on “Concurrent Execution of UNION and UNION ALL Branches!

Leave a Reply

Your email address will not be published. Required fields are marked *