Traditionally, set operators are processed in a sequential manner.
Individual branches can be processed in serial or parallel, but only one branch
at a time, one branch after another. It is also possible to execute the individual
branches as concurrent,
1)
When at least one branch is local and it is
considered being processed in parallel.
2)
Optimizer feature enable parameter is set to 12
and above.
This behavior is enabled by default in 12c and in 11g should be
enabled explicitly using PQ_CONCURRENT_UNION hint.
Concurrent execution of branches can be identified by row source
operation “PX SELECTOR” from explain plan.
rajesh@PDB1> create table t1 as select * from all_objects;
Table created.
rajesh@PDB1> create table t2 as select * from all_objects;
Table created.
rajesh@PDB1> create table t3 as select * from all_objects;
Table created.
rajesh@PDB1>
rajesh@PDB1> delete from plan_table;
13 rows deleted.
rajesh@PDB1> explain plan for
2 create table t4 as
3 select * from (
4 select * from t1 union all
5 select * from t2 union all
6 select * from t3 ) ;
Explained.
rajesh@PDB1> @xpdisplay
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 402113709
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | CREATE TABLE
STATEMENT | |
268K| 94M| 4635
(1)| 00:00:01 |
| 1 | LOAD AS SELECT | T4 |
| | | |
| 2 | OPTIMIZER STATISTICS GATHERING | |
268K| 94M| 2060
(1)| 00:00:01 |
| 3 | VIEW | |
268K| 94M| 2060
(1)| 00:00:01 |
| 4 | UNION-ALL | |
| | | |
| 5 | TABLE ACCESS FULL | T1 | 89353 |
9M| 418 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 89354 |
9M| 418 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T3 | 89355 |
9M| 418 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
14 rows selected.
rajesh@PDB1> alter table t1 parallel 2;
Table altered.
rajesh@PDB1> delete from plan_table;
8 rows deleted.
rajesh@PDB1> explain plan for
2 create table t4 as
3 select * from (
4 select * from t1 union all
5 select * from t2 union all
6 select * from t3 ) ;
Explained.
rajesh@PDB1> @xpdisplay
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3248033409
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | TQ |IN-OUT|
---------------------------------------------------------------------------------------
| 0 | CREATE TABLE
STATEMENT | |
268K| 94M| |
|
| 1 | LOAD AS SELECT | T4 |
| | |
|
| 2 | PX COORDINATOR | | |
| | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 268K|
94M| Q1,00 | P->S |
| 4 | OPTIMIZER STATISTICS GATHERING | |
268K| 94M| Q1,00 | PCWC |
| 5 | VIEW | |
268K| 94M| Q1,00 | PCWP |
| 6 | UNION-ALL | | |
| Q1,00 | PCWP |
| 7 | PX BLOCK ITERATOR | | 89353 | 9M|
Q1,00 | PCWC |
| 8 | TABLE ACCESS FULL | T1 | 89353 | 9M|
Q1,00 | PCWP |
| 9 | PX
SELECTOR | | |
| Q1,00 | PCWP |
| 10 | TABLE ACCESS FULL | T2 | 89354 | 9M|
Q1,00 | PCWP |
| 11 | PX
SELECTOR | | | |
Q1,00 | PCWP |
| 12 | TABLE ACCESS FULL | T3 | 89355 | 9M| Q1,00 | PCWP |
---------------------------------------------------------------------------------------Note
-----
- Degree of Parallelism
is 2 because of table property
23 rows selected.
rajesh@PDB1>
Hi Rajesh, thanks for your post. I have some requirement like this. When I am seeing the explain plan it is showing as if it is running parallely. BUt the performance is not as expected.
ReplyDeleteI am not getting the point which mentioned in some other blogs, that we need to mention the query block name in the hint. Have you faced this type of issue? if yes, how you solved it.
I am not aware how to mention the query block name. Please suggest.
Thanks,
Mithun
....
DeleteI am not aware how to mention the query block name. Please suggest
....
you can get the query block name using QB_NAME hint, a quick demo of that is below.
demo@ORA11G> explain plan for
2 select e1.ename, e3.max_sal
3 from emp e1, ( select /*+ qb_name(q3) */ deptno,max(sal) max_sal
4 from emp e2
5 group by deptno ) e3
6 where e1.deptno = e3.deptno;
Explained.
demo@ORA11G> select * from table( dbms_xplan.display(format=>' +alias'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 525 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 15 | 525 | 7 (15)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - Q3 / E3@SEL$1
3 - Q3
4 - Q3 / E2@Q3
5 - SEL$1 / E1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPTNO"="E3"."DEPTNO")
26 rows selected.
demo@ORA11G>