Rétention de l'AWR et la taille de SYSAUX

Dernièrement, nous avons rencontré un problème de performance. Dans le but d'identifier et résoudre le problème, nous avions voulu comparer des clichés (snapshots) sur différentes périodes cependant, nous n'avons pas pu reculer assez loin dans le passé car les données de l'historique ne sont pas conservées suffisamment longtemps.

Pour éviter que cela se reproduise, nous voulons conserver une année complète d'information afin d'être en mesure de comparer des clichés (snapshots) à différentes périodes durant la dernière année.

Par défaut, les clichés (snapshots) sont collectés à toutes les heures et ils sont conservés pour une période de 7 jours.

Pour afficher la configuration actuelle :

Select extract (day from snap_interval) * 24 * 60 +
extract (hour from snap_interval) * 60 +
extract (minute from snap_interval) "Snapshot Interval",
extract (day from retention) * 24 * 60 +
extract (hour from retention) * 60 +
extract (minute from retention) "Retention Interval"
from dba_hist_wr_control;

Pour modifier ces valeurs, il suffit d’utiliser le package suivant en spécifiant les valeurs désirées :

begin
dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 365*24*60
);
end;
/

Oracle a su nous faciliter la vie pour estimer la taille du tablespace SYSAUX. Il fournit un script (utlsyxsz.sql) qui évalue la taille que devrait être le tablespace SYSAUX. Son utilisation est fort simple. L'exécution s'effectue en mode interactif et on doit tout simplement fournir certaines valeurs pendant l'exécution.

Suite à l'exécution, l'estimation me recommande de définir un tablespace d'environ 60 GB. Voici un extrait de l'exécution du script :

...
~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 60,143.6 MB
| Estimated size of AWR per instance: 20,047.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 365.00 days
| Num Instances - 3
| Active Sessions - 20.00
| Datafiles - 139
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 182.7 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 5,205
| Indexes - 10,294
| Columns - 64,429
| Partitions - 17
| Indexes on Partitions - 51
| Columns in Partitions - 136
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...

En passant. ce script prend en compte un environnement RAC. Lors de l'exécution, vous pouvez spécifier le nombre d'instance.

Maintenant, il reste à voir si l'estimation est réaliste.