There are multiple ways to avoid the vast amounts of data in the SYSAUX tablespace created by the AUTO_STATS_ADVISOR_TASK. This advisor runs during a maintenance window, and stores its history in the “SM/ADVISOR” class of segments in SYSAUX.
Zero
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);That’s a proper way to get rid of it, and to implicitly truncate all the data segments connected with it. So it’s a good starting point anyway. But now, you will face ORA-12012 errors for background jobs named like “SYS”.”ORA$AT_OS_OPT_SY_20523″. As they still have the task on their list, it will fault once per execution attempt. So dropping the task can’t be the (whole) solution: DON’T STOP HERE.
Re-initialize the task – it will be back, it will run again, and it will fill up SYSAUX again – but that is why we will have further steps. 🙂
EXEC DBMS_STATS.INIT_PACKAGE();First
A very nice way to have the job, but let it do “nothing, successfully” is to overwrite its rules by creating a rule filter for it:
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
'AUTO_STATS_ADVISOR_TASK',
NULL, -- applies to all operation types (EXECUTE/REPORT/…)
NULL, -- all rules
'DISABLE');
END;
/So we still have the job, it will run, but it does nothing, and most important, writes nothing into MY PRECIOUS SYSAUX!
Second
“Park” the auto advisor by setting its internal intervals to a maximum. This effectively disables automated executions, but keeps the task defined. And thus, the job calling it won’t complain.
BEGIN
dbms_advisor.set_task_parameter('AUTO_STATS_ADVISOR_TASK','_AUTO_MMON_INTERVAL',2147483647);
dbms_advisor.set_task_parameter('AUTO_STATS_ADVISOR_TASK','_AUTO_STATS_INTERVAL',2147483647);
END;Advisor does not run – advisor does not top off SYSAUX.
Third
If you are lucky enough to have a system of 21c or above, or a 19c with the appropriate back-port in it (Bug 26749785 which can’t be in a RU due to not being RAC-Rolling) it’s just a preference to set:
exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');Advisor will not run – advisor will not fill up my SYSAUX.
Fourth
If you still want to receive information from this advisor, you can also keep its retention to a minimum. Of course, after re-initializing and truncating the segments it as described above.
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER (task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 1);Advisor will run – advisor will litter my SYSAUX, but not as much as before.
Personal Remark
I do not like advisors. I frequently disable things like Space Advisor and, most of all, the Tuning Advisor. This AUTO_STATS_ADVISOR_TASK is just another one who frequently complains about well-reflected and well-tested measures (non-default statistics collection in this case), and there is no way explaining my reasons to it.
Stacked heuristics make it terribly difficult to reproduce results when searching for errors. Even (or especially?) if a human is in between and clicks “OK I accept this suggestion.” People have the tendency to hide behind Oracle’s automatic recommendations, crediting the tools behind them with an undue expertise. Use your own brain!
Sources and Good Further Reads
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
https://mikedietrichde.com/2023/02/28/oracle-optimizer-statistics-advisor-in-oracle-19c
One thought on “Multiple Ways How to Get Rid of the Oracle Database Optimizer Statistics Advisor AUTO_STATS_ADVISOR_TASK”