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.1Information 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' ;
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;
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';
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;
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;
- The count on WRI$_ADV_OBJECTS is huge (millions).
Note: This query can take some minutes.
Changes
Upgrading ZDLRA to version 19.2
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |