Does parallel_degree_limit work with parallel_degree_policy=manual?

The Oracle 11g parameter parallel_degree_limit is designed to put a cap on the maximum DOP for a statement.

The Oracle Database Reference 11g Release 2 (11.2) says this:

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

But that’s not entirely correct because it turns out you can enable auto DOP via a hint. The PARALLEL hint without a valid object on which to act will enable auto DOP for the statement. Here is an example:

SYS@DEMO1> @parms
Enter value for parameter: parallel_degree
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
parallel_degree_limit                              CPU                                                                    FALSE    TRUE       TRUE
parallel_degree_policy                             MANUAL                                                                 FALSE    TRUE       TRUE
 
Elapsed: 00:00:00.00
SYS@DEMO1> select /*+ parallel */ count(*) from eo00.SALES_1M2;
 
  COUNT(*)
----------
   1000000
 
Elapsed: 00:00:00.02
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aszs6rg3fttrt, child number 2
-------------------------------------
select /*+ parallel */ count(*) from eo00.SALES_1M2
 
Plan hash value: 3155295854
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |       |    25 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |           |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |           |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000  |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |           |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |           |   982K|    25   (4)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SALES_1M2 |   982K|    25   (4)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 7
 
 
28 rows selected.
 
Elapsed: 00:00:00.02

So as you can see, the hint enabled auto DOP, even though the parallel_degree_policy is set to manual. So let’s see if the parallel_degree_limit will kick in for such a case.

SYS@DEMO1> alter session set parallel_degree_limit=4;
 
Session altered.
 
Elapsed: 00:00:00.00
SYS@DEMO1> select /*+ parallel */ count(*) from eo00.SALES_1M2;
 
  COUNT(*)
----------
   1000000
 
Elapsed: 00:00:00.02
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aszs6rg3fttrt, child number 1
-------------------------------------
select /*+ parallel */ count(*) from eo00.SALES_1M2
 
Plan hash value: 3155295854
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |       |    44 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |           |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |           |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000  |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |           |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |           |   982K|    44   (3)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SALES_1M2 |   982K|    44   (3)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
 
 
28 rows selected.
 
Elapsed: 00:00:00.02

Yes it does. So what does it mean? Well for one thing the documentation (and some presentations I have seen recently) are slightly wrong. But the real moral of the story is that just because you have parallel_degree_policy set to manual, doesn’t mean you are not using auto DOP.

Does this mean that the other features enabled by auto DOP (parallel statement queuing and in-memory parallel) will kick in on these kinds of statements as well? I’ll leave that as an exercise for the reader.

10 Comments

  1. Uwe Hesse says:

    Hi Kerry, that is indeed interesting!
    Just did a quick check on 11.2.0.3 with a very small table. (create table t as select * from dual)

    It looks like the hint /*+ parallel */ does NOT the same as parallel_degree_policy=auto.

    alter session set parallel_degree_policy=manual;
    select /*+ parallel */ * from t;
    - automatic DOP: Computed Degree of Parallelism is 2

    Against:
    alter session set parallel_degree_policy=auto;
    select * from t;
    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

    That is somewhat in line with the DOC:
    http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50801
    “PARALLEL: The statement always is run parallel, and the database computes the degree of parallelism, which can be 2 or greater.”

    But still, it is a bit weird.

    Kind regards
    Uwe

  2. Hi Kerry,

    what I found is both object _and_ DOP must be missing from the hint in order for Auto DOP to kick in.

    For example this will be manual DOP with degree 16 even though it does not specify an object (and of course it will ignore parallel_degree_limit too):

    select /*+ parallel(16) */ count(*) from eo00.SALES_1M2;

  3. Weidong Zhou says:

    Interesting, I verified and got the same result.

Leave a Reply