The Oracle hint PARALLEL (AUTO) seems to be preventing parallel execution. The following is on a 12c development server, but we have seen similar behavior on a 19c server. PARALLEL yields parallel execution, but PARALLEL (AUTO) does not. Why not?
drop table ParallelAuto;
create table ParallelAuto as
select level id from dual connect by level <= 1
;
select pdml_enabled, pdml_status, pddl_status, pq_status
from v$session
where sid = (select sid from v$mystat where rownum = 1)
;
+--------------+-------------+-------------+-----------+ | PDML_ENABLED | PDML_STATUS | PDDL_STATUS | PQ_STATUS | +--------------+-------------+-------------+-----------+ | NO | DISABLED | ENABLED | ENABLED | +--------------+-------------+-------------+-----------+
alter session ENABLE PARALLEL DML;
select pdml_enabled, pdml_status, pddl_status, pq_status
from v$session
where sid = (select sid from v$mystat where rownum = 1)
;
+--------------+-------------+-------------+-----------+ | PDML_ENABLED | PDML_STATUS | PDDL_STATUS | PQ_STATUS | +--------------+-------------+-------------+-----------+ | YES | ENABLED | ENABLED | ENABLED | +--------------+-------------+-------------+-----------+
PDML is enabled.
The following are %parallel% parameters for reference.
select name, value, isdefault, ismodified, isadjusted
from v$parameter
where name like '%parallel%'
;
+---------------------------------+--------+-----------+------------+------------+ | NAME | VALUE | ISDEFAULT | ISMODIFIED | ISADJUSTED | +---------------------------------+--------+-----------+------------+------------+ | parallel_server | FALSE | TRUE | FALSE | FALSE | | parallel_server_instances | 1 | TRUE | FALSE | FALSE | | recovery_parallelism | 0 | TRUE | FALSE | FALSE | | fast_start_parallel_rollback | LOW | TRUE | FALSE | FALSE | | parallel_min_percent | 0 | TRUE | FALSE | FALSE | | parallel_min_servers | 32 | TRUE | FALSE | FALSE | | parallel_max_servers | 320 | TRUE | FALSE | FALSE | | parallel_instance_group | | TRUE | FALSE | FALSE | | parallel_execution_message_size | 16384 | TRUE | FALSE | FALSE | | parallel_degree_policy | MANUAL | TRUE | FALSE | FALSE | | parallel_adaptive_multi_user | TRUE | TRUE | FALSE | FALSE | | parallel_threads_per_cpu | 2 | TRUE | FALSE | FALSE | | parallel_automatic_tuning | FALSE | TRUE | FALSE | FALSE | | parallel_io_cap_enabled | FALSE | TRUE | FALSE | FALSE | | parallel_min_time_threshold | AUTO | TRUE | FALSE | FALSE | | parallel_degree_limit | CPU | TRUE | FALSE | FALSE | | parallel_force_local | FALSE | TRUE | FALSE | FALSE | | parallel_servers_target | 128 | TRUE | FALSE | FALSE | | parallel_degree_level | 100 | TRUE | FALSE | FALSE | +---------------------------------+--------+-----------+------------+------------+
Explaining this UPDATE DML shows we do indeed get a parallel execution.
update /*+ ENABLE_PARALLEL_DML PARALLEL */ ParallelAuto set id = 2;
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 13 | 3 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | UPDATE | PARALLELAUTO | | | | | Q1,00 | PCWP | | | 4 | PX BLOCK ITERATOR | | 1 | 13 | 3 (0)| 00:00:01 | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL| PARALLELAUTO | 1 | 13 | 3 (0)| 00:00:01 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------- ... Note - dynamic statistics used: dynamic sampling (level=2) - automatic DOP: Computed Degree of Parallelism is 2
However, when using the PARALLEL (AUTO) hint we no longer receive the parallel execution.
update /*+ ENABLE_PARALLEL_DML PARALLEL (AUTO) */ ParallelAuto set id = 3;
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | | 1 | UPDATE | PARALLELAUTO | | | | | | 2 | TABLE ACCESS FULL| PARALLELAUTO | 1 | 13 | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------------- ... Note - dynamic statistics used: dynamic sampling (level=2) - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold - PDML disabled because object is not decorated with parallel clause
Just to confirm it is not the PARALLEL_DEGREE_POLICY let's change it to not be MANUAL.
alter session set parallel_degree_policy = AUTO;
select name, value, isdefault, ismodified, isadjusted
from v$parameter
where name like 'parallel_degree_policy'
;
+---------------------------------+--------+-----------+------------+------------+ | NAME | VALUE | ISDEFAULT | ISMODIFIED | ISADJUSTED | +---------------------------------+--------+-----------+------------+------------+ | parallel_degree_policy | AUTO | TRUE | MODIFIED | FALSE | +---------------------------------+--------+-----------+------------+------------+
update /*+ ENABLE_PARALLEL_DML PARALLEL */ ParallelAuto set id = 3;
Note - dynamic statistics used: dynamic sampling (level=2) - automatic DOP: Computed Degree of Parallelism is 2
update /*+ ENABLE_PARALLEL_DML PARALLEL (AUTO) */ ParallelAuto set id = 3;
Note - dynamic statistics used: dynamic sampling (level=2) - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold - PDML disabled because object is not decorated with parallel clause
Same results.
Even when we remove the PDML aspect from the statement, a surprising result is occurring for SELECT (Parallel Query - PQ) as well.
select /*+ PARALLEL */ * from ParallelAuto;
Note - dynamic statistics used: dynamic sampling (level=2) - automatic DOP: Computed Degree of Parallelism is 2
select /*+ PARALLEL (AUTO) */ * from ParallelAuto;
Note - dynamic statistics used: dynamic sampling (level=2) - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
There is very limited activity on this non-production server.
This behavior of PARALLEL (AUTO) seems to contradict the documentation (referenced below).
Oracle SQL Database Reference | PARALLEL_DEGREE_POLICY
Note:Automatic degree of parallelism will be enabled regardless of the value of PARALLEL_DEGREE_POLICY if a PARALLEL hint is used at the SQL statement level.
Oracle SQL Reference Guide | Comments | Hints
This hint overrides the value of the PARALLEL_DEGREE_POLICY initialization parameter. It applies to the SELECT, INSERT, MERGE, UPDATE, and DELETE portions of a statement, as well as to the table scan portion. If any parallel restrictions are violated, then the hint is ignored.
12c
PARALLEL: The statement always is run parallel, and the database computes the degree of parallelism, which can be 2 or greater.
PARALLEL (AUTO): The database computes the degree of parallelism, which can be 1 or greater. If the computed degree of parallelism is 1, then the statement runs serially.
PARALLEL (MANUAL): The optimizer is forced to use the parallel settings of the objects in the statement.
19c/21c
PARALLEL: The statement results in a degree of parallelism equal to or greater than the computed degree of parallelism, except when parallelism is not feasible for the lowest cost plan. When parallelism is is not feasible, the statement runs serially.
PARALLEL (AUTO): The statement results in a degree of parallelism that is equal to or greater than the computed degree of parallelism, except when parallelism is not feasible for the lowest cost plan. When parallelism is is not feasible, the statement runs serially.
PARALLEL (MANUAL): The optimizer is forced to use the parallel settings of the objects in the statement.
What is going on here? Thanks in advance.
PARALLEL(AUTO) will not enable parallelism if the estimated run time is less than the PARALLEL_MIN_TIME_THRESHOLD, which defaults to 10 seconds (which is confusingly also called "AUTO"). This behavior is probably a good thing because parallelism adds a lot of overhead, and parallelism may unfairly use a huge amount of resources to make a fast statement only slightly faster.
You can test this behavior by creating a fake number of rows and making the optimizer think the SQL will take a long time:
begin
dbms_stats.set_table_stats(user, 'PARALLELAUTO', numrows => 99999999999);
end;
/
Alternatively, you can modify PARALLEL_MIN_TIME_THRESHOLD at the session or system level. Although since your test table has only one row, there may not be any value low enough to trigger parallelism without also creating a fake row count.
alter session set parallel_min_time_threshold = 1;