Skip to Main Content
  • Questions
  • Parallel recursive WITH Enhancements

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: April 03, 2019 - 11:02 am UTC

Last updated: April 09, 2019 - 2:14 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

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

and Connor said...

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>





Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.