After upgrading Oracle database to 12cR2 SYSAUX table-space was noticed to be exponentially increasing space and table SYS. WRI$_ADV_OBJECTS was largest !

Why is that ?

In Oracle 12.2 a new feature “Optimizer Statistics Advisor” [AUTO_STATS_ADVISOR_TASK] runs huge number of times causing SYSAUX tablespace to grow

SOLUTION:

Using the following query I will calculate the number of rows in table SYS. WRI$_ADV_OBJECTS:

SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=’AUTO_STATS_ADVISOR_TASK’);

  COUNT(*)

———-

  28251381

Migrate the rows from the current table to a new table

SQL> 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’);

Table created.

Then truncate the original table:

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

Table truncated.

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

20703 rows created.

SQL> commit;

Commit complete.

SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

Index altered.

SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

Index altered.

SQL> drop table WRI$_ADV_OBJECTS_NEW purge;

Table dropped.

Drop the statistics advisor task from dictionary to refrain from executing:

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘AUTO_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.

SQL> DECLARE

filter1 CLOB;

BEGIN

filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(‘AUTO_STATS_ADVISOR_TASK’,NULL,NULL,’DISABLE’);

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.