Statistics Advisor

Check

To see execution dates...

COLUMN task_name FORMAT a25

COLUMN execution_name FORMAT a15

SELECT task_id,

task_name,

execution_name,

execution_start

FROM dba_advisor_executions

WHERE TASK_NAME='AUTO_STATS_ADVISOR_TASK';

EXECUTION_DAYS_TO_EXPIRE

COLUMN task_name FORMAT a25

COLUMN parameter_name FORMAT a35

COLUMN parameter_value FORMAT a20

SET LINESIZE 120

SELECT task_name,

parameter_name,

parameter_value

FROM dba_advisor_parameters

WHERE task_name='AUTO_STATS_ADVISOR_TASK'

AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

  • A parameter_value of UNLIMITED could lead to excessive SYSAUX tablespace usage.
  • For 12.2.0.1 and 18c, UNLIMITED is the default unless 12.2.0.1.191015 RU or 18.5.0.0.190115 RU is applied. Otherwise the default is 30 days.

To Change EXECUTION_DAYS_TO_EXPIRE to 30...

EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK',

parameter=> 'EXECUTION_DAYS_TO_EXPIRE',

value => 30);

Or...

EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK',

parameter => 'EXECUTION_DAYS_TO_EXPIRE',

value => 30);

To manually purge...

exec prvt_advisor.delete_expired_tasks;

To free up space after the purge completes...

alter table WRI$_ADV_OBJECTS move;

alter index WRI$_ADV_OBJECTS_PK rebuild;

alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;

alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

NOTE: If you don't manually purge after changing the parameter, the purge will happen automatically during the next scheduled autotask execution. Note that the scheduled run will not perform the move/rebuild commands to free up space.
NOTE: WRI$_ADV_OBJECTS_IDX_02 did not exist on my 12.2 test instance.

Bibliography


https://support.oracle.comHow To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)