Team,
was reading about this feature Parallel recursive WITH clause enhancements in 12.2 database from the below link:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6 from 12.2 we could able to see that the "recursive" member in the Recursive with clause statements got parallelized (along with "Inmemory cursor duration temp table" optimization) with top level parallel hints.
demo@PDB1> with r (empno,ename,mgr,l) as
2 (
3 select empno,ename,mgr,1 l
4 from emp
5 where mgr is null
6 union all
7 select e.empno,e.ename,e.mgr,r.l+1
8 from emp e, r
9 where r.empno = e.mgr )
10 select /*+ parallel */ * from r ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3854991928
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 138 | 8 (0)| 00:00:01 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6699_3A266F | | | | | | | |
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 14 | 2 (0)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
| 6 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6699_3A266F | | | | | Q2,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1 | 14 | 2 (0)| 00:00:01 | Q2,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | EMP | 1 | 14 | 2 (0)| 00:00:01 | Q2,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 32880 | 4 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 11 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6699_3A266F | | | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 2 | 32880 | 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 13 | BUFFER SORT (REUSE) | | | | | | Q1,00 | PCWP | |
|* 14 | TABLE ACCESS FULL | EMP | 13 | 182 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6699_3A266F | | | | | Q1,00 | PCWP | |
| 17 | PX COORDINATOR | | | | | | | | |
| 18 | PX SEND QC (RANDOM) | :TQ30000 | 3 | 138 | 2 (0)| 00:00:01 | Q3,00 | P->S | QC (RAND) |
| 19 | VIEW | | 3 | 138 | 2 (0)| 00:00:01 | Q3,00 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 3 | 32892 | 2 (0)| 00:00:01 | Q3,00 | PCWC | |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6699_3A266F | 3 | 32892 | 2 (0)| 00:00:01 | Q3,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("MGR" IS NULL)
12 - access("R"."EMPNO"="E"."MGR")
14 - filter("E"."MGR" IS NOT NULL)
16 - filter("R"."INTERNAL_ITERS$"=LEVEL)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
However in 12.1 only the "achor member" portion got parallelized, but not the recursive members with top level parallel hints.
v_rjeyabal@ORA12.1> set autotrace traceonly explain
v_rjeyabal@ORA12.1> with r(empno,ename,mgr,l) as (
2 select empno,ename,mgr,1 l
3 from emp
4 where mgr is null
5 union all
6 select e.empno,e.ename,e.mgr,r.l+1
7 from emp e, r
8 where r.empno = e.mgr )
9 select /*+ parallel */ * from r ;
Execution Plan
----------------------------------------------------------
Plan hash value: 627582838
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 138 | 6 (0)| 00:00:01 | | | |
| 1 | VIEW | | 3 | 138 | 6 (0)| 00:00:01 | | | |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 14 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 1 | 14 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | EMP | 1 | 14 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | HASH JOIN | | 2 | 80 | 4 (0)| 00:00:01 | | | |
| 8 | RECURSIVE WITH PUMP | | | | | | | | |
|* 9 | TABLE ACCESS FULL | EMP | 13 | 182 | 2 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("MGR" IS NULL)
7 - access("R"."EMPNO"="E"."MGR")
9 - filter("E"."MGR" IS NOT NULL)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
v_rjeyabal@ORA12.1> set autotrace off
However when DOP is provided in the parallell hints, dont see the "recursive member" getting parallelized (only the "anchor member" are getting parallelized) in 12.2 database.
is this an expected behavior kindly advice, dont see that mentioned in the above docs link
demo@PDB1> with r (empno,ename,mgr,l) as
2 (
3 select empno,ename,mgr,1 l
4 from emp
5 where mgr is null
6 union all
7 select e.empno,e.ename,e.mgr,r.l+1
8 from emp e, r
9 where r.empno = e.mgr )
10 select /*+ parallel(r,2) */ * from r ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4144284481
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 138 | 6 (0)| 00:00:01 | | | |
| 1 | VIEW | | 3 | 138 | 6 (0)| 00:00:01 | | | |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 14 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 1 | 14 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | EMP | 1 | 14 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | HASH JOIN | | 2 | 80 | 4 (0)| 00:00:01 | | | |
| 8 | RECURSIVE WITH PUMP | | | | | | | | |
| 9 | BUFFER SORT (REUSE) | | | | | | | | |
|* 10 | TABLE ACCESS FULL | EMP | 13 | 182 | 2 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("MGR" IS NULL)
7 - access("R"."EMPNO"="E"."MGR")
10 - filter("E"."MGR" IS NOT NULL)
Note
-----
- Degree of Parallelism is 2 because of table property
I'll check with the PM to see why this anomaly exists, but you can workaround with an unaliased hint,
SQL> create table t as
2 select rownum empno,
3 case when mod(rownum,100000)=0 then null else trunc(rownum/100) end mgr,
4 rpad('x',100) ename
5 from dual
6 connect by level <= 1000000;
Table created.
SQL> set lines 200
SQL> set autotrace traceonly explain
SQL> with r (empno,ename,mgr,l) as
2 (
3 select empno,ename,mgr,1 l
4 from t
5 where mgr is null
6 union all
7 select e.empno,e.ename,e.mgr,r.l+1
8 from t e, r
9 where r.empno = e.mgr )
10 select /*+ parallel(r,2) */ * from r ;
Execution Plan
----------------------------------------------------------
Plan hash value: 663831347
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999T| 80P| 2226M(100)| 24:09:38 | | | |
| 1 | VIEW | | 999T| 80P| 2226M(100)| 24:09:38 | | | |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 1100 | 2456 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 10 | 1100 | 2456 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | T | 10 | 1100 | 2456 (1)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | HASH JOIN | | 999T| 120P| 2226M(100)| 24:09:38 | | | |
| 8 | BUFFER SORT (REUSE) | | | | | | | | |
| 9 | TABLE ACCESS FULL | T | 1000K| 104M| 2456 (1)| 00:00:01 | | | |
| 10 | RECURSIVE WITH PUMP | | | | | | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("MGR" IS NULL)
7 - access("R"."EMPNO"="E"."MGR")
Note
-----
- Degree of Parallelism is 2 because of table property
SQL>
SQL> with r (empno,ename,mgr,l) as
2 (
3 select empno,ename,mgr,1 l
4 from t
5 where mgr is null
6 union all
7 select e.empno,e.ename,e.mgr,r.l+1
8 from t e, r
9 where r.empno = e.mgr )
10 select /*+ parallel(2) */ * from r ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2067424220
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999T| 80P| 337T (1)|999:59:59 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6D0F_BEB78F09 | | | | | | | |
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ20000 | 10 | 1100 | 2456 (1)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
| 6 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6D0F_BEB78F09 | | | | | Q2,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 10 | 1100 | 2456 (1)| 00:00:01 | Q2,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | T | 10 | 1100 | 2456 (1)| 00:00:01 | Q2,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10002 | 999T| 14E| 2226M(100)| 24:09:38 | Q1,02 | P->S | QC (RAND) |
| 11 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6D0F_BEB78F09 | | | | | Q1,02 | PCWP | |
|* 12 | HASH JOIN | | 999T| 14E| 2226M(100)| 24:09:38 | Q1,02 | PCWP | |
| 13 | BUFFER SORT (REUSE) | | | | | | Q1,02 | PCWP | |
| 14 | PX RECEIVE | | 1000K| 104M| 2456 (1)| 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10000 | 1000K| 104M| 2456 (1)| 00:00:01 | Q1,00 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 1000K| 104M| 2456 (1)| 00:00:01 | Q1,00 | PCWC | |
| 17 | TABLE ACCESS FULL | T | 1000K| 104M| 2456 (1)| 00:00:01 | Q1,00 | PCWP | |
| 18 | PX RECEIVE | | | | | | Q1,02 | PCWP | |
| 19 | PX SEND HASH | :TQ10001 | | | | | Q1,01 | P->P | HASH |
| 20 | PX BLOCK ITERATOR | | | | | | Q1,01 | PCWC | |
|* 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D0F_BEB78F09 | | | | | Q1,01 | PCWP | |
| 22 | PX COORDINATOR | | | | | | | | |
| 23 | PX SEND QC (RANDOM) | :TQ30000 | 999T| 80P| 337T (1)|999:59:59 | Q3,00 | P->S | QC (RAND) |
| 24 | VIEW | | 999T| 80P| 337T (1)|999:59:59 | Q3,00 | PCWP | |
| 25 | PX BLOCK ITERATOR | | 999T| 14E| 337T (1)|999:59:59 | Q3,00 | PCWC | |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D0F_BEB78F09 | 999T| 14E| 337T (1)|999:59:59 | Q3,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("MGR" IS NULL)
12 - access("R"."EMPNO"="E"."MGR")
21 - filter("R"."INTERNAL_ITERS$"=LEVEL)
Note
-----
- Degree of Parallelism is 2 because of hint
SQL>
SQL> set autotrace off
SQL>