Fixing DEGREE on tables does not help
Ralf, December 12, 2016 - 9:07 am UTC
Hi,
yes, we tried to use a fixed DEGREE on both tables. One table has DEGREE = 1 and the other DEGREE = 32. Both seem to be ignored by the optimizer when gathering statistics.
Best regards,
Ralf
December 13, 2016 - 3:29 am UTC
can we get
show parameter parallel
from all instances
A reader, December 12, 2016 - 7:09 pm UTC
DBMS_STATS.SET_*_PREFS(DEGREE).
Using DBMS_STATS.SET_TABLE_PREFS() does the trick
Ralf, December 13, 2016 - 9:30 am UTC
Hi all,
yes, the optimizer ignores the limit imposed by PARALLEL_DEGREE_LIMIT when gathering statistics.
We have to adjust the DOP used on those tables for statistics gathering using the mentioned DBMS_STATS.SET_*_PREFS() procedures.
The PARALLEL* parameters are:
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string 8
parallel_degree_policy string AUTO
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 1280
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 512
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
2
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ -------------------------------- -----------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string 8
parallel_degree_policy string AUTO
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 1280
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 512
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
Regards,
Ralf
December 15, 2016 - 5:09 am UTC
I'm glad the set_table_pref has assisted.
Admittedly, we've got so many variations of the parallel parameters nowadays, its tough to keep up (well...I know *I* find it tough).
Similarly, the prefs could be picked up at table, schema or database level, and "auto" degree then defers to what we find on the table etc (plus the 'concurrent' setting as well from 11.2 onwards).
If you trace the dbms_stats calls, it would be interesting to see if you are getting a "parallel" hint in the generated sql's, or a "parallel(n)" hint. The latter (I think) would override any parallel_degree_limit set