Why doesn't dbms_stats.set_schema_prefs seem to work?

When trying to alter the default settings for statistics gathering on partitioned tables, I had troubles setting schema-level preferences using dbms_stats.set_schema_prefs:

exec dbms_stats.set_schema_prefs('SCOTT','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

But when you check if the setting has been accepted using dbms_stats.get_prefs, it still seem to be set to FALSE:

SELECT  DBMS_STATS.get_prefs(ownname=>'SCOTT',pname=>'INCREMENTAL') "INCREMENTAL" FROM DUAL;

INCREMENTAL
--------------------
FALSE

I found the following note by Maria Colgan:

http://www.nocoug.org/download/2011-11/Maria_Colgan_Optimizer_Statistics.pdf

which states that set_schema_prefs only applies to current objects in the schema and that new objects will pick up global preferences instead.

So according to the note, I was doing the right thing, after all I was trying to alter the default behaviour for the optimizer when working on existing objects.

How come the new setting didn't seem to stick?

In my case, a global change was acceptable, so I could try to set the parameter globally instead, using set_global_prefs:

BEGIN
  dbms_stats.set_global_prefs('INCREMENTAL','TRUE');
END;
/

PL/SQL procedure successfully completed.

After that, the setting certainly seem to stick:

SELECT  DBMS_STATS.get_prefs(ownname=>'SCOTT',pname=>'INCREMENTAL') "INCREMENTAL" FROM DUAL;

INCREMENTAL
--------------------
TRUE

Maria points out that a global change like the one above will apply to all existing objects as well as any new objects.

My conlution is that dbms_stats.get_prefs will report the settings that applies for future objects only, not existing ones. If that is the case, the output of the report is ambiguous, as it will give the DBA the impression that he/she has not made the change permanent.

To view the default settings for a particular schema, see this post.

Oracle Documentation on the subject is here