Oracle: reduce SYSAUX size (purge optimizer statistics history)
Identify SYSAUX occupants, purge the optimizer statistics history and rebuild fragmented tables and indexes.
The SYSAUX tablespace of an Oracle database can grow very large, most often because of optimizer statistics history (SM/OPTSTAT) or AWR snapshots. This page shows how to find what uses the space, purge old statistics, and reorganise the fragmented tables and indexes afterwards.
References: Oracle Support note 1563921.1 (account required), SYSAUX tablespace notes, SYSAUX: what is it for and how to clean it.
1. Find what occupies SYSAUX
set linesize 120
set pagesize 100
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "Item", space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema", move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1;
Typical output (extract), where SM/OPTSTAT is the one to watch:
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
EM .08 SYSMAN emd_maintenance.move_em_tblspc
SM/ADVISOR .02 SYS
SM/AWR .15 SYS
SM/OPTSTAT 11.44 SYS
SM/OTHER .02 SYS
XDB .00 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
The Move Procedure column gives the supported way to relocate an occupant to another tablespace.
2. Purge the optimizer statistics history
Check how long old statistics are kept, and shorten it:
select dbms_stats.get_stats_history_retention from dual;
-- keep 10 days
exec dbms_stats.alter_stats_history_retention(10);
Show the oldest statistics still available:
select dbms_stats.get_stats_history_availability from dual;
Purge old statistics. When there is a lot of data, do it in stages (SYSDATE-30, then SYSDATE-25, and so on):
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
The fastest option removes everything at once, like a truncate:
EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);
3. Check the size of the statistics history segments
Purging frees space inside the segments but does not shrink them, so look at the tables and indexes:
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type = 'TABLE'
group by segment_name, segment_type
order by 1 asc;
Repeat with segment_type = 'INDEX' for the indexes.
MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_OPR TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
88 WRI$_OPTSTAT_TAB_HISTORY TABLE
126 WRI$_OPTSTAT_IND_HISTORY TABLE
158 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
4,482 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
4. Rebuild the tables and indexes
Running the table moves makes any concurrent
gather_statsjob fail. Do it in a maintenance window.
Before rebuilding, all %OPT% indexes must be VALID:
col INDEX_NAME form a40
select di.index_name, di.index_type, di.status
from dba_indexes di, dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc;
Move the tables:
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV move tablespace sysaux;
Then rebuild the indexes (a move leaves them unusable):
Alter index SYS.I_WRI$_OPTSTAT_IND_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_OPR_STIME rebuild TABLESPACE SYSAUX;
Or generate the statements for every %OPT% object:
select 'alter table '||segment_name||' move tablespace SYSAUX parallel 8;'
from dba_segments
where tablespace_name = 'SYSAUX' and segment_name like '%OPT%' and segment_type = 'TABLE';
select 'alter index '||segment_name||' rebuild online parallel 8;'
from dba_segments
where tablespace_name = 'SYSAUX' and segment_name like '%OPT%' and segment_type = 'INDEX';
Run the check query again: every index must be VALID.
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
I_WRI$_OPTSTAT_AUX_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_HH_ST FUNCTION-BASED NORMAL VALID
WRH$_OPTIMIZER_ENV_PK NORMAL VALID
5. If the AWR repository is the problem
When the purge takes too long, the AWR repository can be dropped and recreated, which is very fast but erases all AWR history. Run as SYSDBA:
connect / as sysdba
@?/rdbms/admin/catnoawr.sql
@?/rdbms/admin/catawrtb.sql
Then reorganise the AWR (WRH$_) tables and indexes by spooling generated statements:
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 200
SET VERIFY OFF
spool rebuild_tables.sql
SELECT 'ALTER TABLE '||segment_name||' MOVE TABLESPACE SYSAUX ;'
FROM dba_segments
WHERE tablespace_name = 'SYSAUX' AND segment_name LIKE 'WRH$_%' AND segment_type = 'TABLE'
ORDER BY segment_name;
spool off
spool rebuild_indexes.sql
SELECT 'ALTER INDEX '||segment_name||' REBUILD ONLINE;'
FROM dba_segments
WHERE tablespace_name = 'SYSAUX' AND segment_name LIKE 'WRH$_%' AND segment_type = 'INDEX'
ORDER BY segment_name;
spool off
Review and run rebuild_tables.sql, then rebuild_indexes.sql.
Notes
- Moving SYS-owned tables is a sensitive operation: follow Oracle Support guidance for your version and test first.
- Dropping and recreating the AWR repository is irreversible; export the AWR data first if you need it.