Clean SYSAUX Tablespace, purge data from WRI$_ADV_OBJECTS

============

Issue 

============

Tablespace SYSAUX nearly 100% full because table WRI$_ADV_OBJECTS and related indexes are very big

This is a know issue in Oracle 12.2

============

Solution

============

Purge Data from WRI$_ADV_OBJECTS.

Current space usage:

TABLESPACE_NAME FREE_SPACE_MB USED_SPACE_MB     MAX_SPACE_MB  
--------------- ------------- ----------------- ------------- 
SYSAUX                     46              7954         8000 

 
SQL> select * from v$version;
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production                0
PL/SQL Release 12.2.0.1.0 - Production                               

Top objects in SYSAUX Tablespace

                     0
OWNER   TABLESPACE_NAME SEGMENT_NAME                USED_MB
------- --------------- --------------------------- -------
SYS     SYSAUX          WRI$_ADV_OBJECTS               4145
SYS     SYSAUX          WRI$_ADV_OBJECTS_IDX_01        1666
SYS     SYSAUX          WRI$_ADV_OBJECTS_PK            1104
SYS     SYSAUX          SYS_LOB0000164068C00005$$       160


COL TASK_NAME FOR A50

SELECT task_name, count(*) cnt 

  FROM dba_advisor_objects 

GROUP BY task_name 

ORDER BY cnt DESC;

TASK_NAME                 CNT
------------------------- ----------
AUTO_STATS_ADVISOR_TASK   11061381
ADDM:1066102538_1_28416   13
ADDM:1066102538_1_28062   13
ADDM:1066102538_1_27837   12
ADDM:1066102538_1_28086   11
ADDM:1066102538_1_28302   11
ADDM:1066102538_1_27885   11
ADDM:1066102538_1_28134   10

==========================================

Reference for cleanup from WRI$_ADV_OBJECTS:

==========================================

Reference:
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)

If there are too many records in the table WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK, then huge UNDO would be required.
In such cases, following method can be implemented to purge the data by avoiding excessive redo/undo generation.

CREATE TABLE WRI$_ADV_OBJECTS_NEW 
AS SELECT * FROM WRI$_ADV_OBJECTS 
WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

SELECT COUNT(* )FROM WRI$_ADV_OBJECTS_NEW;

COUNT(*)

--------

       2

TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

Note - In case of error ORA-54013: INSERT operation disallowed on virtual columns - see below #Handle ORA-54013

commit;

DROP TABLE WRI$_ADV_OBJECTS_NEW;

--Reorganize the indexes
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

SYSAUX Tablespace usage after  cleanup:

TABLESPACE_NAME FREE_SPACE_MB USED_SPACE_MB     MAX_SPACE_MB  
--------------- ------------- ----------------- ------------- 
SYSAUX                   6960              1040          8000 

============

Prevent issue from happen again

============

Reference: 

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

COL task_name FORMAT a25

COL parameter_name FORMAT a35

COL parameter_value FORMAT a20

SET lines 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';

TASK_NAME                 PARAMETER_NAME            PARAMETER_VALUE

------------------------- ------------------------- -----------------

AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE  UNLIMITED

BEGIN

 DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 30);

END;

/

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  30

Now, all the statistics advisor execution records older than 30 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:-

conn / as sysdba

BEGIN

 PRVT_ADVISOR.delete_expired_tasks;

END;

/

 Check status after purge

COL EXECUTION_NAME FOR A30

SELECT task_id,task_name,execution_name ,execution_start 

  FROM DBA_ADVISOR_EXECUTIONS 

 WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

ORDER BY  execution_start;

   TASK_ID TASK_NAME                 EXECUTION_NAME  EXECUTION_START

---------- ------------------------- --------------- ---------------

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15898      01-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15899      01-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15912      02-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15913      02-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15930      03-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15931      03-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15948      04-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15960      05-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15983      06-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16010      07-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16027      08-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16059      09-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16084      10-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16107      11-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16140      12-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16168      13-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16200      14-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16222      15-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16248      16-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16270      17-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16295      18-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16318      19-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16349      20-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16375      21-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16398      22-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16421      23-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16443      24-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16459      25-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16488      26-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16501      27-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16529      28-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16543      29-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16558      30-MAR-21

========================

Appendix

========================

1. crontab script to automate purge

crontab

2 7 * * * bash -l /software/oracle/oracle/scripts/space/space_cleanup/delete_expired_tasks.sh

delete_expired_tasks.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/space/space_cleanup

. /etc/sh/orash/oracle_login.sh igt

ORACLE_HOME=/software/oracle/1910

ORACLE_SID=igt

PATH=${PATH}:${ORACLE_HOME}/bin

cd ${WORK_DIR}

sqlplus / as sysdba @delete_expired_tasks.sql

delete_expired_tasks.sql

BEGIN

 PRVT_ADVISOR.delete_expired_tasks;

END;

/

EXIT;

2. Handle ORA-54013: INSERT operation disallowed on virtual columns 

SQL> INSERT INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

INSERT INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

SELECT column_name, virtual_column

  FROM user_tab_cols

 WHERE table_name = 'WRI$_ADV_OBJECTS'

COLUMN_NAME                    VIRTUAL_COLU

------------------------------ ------------

ID                             NO

TYPE                           NO

TASK_ID                        NO

EXEC_NAME                      NO

ATTR1                          NO

ATTR2                          NO

ATTR3                          NO

ATTR4                          NO

ATTR5                          NO

ATTR6                          NO

ATTR7                          NO

ATTR8                          NO

ATTR9                          NO

ATTR10                         NO

ATTR11                         NO

ATTR12                         NO

ATTR13                         NO

ATTR14                         NO

ATTR15                         NO

ATTR16                         NO

ATTR17                         NO

ATTR18                         NO

ATTR19                         NO

ATTR20                         NO

OTHER                          NO

SPARE_N1                       NO

SPARE_N2                       NO

SPARE_N3                       NO

SPARE_N4                       NO

SPARE_C1                       NO

SPARE_C2                       NO

SPARE_C3                       NO

SPARE_C4                       NO

SQL_ID_VC                      YES

Need to specify field by field

COL column_name  FOR A40

SELECT  LOWER(column_name)||',' column_name 

  FROM USER_TAB_COLS 

 WHERE table_name = 'WRI$_ADV_OBJECTS_NEW' 

ORDER BY column_id;

INSERT INTO WRI$_ADV_OBJECTS

(

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

)

SELECT

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

 FROM WRI$_ADV_OBJECTS_NEW;

commit;

=======================

Create a job to cleanup 

=======================

delete_expired_tasks.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/space

. /etc/sh/orash/oracle_login.sh igt

ORA_VER=1910

ORACLE_SID=igt

ORACLE_HOME=/software/oracle/1910

cd ${WORK_DIR}

sqlplus / as sysdba @delete_expired_tasks.sql

delete_expired_tasks.sql

-----------------------------

--WRI$_ADV_OBJECTS

-----------------------------

CREATE TABLE WRI$_ADV_OBJECTS_NEW 

AS SELECT * FROM WRI$_ADV_OBJECTS 

WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT INTO WRI$_ADV_OBJECTS

(

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

)

SELECT

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

 FROM WRI$_ADV_OBJECTS_NEW;

commit;

DROP TABLE WRI$_ADV_OBJECTS_NEW;

ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

-----------------------------

--WRI$_ADV_OBJECTS

-----------------------------

BEGIN

 PRVT_ADVISOR.delete_expired_tasks;

END;

/

EXIT;

Optionally, stop package AUTO_STATS_ADVISOR_TASK from running.

DECLARE

 v_tname VARCHAR2(32767);

BEGIN

 v_tname := 'AUTO_STATS_ADVISOR_TASK';

 DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

/

The Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK) can be recreated any time by running the following if DBA(s) wants to use the Statistics Advisor.

BEGIN

 DBMS_STATS.INIT_PACKAGE();

END;

/