How To Disable Optimizer Statistics Advisor From 12.2 Onwards

刘泽宇

目标：
Goal of this document is to provide the method to disable the Optimizer Statistics Advisor from 12.2 onwards.

There are many issues related to Statistics advisor from 12.2.

There are lot of SYSAUX space occupied by the automatic statistics advisor task AUTO_STATS_ADVISOR_TASK. Currently, there is no method to disable the the task specifically.
There is no information available in the dictionary to know whether the AUTO_STATS_ADVISOR_TASK is enabled or disabled.

处理方法：
There is an enhancement raised to provide the feature to control the statistics advisor and is fixed in 21C. The enhancement fix added a new preference 'AUTO_STATS_ADVISOR_TASK' to control the statistic advisor run.

Unpublished Bug 26749785 - PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK
One-off backport can be requested for versions like 12.2 / 18c / 19c to get this functionality.

With the new fix, a new preference "AUTO_STATS_ADVISOR_TASK" is added to enable/disable AUTO_STATS_ADVISOR_TASK manually.

'TRUE'(default) :  statistics advisor will run after auto stats collection
'FALSE'            :  auto stats advisor task will be disabled.

User can set this preference to TRUE/FALSE using DBMS_STATS.set_global_prefs and check the current value set for the preference using DBMS_STATS.get_prefs API.
To check the current value for the preference:

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
TRUE

To disable the Statistics Advisor:

SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
FALSE