I'm seeing a curious thing on my 12c database (12.1.0.2.0) and wondered if anyone had any information that could explain this behaviour. Nothing is wrong, but I don't know why Oracle is claiming what it reports for DOP in parallel query execution plans. PARALLEL_DEGREE_POLICY is set to MANUAL so there is no "Auto DOP" happening. I'm seeing Oracle 12c report "Degree of Parallelism is # because of table property" where "#" is an integer number, but none of my tables have their DEGREE set at all. So where is the Optimizer pulling these DOP values from then? The large "fact" tables in the queries are partitioned, and have various numbers of partitions - 13 or 192 in the tables in the queries I am looking at. The other smaller "dimension" tables in the queries are not partitioned at all. Each query is only referencing one large table and between 3 and 6 other much smaller tables.
Details of my system and database - Oracle 12.1.0.2.0 on a Sun SPARC Solaris system with multiple CPUs.
SQL> select * from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Solaris: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- -------------
cpu_count integer 48
parallel_threads_per_cpu integer 2
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------
parallel_adaptive_multi_user boolean FALSE
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 500
parallel_min_percent integer 0
parallel_min_servers integer 96
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 384
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
In execution plans that are executed using parallel query execution I'm seeing Oracle report in the "Note" section that "Degree of Parallelism is # because of table property", but the value for "#" doesn't correspond to anything set against a table. And all of the tables and their indexes have a DEGREE of DEFAULT or 1 or even 0:
SQL> select degree, count (*) cnt from dba_tables where owner='XXXXX' group by degree order by 1 ;
DEGREE CNT
---------------------------------------- ----------------
1 76
DEFAULT 9
SQL> select degree, count (*) cnt from dba_indexes where owner='XXXXX' group by degree order by 1 ;
DEGREE CNT
---------------------------------------- ----------------
0 1
1 366
DEFAULT 179
Examples from real execution plans obtained from DBMS_XPLAN.DISPLAY_CURSOR:
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 16 because of table property
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 48 because of table property
Like I said, 16 and 48 don't make sense to me given that no tables have such DEGREE values against them, and the system has a CPU_COUNT value of 48 but also 2 for PARALLEL_THREADS_PER_CPU. If the DOP is based on CPUs * THREADS then why is it being reported as being "because of table property"? Is this a bug in the sense that it is not "because of a table property" and the "Note" output is incorrect? Is the "dynamic sampling" causing this in some way?
I'm just curious as to what is going on and how much I can trust this DOP information in the Note section in 12c.
Thanks,
John
EDIT - Added mention that tables are partitioned.