ZDLRA: SM/ADVISOR using more space in SYSAUX (Doc ID 2730556.1)

Last updated on JANUARY 08, 2021

Applies to:

Zero Data Loss Recovery Appliance Software - Version 19.2.1.1.1 to 19.2.1.1.1
Information in this document applies to any platform.

Symptoms

Percent of space used in SYSAUX Tablespace is greater than 90%

SQL> connect <UserName>/<Password> as sysdba
SQL> SELECT m.tablespace_name,ROUND((m.tablespace_size)*t.block_size/1024/1024, 3) total_used,
         ROUND((m.used_space)*t.block_size/1024/1024, 3) mb_used, ROUND((m.tablespace_size - m.used_space)*t.block_size/1024/1024, 3) mb_free, ROUND(m.used_percent, 2)
       FROM dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p
       WHERE p.name='statistics_level' and p.value!='BASIC' and t.contents NOT IN ('TEMPORARY', 'UNDO') and t.tablespace_name = m.tablespace_name and t.tablespace_name ='SYSAUX' ;

The SM/ADVISOR is using more space.

SQL> connect <UserName>/<Password> as sysdba
SQL> SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000
SQL> COLUMN OCCUPANT_NAME FORMAT A30
SQL> SELECT occupant_name, space_usage_kbytes
FROM v$sysaux_occupants
ORDER BY space_usage_kbytes DESC;

The value of AUTO_STATS_ADVISOR_TASK is 30 days for AUTO_STATS_ADVISOR_TASK task :

SQL> connect <UserName>/<Password> as sysdba
SQL> SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000
SQL> COLUMN TASK_NAME FORMAT A30
SQL> COLUMN PARAMETER_NAME FORMAT A30
SQL> COLUMN PARAMETER_VALUE FORMAT A30
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';

The task with higher count is AUTO_STATS_ADVISOR_TASK

SQL> connect <UserName>/<Password> as sysdba
SQL> SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000
SQL> COLUMN TASK_NAME FORMAT A30
SQL> SELECT task_name, count(*) CNT
FROM DBA_ADVISOR_OBJECTS
GROUP BY TASK_NAME ORDER BY CNT DESC;