Oracle 19c & 12c Create Index Parallel Hint

Question

When creating an index the behavior of the PARALLEL hint does not seem to operate as expected in both 19c and 12c.

In all 19c cases the hint was Unused, but in two of the cases the note actually says automatic DOP was used and in those cases it actually processed in parallel.

Hint                       DOP  NOTE
-------------------------  ---  ---------------------------------------------------------------------------
/*+ PARALLEL (16) */         1
/*+ PARALLEL (4) */          1
/*+ PARALLEL */             16  automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
/*+ PARALLEL (AUTO) */      16  automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
/*+ PARALLEL (DEFAULT) */    1
/*+ PARALLEL (MANUAL) */     1

From Oracle 19c VLDB and Partitioning Guide - Controlling Automatic Degree of Parallelism:

"MANUAL This setting disables Auto DOP,...With the default setting of MANUAL for PARALLEL_DEGREE_POLICY, the system only uses parallel execution when a DOP has been explicitly set on an object or if a parallel hint is specified in the SQL statement. The DOP used is exactly what was specified."

The DOP was not explicitly set on the table for which this index was created.

Using Oracle 19c with the following initialization parameters.

set serveroutput on;
show parameters parallel;
---------------------------------------------------
awr_pdb_max_parallel_slaves          integer  10
containers_parallel_degree           integer  65535
fast_start_parallel_rollback         string   LOW
max_datapump_parallel_per_job        string   50
optimizer_ignore_parallel_hints      boolean  FALSE
parallel_adaptive_multi_user         boolean  FALSE
parallel_degree_limit                string   CPU
parallel_degree_policy               string   MANUAL
parallel_execution_message_size      integer  16384
parallel_force_local                 boolean  FALSE
parallel_instance_group              string  
parallel_max_servers                 integer  320
parallel_min_degree                  string   1
parallel_min_percent                 integer  0
parallel_min_servers                 integer  32
parallel_min_time_threshold          string   AUTO
parallel_servers_target              integer  128
parallel_threads_per_cpu             integer  2
recovery_parallelism                 integer  0
...
show parameters cpu_count;
show parameters parallel_threads_per_cpu;
---------------------------------------------------
cpu_count                            integer  8
parallel_threads_per_cpu             integer  2

The index had the following form:

CREATE /*+ [PARALLEL HINT] */ INDEX OWN.IX ON
OWN.T (some_char, some_number, some_date1, some_date2)
LOCAL NOLOGGING TABLESPACE INDX COMPRESS

My session was the only active session and the results were repeatable indicating system load was not a factor. I have explain plans if needed, but this post is long enough as it is.

I attempted a similar task in Oracle 12c (but the index wasn't local and the underlying table wasn't partitioned) with the following results and initialization parameters.

Hint                       DOP   Note
-------------------------  ----- --------------------------------------------------------------------------------
/*+ PARALLEL (16) */       16/32 Degree of Parallelism is 16 because of hint
/*+ PARALLEL (4) */          4/8 Degree of Parallelism is 4 because of hint
/*+ PARALLEL */              2/4 automatic DOP: Computed Degree of Parallelism is 2
/*+ PARALLEL (AUTO) */         1 automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
/*+ PARALLEL (DEFAULT) */      1
/*+ PARALLEL (MANUAL) */       1

Using Oracle 12c with the following initialization parameters.

show parameters parallel;
------------------------------------------------------
fast_start_parallel_rollback         string   LOW
parallel_adaptive_multi_user         boolean  TRUE
parallel_automatic_tuning            boolean  FALSE
parallel_degree_level                integer  100
parallel_degree_limit                string   CPU
parallel_degree_policy               string   MANUAL
parallel_execution_message_size      integer  16384
parallel_force_local                 boolean  FALSE
parallel_instance_group              string  
parallel_io_cap_enabled              boolean  FALSE
parallel_max_servers                 integer  320
parallel_min_percent                 integer  0
parallel_min_servers                 integer  32
parallel_min_time_threshold          string   AUTO
parallel_server                      boolean  FALSE
parallel_server_instances            integer  1
parallel_servers_target              integer  128
parallel_threads_per_cpu             integer  2
recovery_parallelism                 integer  0
...
show parameters cpu_count;
show parameters parallel_threads_per_cpu;
------------------------------------------------------
cpu_count                            integer  8
parallel_threads_per_cpu             integer  2

In 19c:

1. Why was DOP 1 for the parallel hints with /*+ PARALLEL (16) */, /*+ PARALLEL (4) */, and /*+ PARALLEL (DEFAULT) */ cases listed above? Expected 16, 4, and 16 (=8*2), respectively.

2. For the two cases when the DOP was 16, why does the explain plan say the hint was Unused when auto DOP did calculate a DOP > 1?

In 12c

3. Why was DOP 1 for the parallel hints with /*+ PARALLEL (DEFAULT) */? Expected 16 (=8*2).

19c vs. 12c

4. Why does 19c not produce double the DOP of parallel execution servers like is done in 12c? (In 12c, when auto DOP is 4 we get 8 parallel executions servers, when DOP is 16 we get 32, ..., but this does not occur in 19c.)

Answer 1

In Oracle 12c introduced a hint for parallel dml, ENABLE_PARALLEL_DML ,
which you can use directly inside the dml sql statement.

/*+ parallel(16) enable_parallel_dml */