Skip to content

AUTO_STATS_ADVISOR_TASK how to get its recommendations

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:

AUTO_STATS_ADVISOR_TASK01
AUTO_STATS_ADVISOR_TASK01

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

AUTO_STATS_ADVISOR_TASK02
AUTO_STATS_ADVISOR_TASK02

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)

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *