Geek Answers Handbook

Reasons for Oracle Automatic Degree Parallelism?

I apologize in advance as I am new to this area and that I cannot provide all the necessary information in advance, but here.

  • I am in two node RAR servers.
  • Optimizer statistics were recently compiled and very accurate.
  • I have a query referenced by 3 tables. (Table A on the left is the outer join Table B on the X is the left outer link Table C on the Z.)
  • Tables B and C are broken.
  • All three tables have degree 1.
  • select index_name from dba_indexes, where degree! = 1, and index_name does not look like "SYS%" does not return rows. (Therefore, indices have degree 1).
  • I have the following options 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

FROM

  • all degrees of tables and indexes set to 1
  • parallel_degree_policy set to MANUAL
  • the request is missing hints

DOP: Parallelism 3?

+4
1

38 , parallelism.

, , DOP, DOP.

, , parallelism:

  • alter session force parallel (query|dml|ddl); , : select * from v$session where pq_status = 'FORCED' or pdml_status = 'FORCED' or pddl_status = 'FORCED';.
  • , . , , SQL , . explain plan for ... select * from table(dbms_xplan.display);. "", , , .
+3

More articles:


All Articles