Oracle : réduire la taille de SYSAUX (purge de l'historique des statistiques)
Identifier les occupants de SYSAUX, purger l'historique des statistiques et reconstruire les tables et index fragmentés.
Le tablespace SYSAUX d'une base Oracle peut devenir très volumineux, le plus souvent à cause de l'historique des statistiques de l'optimiseur (SM/OPTSTAT) ou des snapshots AWR. Cette page montre comment identifier ce qui consomme l'espace, purger les anciennes statistiques, puis réorganiser les tables et index fragmentés.
Références : note Oracle Support 1563921.1 (compte requis), notes sur SYSAUX, SYSAUX : à quoi il sert et comment le nettoyer.
1. Trouver ce qui occupe 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;
Résultat typique (extrait), où SM/OPTSTAT est celui à surveiller :
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
La colonne Move Procedure indique la méthode supportée pour déplacer un occupant vers un autre tablespace.
2. Purger l'historique des statistiques de l'optimiseur
Vérifier la durée de conservation des anciennes statistiques, puis la réduire :
select dbms_stats.get_stats_history_retention from dual;
-- conserver 10 jours
exec dbms_stats.alter_stats_history_retention(10);
Afficher les statistiques les plus anciennes encore disponibles :
select dbms_stats.get_stats_history_availability from dual;
Purger les anciennes statistiques. S'il y a beaucoup de données, procédez par étapes (SYSDATE-30, puis SYSDATE-25, etc.) :
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
L'option la plus rapide supprime tout d'un coup, comme un truncate :
EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);
3. Vérifier la taille des segments de l'historique
La purge libère de l'espace dans les segments mais ne les réduit pas : regardez les tables et les index.
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;
Recommencez avec segment_type = 'INDEX' pour les index.
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. Reconstruire les tables et les index
Le déplacement des tables fait échouer tout job
gather_statsen cours. Faites-le pendant une fenêtre de maintenance.
Avant la reconstruction, tous les index %OPT% doivent être 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;
Déplacer les 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;
Puis reconstruire les index (le déplacement les rend inutilisables) :
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;
Ou générer les instructions pour tous les objets %OPT% :
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';
Relancez la requête de contrôle : tous les index doivent être 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. Si le problème vient du référentiel AWR
Quand la purge est trop longue, on peut supprimer et recréer le référentiel AWR : c'est très rapide mais cela efface tout l'historique AWR. À exécuter en SYSDBA :
connect / as sysdba
@?/rdbms/admin/catnoawr.sql
@?/rdbms/admin/catawrtb.sql
Réorganisez ensuite les tables et index AWR (WRH$_) en générant les instructions dans un fichier :
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
Relisez puis exécutez rebuild_tables.sql, ensuite rebuild_indexes.sql.
Remarques
- Déplacer des tables appartenant à SYS est une opération sensible : suivez les recommandations du support Oracle pour votre version et testez d'abord.
- Supprimer et recréer le référentiel AWR est irréversible ; exportez d'abord les données AWR si vous en avez besoin.