Reasons for Oracle Automatic Degree of Parallelism?
cost-based-optimizer, oracle11g, parallel-processing, performance, sql
Solution
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.
Problem
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?