Embedded Files
Check
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
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.
NOTE: WRI$_ADV_OBJECTS_IDX_02 did not exist on my 12.2 test instance.
Bibliography
Bibliography
https://support.oracle.comHow To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
Page updated
Google Sites
Report abuse