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:
WRH$_EVENT_HISTOGRAM:
Estimate sysaux size:
Below script can be used to estimate the size of sysaux based on the number of active sessions, tables , retention and interval
kIsH@Xhydra<>@?/rdbms/admin/utlsyxsz
This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: sysaux.es
Using the report name sysaux.es
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Estimated at
17:18:07 on Feb 16, 2023 ( Thursday ) in Timezone +05:30
DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
----------- ---------------------------------------- ----- ----------------- ---
* DB9ZX xhydra - Linux x86 64-bit 1 16:03:05 (02/16) NO
~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 1,088.8 MB
|
| Total size of SM/AWR 167.3 MB ( 15.4% of SYSAUX )
| Total size of SM/OPTSTAT 107.6 MB ( 9.9% of SYSAUX )
| Total size of SDO 231.8 MB ( 21.3% of SYSAUX )
| Total size of AUDSYS 82.9 MB ( 7.6% of SYSAUX )
| Total size of XDB 68.4 MB ( 6.3% of SYSAUX )
| Total size of SM/OTHER 65.9 MB ( 6.1% of SYSAUX )
| Total size of AO 45.8 MB ( 4.2% of SYSAUX )
| Total size of SM/ADVISOR 26.7 MB ( 2.5% of SYSAUX )
| Total size of LOGMNR 10.8 MB ( 1.0% of SYSAUX )
| Total size of SMON_SCN_TIME 10.5 MB ( 1.0% of SYSAUX )
| Total size of WM 6.6 MB ( 0.6% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE 3.7 MB ( 0.3% of SYSAUX )
| Total size of JOB_SCHEDULER 3.0 MB ( 0.3% of SYSAUX )
| Total size of PL/SCOPE 2.8 MB ( 0.3% of SYSAUX )
| Total size of TEXT 2.8 MB ( 0.3% of SYSAUX )
| Total size of STREAMS 1.7 MB ( 0.2% of SYSAUX )
| Total size of LOGSTDBY 1.6 MB ( 0.1% of SYSAUX )
| Total size of EM_MONITORING_USER 0.7 MB ( 0.1% of SYSAUX )
| Total size of AUTO_TASK 0.6 MB ( 0.1% of SYSAUX )
| Total size of Others 247.6 MB ( 22.7% of SYSAUX )
|
~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~
| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
| - Interval Setting (minutes)
| - Retention Setting (days)
| - Number of Instances
| - Average Number of Active Sessions
| - Number of Datafiles
|
| For 'Interval Setting',
| Press <return> to use the current value: 60.0 minutes
| otherwise enter an alternative
|
Enter value for interval:
** Value for 'Interval Setting': 60
|
| For 'Retention Setting',
| Press <return> to use the current value: 8.00 days
| otherwise enter an alternative
|
Enter value for retention:
** Value for 'Retention Setting': 8
|
| For 'Number of Instances',
| Press <return> to use the current value: 1.00
| otherwise enter an alternative
|
Enter value for num_instances:
** Value for 'Number of Instances': 1
|
| For 'Average Number of Active Sessions',
| Press <return> to use the current value: 12.0
| otherwise enter an alternative
|
Enter value for active_sessions:
** Value for 'Average Number of Active Sessions': 12
| ***************************************************
| Estimated size of AWR: 297.6 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 8.00 days
| Num Instances - 1
| Active Sessions - 12.00
| Datafiles - 12
| ***************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
| - Number of Tables in the Database
| - Number of Partitions in the Database
| - Statistics Retention Period (days)
| - DML Activity in the Database (level)
|
| For 'Number of Tables',
| Press <return> to use the current value: 191.0
| otherwise enter an alternative <a positive integer>
|
Enter value for number_of_tables:
** Value for 'Number of Tables': 191
|
| For 'Number of Partitions',
| Press <return> to use the current value: 11.0
| otherwise enter an alternative <a positive integer>
|
Enter value for number_of_partitions:
** Value for 'Number of Partitions': 11
|
| For 'Statistics Retention',
| Press <return> to use the current value: 31.0 days
| otherwise enter an alternative <a positive integer>
|
Enter value for stats_retention:
** Value for 'Statistics Retention': 31
|
| For 'DML Activity',
| Press <return> to use the current value: 2 <medium>
| otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity:
** Value for 'DML Activity': 2
| ***************************************************
| Estimated size of Stats history 42.3 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 191
| Indexes - 279
| Columns - 1,466
| Partitions - 11
| Indexes on Partitions - 27
| Columns in Partitions - 962
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************
~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 297.6 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 8.00 days
| Num Instances - 1
| Active Sessions - 12.00
| Datafiles - 12
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 42.3 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 191
| Indexes - 279
| Columns - 1,466
| Partitions - 11
| Indexes on Partitions - 27
| Columns in Partitions - 962
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| For all the other components, the estimate
| is equal to the current space usage of
| the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************
| Est size of SDO 231.8 MB
| Est size of AUDSYS 82.9 MB
| Est size of XDB 68.4 MB
| Est size of SM/OTHER 65.9 MB
| Est size of AO 45.8 MB
| Est size of SM/ADVISOR 26.7 MB
| Est size of LOGMNR 10.8 MB
| Est size of SMON_SCN_TIME 10.5 MB
| Est size of WM 6.6 MB
| Est size of SQL_MANAGEMENT_BASE 3.7 MB
| Est size of JOB_SCHEDULER 3.0 MB
| Est size of PL/SCOPE 2.8 MB
| Est size of TEXT 2.8 MB
| Est size of STREAMS 1.7 MB
| Est size of LOGSTDBY 1.6 MB
| Est size of EM_MONITORING_USER 0.7 MB
| Est size of AUTO_TASK 0.6 MB
| Est size of Others 247.6 MB
| Est size of SM/AWR 297.6 MB
| Est size of SM/OPTSTAT 42.3 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 1,153.8 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************
End of Report