SYSAUX TABLESPACE GROWTH ISSUE ORACLE

SYSAUX” the son of the “SYSTEM” tablespace plays a key role in accomodating oracle performance data very effectively. SYSAUX is capable of auto purging the objects with the help of MMON processes which is big boon for DBA’s in administering the performance snapshots. But in some exceptional cases, either due to bug or due to multiple features enabled in the database, “MMON” the purger donot have sufficient time to purge large or LOB objects with in the maintanence window and this leads the DBA’s to take additional care of SYSAUX tablespace and its data.

GOOD PRACTICES:

  • Always think twice regarding the necessity of any features before the planning retention for AWR and STATS
  • Upgrading to newer versions may release new advisor or stats or AWR features which may eat up the sysaux space. So if any of the jobs are not really necessary, then disable those jobs
  • Set SYSAUX autoextend to OFF to reuse the existing space rather than extending the space. Also the growth history of each component will be difficult to track with autoextend ON due to dynamicity.
  • Check if Statistics level parameter has value “ALL” . If so, then change it to “Typical” . This is because oracle collects granular level statistics in ‘ALL’ (Beast mode 😉 )mode at every corner of database and search for space to store them
  • Check for version specific advisors, baselines and sql tuning sets and declutter them if not required

If you are in standard edition, then awrinfo report is not possible. So manually the occupants needs to be checked and analyzed

Check the occupants which occupy huge size in the sysaux tablespace first to understand the culprit

col OCCUPANT_NAME for a20
col OCCUPANT_DESC for a40
select *
            from
(
select OCCUPANT_NAME,
            OCCUPANT_DESC,
             SPACE_USAGE_KBYTES/1048576 OCCUPIED_MB
from
v$sysaux_occupants
order by 3 desc
)
where rownum < 6;

Check the top segments which occupy space in sysaux

col SEGMENT_NAME for a20
select * from
(select segment_name,
             bytes/1073741824 GB,
             segment_type
from
dba_segments
where tablespace_name='SYSAUX'
order by 2 desc)
where rownum < 6;

Basically there are by default 32 SYSAUX components. Out of these 32 , usually SM/AWR, SM/ADVISOR or SM/OPTSTAT will be top contributors.

SQL> select occupant_name from v$sysaux_occupants;

OCCUPANT_NAME
----------------------------------------------------------------
LOGMNR
LOGSTDBY
SMON_SCN_TIME
AUDSYS
PL/SCOPE
STREAMS
AUDIT_TABLES
XDB
AO
XSOQHIST
XSAMD
SM/AWR
SM/ADVISOR
SM/OPTSTAT
SM/OTHER
STATSPACK
SDO
WM
ORDIM
ORDIM/ORDDATA
ORDIM/ORDPLUGINS
ORDIM/SI_INFORMTN_SCHEMA
EM
TEXT
ULTRASEARCH
ULTRASEARCH_DEMO_USER
EXPRESSION_FILTER
EM_MONITORING_USER
TSM
SQL_MANAGEMENT_BASE
AUTO_TASK
JOB_SCHEDULER

Check for default retention of AWR and OPTSTATS. More the data, more space is needed and so the negative impacts. There is a saying “Too much of anything is good for nothing” but not always. So its always better to think about the performance requirements to decide a better retention

SQL> select extract(day from retention) "days" from dba_hist_wr_control;

      days
----------
         8

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

If you are licensed with enterprise edition, then AWRINFO report shows a lot of valuable information about the SYSAUX

kish@exdbx<>@?/rdbms/admin/awrinfo

In this report, firstly look for the schema which is on top 3. If the schema is non SYS or non Oracle user, then it is a hint that the space issue is specific to the objects generated by that user. Also look if “AUTOEXTEND” status is OFF. If it is ON, then switch it off.

kish@exdbx<>select file_name,autoextensible from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME            AUT
-------------------- ---
+DATA/exdbx/datafile YES
/sysaux.257.10689095
75

kish@exdbx<>alter database datafile '+DATA/exdbx/datafile/sysaux.257.1068909575' autoextend off;

Database altered.
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                      1,014.7 MB ( 3% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS          occupies            594.9 MB (  58.6% )
| Schema  XDB          occupies            127.1 MB (  12.5% )
| Schema  SYSMAN       occupies             84.6 MB (   8.3% )

Look at top contributors of sysaux occupation

Mostly, SM/AWR will be on top of the sysaux occupants and this becomes a problem in day to day life of DBA.

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/AWR               SYS                          440.6 MB
| XDB                  XDB                          127.1 MB
| EM                   SYSMAN                        84.6 MB
| SDO                  MDSYS                         74.3 MB
| SM/OPTSTAT           SYS                           60.4 MB

There are basically two components in SYSAUX occupant

  • AWR Components – Related to AWR performance tables
  • non-AWR components – Related to all other statistics table etc..
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED          30.0 WRH$_LATCH.WRH$_LATCH_577612197_0                             -  93%  TABLE PARTITION
FIXED          27.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_577612197_0                       -  79%  INDEX PARTITION
FIXED          23.0 WRH$_LATCH_PK.WRH$_LATCH_577612197_0                          -  80%  INDEX PARTITION
FIXED          20.0 WRH$_SYSSTAT.WRH$_SYSSTA_577612197_0                          -  90%  TABLE PARTITION
FIXED          17.0 WRH$_PARAMETER_PK.WRH$_PARAME_577612197_0                     -  71%  INDEX PARTITION

Non AWR components

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR        54.2 XDB.SYS_LOB0000069708C00025$$                                         LOBSEGMENT
NON_AWR        26.0 MDSYS.SYS_LOB0000075748C00006$$                                       LOBSEGMENT
NON_AWR        22.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                    INDEX
NON_AWR        16.1 SYS.SYS_LOB0000077623C00004$$                                         LOBSEGMENT
NON_AWR        14.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY                                      TABLE

WRH$_SQL_PLAN growth:

Optimizer plans history are stored in LOB tables which occupy ample space in SYSAUX. If MMON cannot purge the orphaned rows from the LOB, then LOB should be manually truncated to reset the high watermark and release space from SYSAUX.

kish@exdbx<>select owner,
  2                table_name,
  3                index_name,
  4                partitioned
  5  from dba_lobs
  6  where table_name like '%WRH$_SQL_PLAN%';

OWNER                          TABLE_NAME                     INDEX_NAME                     PAR
------------------------------ ------------------------------ ------------------------------ ---
SYS                            WRH$_SQL_PLAN                  SYS_IL0000006417C00038$$       NO

Below is truncate method which can be used to clean up sysaux

Before performing the steps , analyze the table for corruptions

kish@exdbx<>analyze table WRH$_SQL_PLAN validate structure cascade;

Table analyzed.
SQL> select count(*) from wrh$_sql_plan;

  COUNT(*)
----------
     21774

SQL> select bytes/1048576 MB 
from dba_segments 
where segment_name='WRH$_SQL_PLAN';

        MB
----------
         6

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0);

PL/SQL procedure successfully completed.

SQL> create table sys.WRH$_SQL_PLAN_BKP tablespace SYSAUX
  2  as
  3  select *
  4  from sys.WRH$_SQL_PLAN
  5  where (dbid,snap_id) in (select dbid,snap_id from dba_hist_snapshot);

Table created.

SQL> truncate table sys.WRH$_SQL_PLAN;

Table truncated.

SQL> insert /*+APPEND PARALLEL(4)*/ into sys.WRH$_SQL_PLAN
  2  select * from sys.WRH$_SQL_PLAN_BKP;

1769 rows created.

SQL> commit;

Commit complete.

SQL> drop table sys.WRH$_SQL_PLAN_BKP;

Table dropped.

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60);

PL/SQL procedure successfully completed.

SQL> select bytes/1048576 MB from dba_segments where segment_name='WRH$_SQL_PLAN';

        MB
----------
     .6875

SQL> select count(*) from wrh$_sql_plan;

  COUNT(*)
----------
      1769

OPTSTAT:

For opstat components below recommendations should be followed

  • statistics_level string TYPICAL
  • Reduce the retention
  • control_management_pack_access=’NONE’

LATCH:

Leave a Reply