Contexte

Oracle met à disposition un outil nommé Automatic Worload Repository (AWR) qui, comme son nom l’indique, collecte des informations sur l’utilisation et les performances de la base. Cet outil est très utile pour réaliser un audit de performance, en revanche pour utiliser AWR vous devez disposer du pack « Diagnostic Pack » soumis à licence. Pour palier ce problème, vous pouvez utiliser Statspack, l’ancêtre d’AWR, un outil gratuit.
En prévention d’un éventuel problème de performance, vous avez mis en place un job permettant de prendre, à intervalles réguliers, des clichés Statspack. Pour ne pas saturer vos disques, vous avez positionné des purges régulières avec la procédure classique (STATSPACK.PURGE(inum_days)) fournie par Statspack avec une rétention de 14 jours.
Après plusieurs semaines, vous vous apercevez que la volumétrie des objets appartenant au schéma PERFSTAT ne cesse de croître.

La table STATS$SQLTEXT ne se purge pas

En approfondissant, vous remarquez que la table STATS$SQLTEXT occupe une bonne partie de la volumétrie du schéma. Cette table est alimentée à partir du niveau des clichés Statspack 5 (capture SQL, …).
Effectivement, la note Metalink « Running SPPURGE.SQL does not clean up stats$sqltext(Doc ID 233866.1) » nous indique que le script SPPURGE.SQL ne permet pas de nettoyer la table STATS$SQLTEXT. Ce script se base sur la procédure STATSPACK.PURGE.

Comment contourner ce problème ?

Si vous n’agissez pas, l’occupation de Statspack dans votre base ne cessera pas de grandir.

1^ère méthode :

Suivre la note Metalink « How To Automate Purging of Statspack Snapshots (Doc ID 464214.1) » pour créer un nouveau package contenant une procédure incluant la suppression des enregistrements de la table STATS$SQLTEXT. Cette procédure se substitue à la procédure STATSPACK.PURGE.
La note propose de planifier un job avec le package DBMS_JOB pour qu’il exécute cette procédure permettant de purger les clichés Statspack ainsi que la table STATS$SQL_TEXT.

2^ème méthode :

Appeler la procédure STATSPACK.PURGE avec le paramètre i_extended_purge à la valeur TRUE. Cette procédure supprimera toutes les lignes qui ne sont pas liées par une contrainte à la table stats$snapshot.

procedure PURGE
      ( i_num_days        IN  number
      , i_extended_purge  IN  boolean default FALSE
      , i_dbid            IN  number  default null
      , i_instance_number IN  number  default null
      );

Notez que la valeur par défaut de ce paramètre est à FALSE.

Extrait code de la procédure :

if iextended_purge then
--
-- Purge segments which do not have RI constraints to stats$snapshot
-- SQL text
delete from stats$sqltext st1
where (st1.old_hash_value, st1.text_subset) in
(select /*+ index_ffs(st) */
st.old_hash_value, st.text_subset
from stats$sqltext st
where (old_hash_value, text_subset) not in
(select /*+ hash_aj index_ffs(ss) */
distinct old_hash_value, text_subset
from stats$sql_summary ss
)
and st.piece = 0
);
-- SQL execution plans
delete from stats$sql_plan sp1
where sp1.plan_hash_value in
(select /*+ index_ffs(sp) */
sp.plan_hash_value
from stats$sql_plan sp
where plan_hash_value not in
(select /*+ hash_aj */
distinct plan_hash_value
from stats$sql_plan_usage spu
)
and sp.id = 0
);
-- Segment Identifiers
delete /*+ index_ffs(sso) */
from stats$seg_stat_obj sso
where (dbid, dataobj#, obj#, ts#) not in
(select /*+ hash_aj full(ss) */
dbid, dataobj#, obj#, ts#
from stats$seg_stat ss
);
end if; -- extended purge

Conclusion

La première méthode cible en particulier la table STATS$SQL_TEXT qui n’est pas purgée par défaut dans la procédure STATSPACK.PURGE. Cette méthode consomme moins de ressource car elle exécute un Commit à chaque ligne supprimée.
La seconde, elle, est plus complète (purge des tables stats$sql_plan, stats$seg_stat_obj) mais plus longue et plus consommatrice à l’exécution. Si la procédure de purge est exécutée régulièrement, elle est à privilégier.