LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon