Little things worth knowing: auto-DOP or hint-which one takes precedence?

This is just another short post about one of the little things worth knowing. Assume you are on 12.1.0.2 for example and you want to know if parallel_degree_limit reliably keeps the degree of parallelism at bay, for all sessions and queries across the board. After all, every machine has finite CPU (and other) resources that shouldn’t be exhausted. Setting parallel_degree_limit of course only has an effect if you set parallel_degree_policy to limited, auto or adaptive. The latter is the Big Knob to enable Automatic Degree Of Parallelism (DOP), and the post assumes that Auto DOP is enabled for the session. In this configuration, what happens to hinted statements?

Documentation

The documentation (12c Reference Guide, section on parallel_degree_limit) states that you can provide an integer in addition to “CPU” and “IO”:

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 ADAPTIVE, AUTO, or LIMITED.

Without further context this may sound a bit ambiguous-does enabling Auto DOP plus setting parallel_degree_limit to 8 ensure no query can exceed that DOP? And does that setting actually stop users from going over and above that value?

Testing

I am using a 12.1.0.2 RAC database for the test. I would like to limit the maximum DOP of a given statement to 8. Here are my settings for my lab system (emphasis on lab):

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      8
parallel_degree_policy               string      AUTO
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     TRUE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     128
parallel_min_percent                 integer     0
parallel_min_servers                 integer     16
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_servers_target              integer     128
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

Now I need a table to query – after looking around in my demo-schema I found T1 which looks like a good candidate.

SQL> select table_name,num_rows,partitioned,compression,degree from tabs where table_name = 'T1';

TABLE_NAME                       NUM_ROWS PAR COMPRESS DEGREE
------------------------------ ---------- --- -------- ----------
T1                               32000000 NO  DISABLED          1

Unlike when setting parallel_degree_policy to limited I don’t need to worry about decorating the table with a default DOP. With parallel_degree_policy set to auto a parallel plan can be chosen even with the DOP on the table set to 1, provided the optimiser reckons that statement execution exceeds parallel_min_time_threshold. In my case I left its value at its default, which is 10 seconds. When querying the table I do not have to specify a hint to enable PX, or define PX on the session level as you can see in this example:

SQL> select count(*) from T1;

  COUNT(*)
----------
  32000000

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  byb4cbw4vy1cw, child number 1
-------------------------------------
select count(*) from T1

Plan hash value: 2755517917

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |       |   200K(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         |                |    32M|   200K  (1)| 00:00:04 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| T1             |    32M|   200K  (1)| 00:00:04 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

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

   6 - storage(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

The important bit here is the information in the Note section: Oracle computed the DOP to be 8 because of the degree limit. Exactly what I wanted.

Now what if I use the hint?

No changes to the setup, this is the next statement I typed in:

SQL> select /*+ parallel(32) */ count(*) from T1;


  COUNT(*)
----------
  32000000

SQL> SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9d9aha2bdd5zc, child number 0
-------------------------------------
select /*+ parallel(32) */ count(*) from T1

Plan hash value: 2755517917

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |       | 50217 (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         |                |    32M| 50217   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| T1             |    32M| 50217   (1)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

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

   6 - storage(:Z>=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 32 because of hint

So based on this experiment it appears as if the hint took precedence over parallel_degree_limit in a session that had Auto DOP enabled. That doesn’t mean the DBA is out of luck, other tools come to mind to limit the DOP such as Database Resource Manager (DBRM). I have written about using DBRM to limit the DOP for all users of a consumer group some time ago. If you read that post make sure you look at Randolf’s comments.