如何删除12.2以上版本优化器统计助手的老信息

郑全

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)


In this Document
Goal
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
GOAL
Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space.

Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.

DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automatically during the Auto purge window.

In 12.2.0.1, the EXECUTION_DAYS_TO_EXPIRE parameter is set to UNLIMITED which means the old records would never be purged.

SQL> col TASK_NAME format a25
SQL> col parameter_name format a35
SQL> col parameter_value format a20
SQL> set lines 120
SQL> 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';

TASK_NAME                 PARAMETER_NAME                      PARAMETER_VALUE
------------------------- ----------------------------------- --------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE            UNLIMITED
Since AUTO_STATS_ADVISOR_TASK records are not purged from WRI$_ADV_OBJECTS, the SYSAUX space usage is growing rapidly.

There is a Unpublished Bug 27983174 identified where EXECUTION_DAYS_TO_EXPIRE is set to UNLIMITED for AUTO_STATS_ADVISOR_TASK which causes the statistics advisor not to get purged. Document 27983174.8 lnx-183-mdb:got ORA-12012, ORA-20001 and ORA-6512 err from ora$at_os_opt_sy_xxx

There is another Unpublished Bug 26764561 due to that delete_expired_tasks does not work properly.
Document 26764561.8 SQL TUNING Tasks may Fail With: ORA-00933: SQL command not properly ended (SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION)

SOLUTION
For 12.2.0.1, Download & Apply the Patch 30138470 12.2.0.1.191015 (Oct 2019) Database Release Update (DB RU) or above where the above two bug fixes are included. For 18c, Download & Apply the Patch 28822489 18.5.0.0.190115 (Jan 2019) Database Release Update (DB RU) or above where the above two bug fixes are included. For 19c, the bug fixes are included in 19.1.0 itself.

IMPORTANT: It is always recommended to apply the latest DB RU patches which includes many known bug fixes.

For information about Release Updates please read Document 2285040.1

Any one of the following options can be considered after applying the aforementioned respective DB RU patch or above.

Option 1 (Auto-Purge):

The EXECUTION_DAYS_TO_EXPIRE parameter is set to 30 (by default) for AUTO_STATS_ADVISOR_TASK after applying the aforesaid respective DBRU. Hence the old records beyond 30 days are marked as expired. The Auto-Purge job would remove the expired records beyond the retention period of 30 days. Wait for few days for Auto-Purge window to remove the old records if the data is huge.

Option 2 (Manual):

The expired statistics advisor records can be purged manually using the below command instead of relying on Auto-Purge window. This would purge the old statistics advisor records beyond the 30 days retention.
SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;


In Multitenant environment (CDB/PDB), even after applying the aforesaid respective RU patch, the expired AUTO_STATS_ADVISOR_TASK data is NOT purged automatically from PDB despite the default setting of EXECUTION_DAYS_TO_EXPIRE to 30 or to any custom value. In CDB, the expired tasks are purged through Auto-Purge window. In such cases, the expired tasks can be deleted manually using the above command in PDB to clear the SYSAUX space due to AUTO_STATS_ADVISOR_TASK pertaining to the particular PDB. There is an enhancement request raised to implement the Auto-Purge mechanism of expired advisor tasks from PDB as well.

<Unpublished> ENH 31028071 - PURGE EXPIRED AUTO_STATS_ADVISOR_TASK DATA IN PDB
Option 3 (Custom):

If the default 30 days retention period is not required with the interest of SYSAUX space consumption then EXECUTION_DAYS_TO_EXPIRE parameter value can be adjusted. Following commands can be used to modify the parameter to custom value as per the requirement to purge the statistics advisor data with lesser retention period. In this example, it is set to 10.
SQL> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);
or

SQL> EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => 10);
The EXECUTION_DAYS_TO_EXPIRE  parameter value can also be adjusted for INDIVIDUAL_STATS_ADVISOR_TASK if it is configured and there are too many records exists.

SQL> 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';

TASK_NAME                 PARAMETER_NAME                      PARAMETER_VALUE
------------------------- ----------------------------------- --------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE            10
Now, all the statistics advisor execution records older than 10 days are marked as expired and would be purged automatically through the Auto-Purge window.

To purge manually using the below command instead of Auto-Purge window:-

SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;
After the purge, do alter table WRI$_ADV_OBJECTS move; and rebuild three indexes. This will free space in the tablespace."

Example:

Before running delete_expired_tasks (Very old statistics advisor records are found):-

SQL> col task_name format a25
SQL> col EXECUTION_NAME format a15
SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';

   TASK_ID TASK_NAME                 EXECUTION_NAME  EXECUTION
---------- ------------------------- --------------- ---------
        11 AUTO_STATS_ADVISOR_TASK   EXEC_1          26-JAN-17
        11 AUTO_STATS_ADVISOR_TASK   EXEC_11         18-NOV-19
        11 AUTO_STATS_ADVISOR_TASK   EXEC_25         19-NOV-19
...
        11 AUTO_STATS_ADVISOR_TASK   EXEC_5590       04-APR-20
...
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6505       14-APR-20
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6535       15-APR-20
Executing the delete_expired_tasks manually.

SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;

PL/SQL procedure successfully completed.
After running the delete_expired_tasks, old statistics advisor records older than 10 days retention (EXECUTION_DAYS_TO_EXPIRE) are purged.

SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';

        11 AUTO_STATS_ADVISOR_TASK   EXEC_5796       06-APR-20
        11 AUTO_STATS_ADVISOR_TASK   EXEC_5867       07-APR-20
...
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6505       14-APR-20
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6535       15-APR-20