Purge des tables AWR

Il existe un problème de purge sur certaines tables AWR. Cela a été constaté sur les versions 11. Cela entraîne une augmentation importante du tablespace SYSAUX. Les tables suivantes ont été identifiées :

• sys.wrh$_event_histogram
• sys.wrh$_active_session_history

En cas de taille importante du tablespace SYSAUX, on pourra vérifier avec la requête suivante les segments les plus volumineux et confirmer ainsi que ce sont bien les tables listées ci-dessus qui sont consommatrices.

SQL> select owner,segment_name, segment_type, sum(bytes)/1024/1024 from dba_segments where TABLESPACE_NAME='SYSAUX' group by segment_name, segment_type, owner order by 4;

La procédure pour purger ces tables est la suivante (exemple avec sys.wrh$_event_histogram)  :

1/ Arrêt de la collecte AWR

Dans une configuration RAC, la commande n’est à passer que sur un seul noeud.

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 0); 

2/ Sauvegarde des lignes non orphelines dans une table temporaire (sys.wrh$_event_histogram_save)

Pour rappel, le nom de la table ne doit pas dépasser 30 caractères.

SQL> create table sys.wrh$_event_histogram_save tablespace SYSAUX as
select * from sys.WRH$_EVENT_HISTOGRAM
where (dbid,instance_number,snap_id) in (select dbid,instance_number,snap_id from dba_hist_snapshot); 

ATTENTION : Penser à surveiller la volumétrie d’archivelogs. Le nombre de lignes déplacées peut-être estimé ainsi :

SQL> select count(*)
from sys.wrh$_event_histogram
where (dbid,instance_number,snap_id) in (select dbid,instance_number,snap_id from dba_hist_snapshot);

3/ Truncate de la table sys.wrh$_event_histogram

SQL> truncate table sys.wrh$_event_histogram drop storage;

4/ Réinsertion des lignes sauvegardées dans la table wrh$_event_histogram

SQL> insert /*+ APPEND */ into sys.wrh$_event_histogram
select * from sys.wrh$_event_histogram_save;

SQL> commit;

5/ Calcul des statistiques de la table wrh$_event_histogram

SQL> EXEC DBMS_STATS.gather_table_stats('SYS', 'WRH$_EVENT_HISTOGRAM', 
cascade => TRUE); 

6/ Réactivation de la collecte AWR

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 60);

7/ Suppression de la table temporaire

SQL> drop table sys.wrh$_event_histogram_save;