LLI Wiki — My Digital Knowledge Base

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_stats job 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

Mastodon LLIMastodon SocialFosstodon