AUTO_STATS_ADVISOR_TASK how to get its recommendations
Table of contents
Preamble
One of my DBA teammate highlighted me about a table that was quite big in SYSAUX tablespace and asked me if I had the same behavior and how to clean it:
SQL> select count(*) from sys.wri$_adv_objects; COUNT(*) ________ 45925625
Here we have around 45 millions rows while in my other production database I had only 2 millions rows so around 20 time less !
This table is the base of DBA_ADVISOR_OBJECTS view. Checking from where is coming this used space in SYSAUX tablespace:
SQL> set pages 1000
SQL> select occupant_name,to_char(space_usage_kbytes,'999,999,999,999') as space_usage_kbytes
from v$sysaux_occupants
order by space_usage_kbytes desc
fetch first 5 rows only;
OCCUPANT_NAME SPACE_USAGE_KBYTES
________________________ __________________
SM/ADVISOR 11,930,432
SM/AWR 793,408
AUDSYS 546,880
SM/OPTSTAT 484,928
XDB 64,384
Or:
SQL> select * from (select segment_name,owner,tablespace_name,bytes/1024/1024 "Size (MB)",segment_type from dba_segments where tablespace_name='SYSAUX' order by bytes desc) fetch first 10 rows only; SEGMENT_NAME OWNER TABLESPACE_NAME Size (MB) SEGMENT_TYPE ______________________________ ______ _______________ _________ _______________ WRI$_ADV_OBJECTS SYS SYSAUX 4828 TABLE WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 2780 INDEX WRI$_ADV_OBJECTS_IDX_02 SYS SYSAUX 2280 INDEX WRI$_ADV_OBJECTS_PK SYS SYSAUX 1876 INDEX AUD$UNIFIED AUDSYS SYSAUX 249 TABLE PARTITION I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS SYSAUX 160 INDEX AUD$UNIFIED AUDSYS SYSAUX 127 TABLE PARTITION AUD$UNIFIED AUDSYS SYSAUX 120 TABLE PARTITION SYS_LOB0000010930C00001$$ SYS SYSAUX 91,1875 LOBSEGMENT LOGON_TRIGGER SYS SYSAUX 90 TABLE
This is Server Manageability – Advisor Framework ! To be more precise on which task we can perfrom (the same query can be done directly on sys.wri$_adv_objects):
SQL> select task_id, task_name, count(*) cnt from dba_advisor_objects group by task_id, task_name order by cnt desc fetch first 5 rows only;
TASK_ID TASK_NAME CNT
_______ ______________________________ ________
6 AUTO_STATS_ADVISOR_TASK 45919058
1 SYS_AUTO_SQL_TUNING_TASK 4942
10731 SYS_AUTO_SPCADV606002214102025 58
9847 SYS_AUTO_SPCADV505002225092025 54
10034 SYS_AUTO_SPCADV605002230092025 53
So this is clearly the AUTO_STATS_ADVISOR_TASK that is consuming the most space in SYSAUX tablespace…
My testing has been on two Oracle databases running Enterprise Edition databases release 19.25 and release 19.28.
AUTO_STATS_ADVISOR_TASK runs and findings
The first think I had in mind was that the result of this Scheduler Advisor Task did not get purged. Check the parameter with:
SQL> select task_name, parameter_name, parameter_value
from dba_advisor_parameters
where task_name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK')
and parameter_name in ('DAYS_TO_EXPIRE', 'EXECUTION_DAYS_TO_EXPIRE')
order by task_name, parameter_name;
TASK_NAME PARAMETER_NAME PARAMETER_VALUE
_____________________________ ________________________ _______________
AUTO_STATS_ADVISOR_TASK DAYS_TO_EXPIRE UNLIMITED
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30
INDIVIDUAL_STATS_ADVISOR_TASK DAYS_TO_EXPIRE UNLIMITED
INDIVIDUAL_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30
If you have non-expected values you can change them with something like:
exec dbms_advisor.set_task_parameter(task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => 30);
Do I have plenty of run in the SYSAUX repository ?
SQL> set pages 200
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select task_id, task_name, execution_name, execution_start, execution_end, status
from dba_advisor_executions
where task_name='AUTO_STATS_ADVISOR_TASK'
order by execution_start desc;
TASK_ID TASK_NAME EXECUTION_NAME EXECUTION_START EXECUTION_END STATUS
_______ _______________________ ______________ ____________________ ____________________ _________
6 AUTO_STATS_ADVISOR_TASK EXEC_11854 15-oct-2025 22:03:16 15-oct-2025 22:33:22 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11795 14-oct-2025 22:03:19 14-oct-2025 22:33:23 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11724 13-oct-2025 22:01:56 13-oct-2025 22:31:57 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11662 12-oct-2025 06:03:24 12-oct-2025 06:33:24 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11631 11-oct-2025 22:09:42 11-oct-2025 22:39:43 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11598 10-oct-2025 22:02:24 10-oct-2025 22:32:24 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11551 09-oct-2025 22:03:27 09-oct-2025 22:33:31 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11501 08-oct-2025 22:03:07 08-oct-2025 22:33:08 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11442 07-oct-2025 22:03:37 07-oct-2025 22:33:38 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11382 06-oct-2025 22:01:31 06-oct-2025 22:31:32 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11292 05-oct-2025 06:03:39 05-oct-2025 06:33:40 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11266 04-oct-2025 22:08:06 04-oct-2025 22:38:15 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11191 03-oct-2025 22:02:39 03-oct-2025 22:32:40 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11141 02-oct-2025 22:02:30 02-oct-2025 22:32:31 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11091 01-oct-2025 22:02:42 01-oct-2025 22:32:43 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_11041 30-sep-2025 22:02:24 30-sep-2025 22:32:25 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10998 29-sep-2025 22:04:16 29-sep-2025 22:34:20 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10941 28-sep-2025 06:04:47 28-sep-2025 06:34:47 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10921 27-sep-2025 22:04:02 27-sep-2025 22:34:02 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10887 26-sep-2025 22:02:20 26-sep-2025 22:32:20 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10857 25-sep-2025 22:02:40 25-sep-2025 22:32:40 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10827 24-sep-2025 22:02:58 24-sep-2025 22:33:01 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10798 23-sep-2025 22:03:15 23-sep-2025 22:33:15 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10770 22-sep-2025 22:03:38 22-sep-2025 22:33:39 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10721 21-sep-2025 06:03:26 21-sep-2025 06:33:26 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10701 20-sep-2025 22:11:36 20-sep-2025 22:41:37 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10667 19-sep-2025 22:03:47 19-sep-2025 22:33:47 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10639 18-sep-2025 22:06:00 18-sep-2025 22:36:01 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10607 17-sep-2025 22:03:07 17-sep-2025 22:33:08 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10572 16-sep-2025 22:02:06 16-sep-2025 22:32:07 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10482 15-sep-2025 22:01:41 15-sep-2025 22:31:42 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10392 14-sep-2025 06:03:07 14-sep-2025 06:33:08 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10336 13-sep-2025 22:09:04 13-sep-2025 22:39:04 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10232 12-sep-2025 22:04:44 12-sep-2025 22:34:46 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10181 11-sep-2025 22:03:19 11-sep-2025 22:33:19 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10132 10-sep-2025 22:04:50 10-sep-2025 22:34:51 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10082 09-sep-2025 22:03:12 09-sep-2025 22:33:12 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_10022 08-sep-2025 22:01:34 08-sep-2025 22:31:37 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9962 07-sep-2025 06:03:18 07-sep-2025 06:33:19 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9931 06-sep-2025 22:05:36 06-sep-2025 22:35:37 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9892 05-sep-2025 22:04:53 05-sep-2025 22:34:54 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9852 04-sep-2025 22:03:01 04-sep-2025 22:33:02 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9812 03-sep-2025 22:03:09 03-sep-2025 22:33:09 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9761 02-sep-2025 22:02:13 02-sep-2025 22:32:14 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9718 01-sep-2025 22:02:52 01-sep-2025 22:32:52 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9651 31-aug-2025 06:02:31 31-aug-2025 06:32:33 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9631 30-aug-2025 22:03:53 30-aug-2025 22:33:54 COMPLETED
6 AUTO_STATS_ADVISOR_TASK EXEC_9571 29-aug-2025 22:02:16 29-aug-2025 22:32:17 COMPLETED
The purging is apparently working well and I have the same behavior on my two databases. So could it be coming from the findings per execution that would be completely different ?
SQL> select f.task_id, f.task_name, f.execution_name, f.message, r.benefit_type
from dba_advisor_findings f join dba_advisor_recommendations r on f.task_name = r.task_name and f.finding_id = r.finding_id
where f.task_name = 'AUTO_STATS_ADVISOR_TASK'
order by f.task_id, f.task_name, to_number(substr(f.execution_name,6)) desc, f.finding_id
fetch first 20 rows only;
TASK_ID TASK_NAME EXECUTION_NAME MESSAGE BENEFIT_TYPE
_______ _______________________ ______________ _____________________________________________________________________ ______________________________________________________________________________________________________________
6 AUTO_STATS_ADVISOR_TASK EXEC_11854 The CONCURRENT preference is not used. Set the CONCURRENT preference.
6 AUTO_STATS_ADVISOR_TASK EXEC_11854 There are 28 statistics operation(s) using nondefault parameters. Use default parameters for statistics operations.
6 AUTO_STATS_ADVISOR_TASK EXEC_11854 There are 1 uses of GATHER_TABLE_STATS. Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
6 AUTO_STATS_ADVISOR_TASK EXEC_11854 Statistics are being gathered on 1 object(s) when they are not stale. GATHER_SCHEMA_STATS with GATHER_STALE option can be used to gather statistics only when the objects are stale.
6 AUTO_STATS_ADVISOR_TASK EXEC_11854 There are 275 table(s) which have been dropped multiple times. Use TRUNCATE TABLE instead of DROP TABLE commands on these tables.
6 AUTO_STATS_ADVISOR_TASK EXEC_11795 The CONCURRENT preference is not used. Set the CONCURRENT preference.
6 AUTO_STATS_ADVISOR_TASK EXEC_11795 There are 28 statistics operation(s) using nondefault parameters. Use default parameters for statistics operations.
6 AUTO_STATS_ADVISOR_TASK EXEC_11795 There are 1 uses of GATHER_TABLE_STATS. Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
6 AUTO_STATS_ADVISOR_TASK EXEC_11795 Statistics are being gathered on 3 object(s) when they are not stale. GATHER_SCHEMA_STATS with GATHER_STALE option can be used to gather statistics only when the objects are stale.
6 AUTO_STATS_ADVISOR_TASK EXEC_11795 There are 275 table(s) which have been dropped multiple times. Use TRUNCATE TABLE instead of DROP TABLE commands on these tables.
6 AUTO_STATS_ADVISOR_TASK EXEC_11724 The CONCURRENT preference is not used. Set the CONCURRENT preference.
6 AUTO_STATS_ADVISOR_TASK EXEC_11724 There are 28 statistics operation(s) using nondefault parameters. Use default parameters for statistics operations.
6 AUTO_STATS_ADVISOR_TASK EXEC_11724 There are 1 uses of GATHER_TABLE_STATS. Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
6 AUTO_STATS_ADVISOR_TASK EXEC_11724 There are 275 table(s) which have been dropped multiple times. Use TRUNCATE TABLE instead of DROP TABLE commands on these tables.
6 AUTO_STATS_ADVISOR_TASK EXEC_11662 The CONCURRENT preference is not used. Set the CONCURRENT preference.
6 AUTO_STATS_ADVISOR_TASK EXEC_11662 There are 29 statistics operation(s) using nondefault parameters. Use default parameters for statistics operations.
6 AUTO_STATS_ADVISOR_TASK EXEC_11662 There are 1 uses of GATHER_TABLE_STATS. Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
6 AUTO_STATS_ADVISOR_TASK EXEC_11662 Statistics are being gathered on 1 object(s) when they are not stale. GATHER_SCHEMA_STATS with GATHER_STALE option can be used to gather statistics only when the objects are stale.
6 AUTO_STATS_ADVISOR_TASK EXEC_11662 There are 275 table(s) which have been dropped multiple times. Use TRUNCATE TABLE instead of DROP TABLE commands on these tables.
6 AUTO_STATS_ADVISOR_TASK EXEC_11631 The CONCURRENT preference is not used. Set the CONCURRENT preference.
On the database with a bigger sys.wri$_adv_objects I have around 5 recommendations per run versus 3 recommendations per run on the one having the smaller sys.wri$_adv_objects table. This is a first observation but it is not a huge difference.
Digging further in objects involved in findings and recommendations:
SQL> select task_id, task_name, execution_name, count(*) cnt from dba_advisor_objects group by task_id, task_name, execution_name order by cnt desc fetch first 20 rows only;
TASK_ID TASK_NAME EXECUTION_NAME CNT
_______ _______________________ ______________ _______
6 AUTO_STATS_ADVISOR_TASK EXEC_9718 2886083
6 AUTO_STATS_ADVISOR_TASK EXEC_10667 2510687
6 AUTO_STATS_ADVISOR_TASK EXEC_10607 2399542
6 AUTO_STATS_ADVISOR_TASK EXEC_11041 2263235
6 AUTO_STATS_ADVISOR_TASK EXEC_9571 2197377
6 AUTO_STATS_ADVISOR_TASK EXEC_9892 2177610
6 AUTO_STATS_ADVISOR_TASK EXEC_9761 2120748
6 AUTO_STATS_ADVISOR_TASK EXEC_10132 1933379
6 AUTO_STATS_ADVISOR_TASK EXEC_10022 1897943
6 AUTO_STATS_ADVISOR_TASK EXEC_10887 1889020
6 AUTO_STATS_ADVISOR_TASK EXEC_10572 1840184
6 AUTO_STATS_ADVISOR_TASK EXEC_10770 1838981
6 AUTO_STATS_ADVISOR_TASK EXEC_10082 1690140
6 AUTO_STATS_ADVISOR_TASK EXEC_11631 1667063
6 AUTO_STATS_ADVISOR_TASK EXEC_9812 1541606
6 AUTO_STATS_ADVISOR_TASK EXEC_10336 1515423
6 AUTO_STATS_ADVISOR_TASK EXEC_9631 1464710
6 AUTO_STATS_ADVISOR_TASK EXEC_10232 1400042
6 AUTO_STATS_ADVISOR_TASK EXEC_10921 1298916
6 AUTO_STATS_ADVISOR_TASK EXEC_10701 1223876
This is where I have seen a huge difference in the number of records, I have an average of 2 millions objects involved versus an average of 100,000 on the other databases. So the smae 20 factor in the final number of rows.
AUTO_STATS_ADVISOR_TASK reporting
What is wrong on this database where the number of involved objects is 20 times bigger than on the other database. To understand this you have to generate a report. Reports are generated using rules defined in this table:
SQL> select name, description from v$stats_advisor_rules order by rule_id; NAME DESCRIPTION _______________________________ ___________________________________________________________________________ UseAutoJob Use Auto Job for Statistics Collection CompleteAutoJob Auto Statistics Gather Job should complete successfully MaintainStatsHistory Maintain Statistics History UseConcurrent Use Concurrent preference for Statistics Collection UseDefaultPreference Use Default Preference for Stats Collection TurnOnSQLPlanDirective SQL Plan Directives should not be disabled AvoidSetProcedures Avoid Set Statistics Procedures UseDefaultParams Use Default Parameters in Statistics Collection Procedures UseGatherSchemaStats Use gather_schema_stats procedure AvoidInefficientStatsOprSeq Avoid inefficient statistics operation sequences AvoidUnnecessaryStatsCollection Avoid unnecessary statistics collection AvoidStaleStats Avoid objects with stale or no statistics GatherStatsAfterBulkDML Do not gather statistics right before bulk DML LockVolatileTable Statistics for objects with volatile data should be locked UnlockNonVolatileTable Statistics for objects with non-volatile should not be locked MaintainStatsConsistency Statistics of dependent objects should be consistent AvoidDropRecreate Avoid drop and recreate object seqauences UseIncremental Statistics should be maintained incrementally when it is beneficial NotUseIncremental Statistics should not be maintained incrementally when it is not beneficial AvoidOutOfRange Avoid Out of Range Histogram endpoints UseAutoDegree Use Auto Degree for statistics collection UseDefaultObjectPreference Use Default Object Preference for statistics collection AvoidAnalyzeTable Avoid using analyze table commands for statistics collection
To generate an HTML report use below script:
set long 100000 set longchunksize 200 set heading off set pagesize 1000 --Finally doing with DUAL as you can remove the heading and have NO artifact in generated HTML report --variable report clob --exec :report:=dbms_stats.report_advisor_task(task_name=>'AUTO_STATS_ADVISOR_TASK', execution_name=> 'EXEC_11795', type=> 'HTML', section => 'ALL', level=>'ALL +SHOW_HIDDEN') spool c:\download\AUTO_STATS_ADVISOR_TASK.html select dbms_stats.report_advisor_task(task_name=>'AUTO_STATS_ADVISOR_TASK', execution_name=> 'EXEC_11795', type=> 'HTML', section => 'ALL', level=>'ALL +SHOW_HIDDEN') as report from dual; --print report spool off
Examples of what you can find in this report. Gather statistics only if they are stale:

Use TRUNCATE instead of DROP TABLE to avoid loosing some statistics (for example, histograms) stored with column usage information:

To apply recommendations you could use DBMS_STATS.IMPLEMENT_ADVISOR_TASK but the granularity is the task_name / execution_name so it looks difficult to use it…
References
- SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
- SYSAUX Grows Excessively with Entries for AUTO_STATS_ADVISOR_TASK in Table WRI$_ADV_OBJECTS (Doc ID 3104754.1)
- How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
- Tips if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)





