Reasons for Oracle Automatic Degree of Parallelism?

Question

I apologise in advance as I am new to this area and that I may not be providing all the required information up front but here goes.

I am on a two node Oracle RAC.
Optimizer stats have been recently collected and are very accurate.
I have a query where 3 tables are referenced. (Table A left outer join Table B on X left outer join Table C on Z.)
Table B and C are partitioned.
All three tables have a degree of 1.
select index_name from dba_indexes where degree != 1 and index_name not like 'SYS%' returns no rows. (Therefore indexes have degree of 1).
I have the following parameters set:

.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
os_authent_prefix                    string
os_roles                             boolean     FALSE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            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_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     960
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0

With

all degrees of tables and indexes set to 1
parallel_degree_policy set to MANUAL
no hints are provided at the query

What could be possible reasons, when running an explain plan, for my automatic DOP: Computed Degree of Parallelism to be calculated as 3?

I guess that although all tables and indexes have DOP = 1, CBO still can scan all of your three tables envolved in the query in parallel. That is the reason of computed DOP being equal to 3. Also please post the execution plan, that might be helpful. — Yaroslav Shabalin, Jan 10 '14 at 11:48
what about `ALTER SESSION ENABLE PARALLEL DML`. Could be this an explanation? — ibre5041, Jan 10 '14 at 12:04

score 3 · Accepted Answer · edited May 23 '17 at 12:10

Look at my answer here for a list of 38 factors that can influence the degree of parallelism.

But luckily most of those items will only lower the DOP, there aren't many ways to unexpectedly raise the DOP.

With the information you have provided I think there are only two possible items that may cause the parallelism:

alter session force parallel (query|dml|ddl); Use this query to check for a session-level force: select * from v$session where pq_status = 'FORCED' or pdml_status = 'FORCED' or pddl_status = 'FORCED';.
Plan management that is secretly replacing your query with a hinted query. There could be a profile, outline, SQL Plan Management, or advanced rewrite that is adding a parallel hint. Check the explain plan with explain plan for ... and select * from table(dbms_xplan.display);. The Notes section will likely tell you if one of those options was used.

Reasons for Oracle Automatic Degree of Parallelism?

1 Answers1