This content has been marked as final. Show 4 replies
I just checked and got the same result in 11.2 database.
At first I noticed the following in the description of the procedure:
...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:
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).
... degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), ...
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.
Hope it helps.
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..
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 ?
Edited by: Reggy on Nov 27, 2012 8:40 AM