I am experiencing rather strange behaviour of the DBMS_STATS.SET__PREFS proceduree.

I want to achieve that the statistics on the table is gathered with degrre = 4 in parallel and I want to achieve that by setting table preferences and not explicitely mentioning degree in the call to gather table statistics.

...DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.

I checked on 4G table and 250G table with the same result.

The second thing I noticed was how default degree is calculated in the procedure:

...
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
...

Please note the use of currently deprecated DBMS_STATS.GET_PARAM procedure that is replaced by GET_PREFS. The former would not pick up preferences set on the table level. If you set global preferences using DBMS_STATS.SET_GLOBAL_PREFS it would be used as the default (I verified).

Of cause depending on your situation you might find that solution unacceptable. I this case you can still explicitly specify the degree as you did before.

Looks like a bug to me, but of cause I have no confirmation of it yet.

Thanks very much for taking time to explore this.. I know, this paragraph , about oracle perhaps still using serial execution, despite degree set, is really disturbing.

It really means we do not have control at all- and also what criteria then does oracle use to decide if the table is big or not..

As for

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

I interpret it this way:

-default value for degree is obtained from the function get_param
-but you can override this by executing set_table_prefs for degree

so it still should work. But it does not work. The question is why?

As for my implementation of gather_statistics, I may as well mention degree explicitely- that is no problem.
But I just want to understand what is going on here - since this problem brings the whole concept of hierarchy of preferences down. Why do we need set_table_prefs then at all ?