Il existe de nombreuses références à des problèmes de taille du tablespace SYSAUX , dues généralement à deux raisons principales :
- Problème de purge des clichés AWR
- Problème de purge de l’historique des statistiques
La note 1399365.1 –Troubleshooting Issues with SYSAUX Usage by the Automatic Workload Repository (AWR) – du support Oracle recense pas mal de causes et de suggestions d’actions correctives selon le cas.
Dans cet article, je vais décrire un cas de croissance non contrôlée du tablespace SYSAUX due à priori à la non purge effective des grosses tables de l’historique AWR et non recensé explicitement dans la note Oracle.
Vous verrez comment purger manuellement ces tables pour récupérer rapidement de l’espace dans le tablespace.
Le symptôme
Le cas décrit ici concerne des bases en version 11.2.0.4.1 sous Linux 64 bits, il est possible que les PSU suivants apportent un correctif fiable à ce problème.
Après quelques mois de vie de la base de données, j’observe une croissance inexorable de la taille de mon tablespace SYSAUX qu’il faut impérativement arriver stopper, sur quelques bases la taille du tablespace dépasse les 8 Go.
Le contenu du tablespace est le suivant au départ (liste tronquée)
select space_usage_kbytes, occupant_name, occupant_desc, schema_name from V$SYSAUX_OCCUPANTS order by space_usage_kbytes desc;
SPACE_USAGE_KBYTES Occupant Name Occupant Desc Name ------------------ -------------- -------------------------------------------------- ------- 5962624 SM/AWR Server Manageability - Automatic Workload Repository SYS 142208 SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS 51904 SM/ADVISOR Server Manageability - Advisor Framework SYS 45632 SDO Oracle Spatial MDSYS 13824 ORDIM/ORDDATA Oracle Multimedia ORDDATA Components ORDDATA
NB : Le script ?/rdbms/admin/awrinfo.sql peut aussi être utilisé pour obtenir une vue détaillée sur ce qui concerne AWR.
On voit que les clichés AWR occupent majoritairement l’espace, les statistiques (31 jours d’historique) sont quand à elles bien purgées après vérification.
Les traces du process MMON montrent tous les jours à la même heure la présence de l’erreur suivante : ORA-06525: Length Mismatch for CHAR or RAW data
*** 2015-03-12 00:22:53.579
*** SESSION ID:(7.6985) 2015-03-12 00:22:53.579
*** CLIENT ID:() 2015-03-12 00:22:53.579
*** SERVICE NAME:(SYS$BACKGROUND) 2015-03-12 00:22:53.579
*** MODULE NAME:(MMON_SLAVE) 2015-03-12 00:22:53.579
*** ACTION NAME:(Auto-Purge Slave Action) 2015-03-12 00:22:53.579
*** KEWROCISTMTEXEC - encountered error: (ORA-06525: Length Mismatch for CHAR or RAW data
ORA-06512: at "SYS.DBMS_STATS", line 29022
ORA-06512: at line 1
)
*** SQLSTR: total-len=93, dump-len=93,
STR={begin dbms_stats.copy_table_stats('SYS', :bind1, :bind2, :bind3, flags=>1, force=>TRUE); end;}
En cherchant sur le site du support Oracle, on trouve qu’il s’agit du bug 17079301 pour lequel il existe un patch pour la version 11.2.0.4 et divers plateformes.
L’erreur à l’exécution de copy_table_stats pourrait intervenir de le problème de purge AWR ou au moment de la création des nouvelles partitions.
Ce patch ne corrige pas forcement notre problème de purge, néanmoins en l’appliquant les erreurs MMON disparaissent.
Quelques mesures
Voici deux requêtes pour caractériser le problème de purge AWR :
SELECT dbid, min(snap_id), max(snap_id) FROM SYS.WRH$_LATCH GROUP BY dbid ORDER BY 1; DBID MIN(SNAP_ID) MAX(SNAP_ID) ---------- ------------ ------------ 2337086367 1 6290
Les entrées dans la table WRH$_LATCH rattachés à aucun snap_id courant :
SELECT COUNT(*) FROM SYS.WRH$_LATCH where snap_id not in (select snap_id from SYS.WRM$_SNAPSHOT) and dbid=(select DBID from v$database); COUNT(*) ---------- 8 399 586
Comme on le voit ci dessus, il y a un problème dans la purge, le snapshot n°1 est toujours présent dans les tables alors que notre historique AWR est de 8 jours et qu’il n’existe plus , vous pouvez d’ailleurs le vérifier avec :
select * from DBA_HIST_WR_CONTROL;
et le modifier avec la procédure
dbms_workload_repository.modify_snapshot_settings()
Voici la taille du tablespace SYSAUX et celle des ses plus gros objets :
Initial Next Pct Kilo Mega Blocks Tablespaces extent extent Min Max inc Bytes Bytes Oracle --------------- -------------- ------------ ---- ------ ---- ------------ ----------- ----------- SYSAUX 65 536 1 ###### 6 291 456 6 144,00 786 432 Tablespace Taille Objet name Type (Ko) ---------------------------------------- ------------------------- --------------- ------------ SYS.WRH$_EVENT_HISTOGRAM_PK SYSAUX INDEX PARTITION 622 592 SYS.WRH$_EVENT_HISTOGRAM SYSAUX TABLE PARTITION 483 328 SYS.WRH$_LATCH SYSAUX TABLE PARTITION 401 408 SYS.WRH$_SQLSTAT SYSAUX TABLE PARTITION 393 216 SYS.WRH$_SYSSTAT_PK SYSAUX INDEX PARTITION 360 448 SYS.WRH$_LATCH_PK SYSAUX INDEX PARTITION 311 296 SYS.WRH$_SYSSTAT SYSAUX TABLE PARTITION 253 952 SYS.WRH$_PARAMETER_PK SYSAUX INDEX PARTITION 245 760 SYS.WRH$_PARAMETER SYSAUX TABLE PARTITION 204 800 SYS.WRH$_SEG_STAT SYSAUX TABLE PARTITION 172 032 SYS.WRH$_SYSTEM_EVENT SYSAUX TABLE PARTITION 164 864 SYS.WRH$_SERVICE_STAT_PK SYSAUX INDEX PARTITION 163 840 SYS.WRH$_LATCH_MISSES_SUMMARY_PK SYSAUX INDEX PARTITION 147 456 SYS.WRH$_SYSTEM_EVENT_PK SYSAUX INDEX PARTITION 139 264 SYS.WRH$_LATCH_MISSES_SUMMARY SYSAUX TABLE PARTITION 122 880 SYS.WRH$_SQLSTAT_PK SYSAUX INDEX PARTITION 106 496 SYS.WRH$_SERVICE_STAT SYSAUX TABLE PARTITION 98 304 SYS.WRH$_DLM_MISC_PK SYSAUX INDEX PARTITION 90 112 SYS.WRH$_SEG_STAT_PK SYSAUX INDEX PARTITION 90 112 SYS.WRH$_DLM_MISC SYSAUX TABLE PARTITION 81 920 SYS.WRH$_SQLSTAT_INDEX SYSAUX INDEX PARTITION 81 920 SYS.WRH$_ACTIVE_SESSION_HISTORY SYSAUX TABLE PARTITION 59 392 SYS.WRH$_SERVICE_WAIT_CLASS_PK SYSAUX INDEX PARTITION 59 392 SYS.WRH$_EVENT_HISTOGRAM_PK SYSAUX INDEX PARTITION 56 320 SYS.WRH$_ROWCACHE_SUMMARY SYSAUX TABLE PARTITION 50 176 ------------ sum 4 961 280
Les tables et index WRH$_<> sont bien les objets de l’historique AWR, la plupart des ces objets sontpartitionnés . A ce propos, certains problèmes de non purge d’AWR peuvent provenir de problèmes à la création des partitions supplémentaires.
Purge manuelle des tables WRH$
Le truncate des tables et partitions n’est pas pas recommandé, on va plutôt s’orienter vers un delete selectif des lignes orphelines dans les tables WRH$. Certaines de ces tables n’ont pas de colonne snap_id, elles sont purgées par Oracle par une mécanisme particulier, nous ne nous en préoccuperons pas.
Voici les ordres de DELETE à exécuter, certains peuvent être particulièrement longs à s’exécuter selon l’ampleur de la purge nécessaire. Vérifiez que votre tablespace UNDO à au minimum une taille de 2 Go pour que les plus gros delete puissent passer. En tout, une heure ou plus est nécessaire.
Le script suivant génére les ordres delete (sans vérifier la présence de la colonne snap_id, certains ordres ne passeront donc pas, je vous laisse améliorer le script)
spool delete_wrh.sql select 'delete from '||table_name||' where (dbid, snap_id) not in (select D.dbid, RS.snap_id from v$database D, WRM$_SNAPSHOT RS);'||CHR(10)|| 'COMMIT;' AS Ordre_SQL from dba_tables where table_name like 'WRH$%'; spool off
Récupération de l’espace
On peut maintenant récupérer les blocs libres des plus grosses tables (elles doivent avoir l’option row movmement) :
alter table SYS.WRH$_LATCH shrink space CASCADE; alter table SYS.WRH$_SQLSTAT shrink space CASCADE; alter table SYS.WRH$_SYSSTAT shrink space CASCADE; alter table SYS.WRH$_EVENT_HISTOGRAM shrink space CASCADE; alter table SYS.WRH$_PARAMETER shrink space CASCADE; alter table SYS.WRH$_SEG_STAT shrink space CASCADE; alter table SYS.WRH$_ACTIVE_SESSION_HISTORY shrink space CASCADE; alter table SYS.WRH$_SYSTEM_EVENT shrink space CASCADE; alter table SYS.WRH$_SERVICE_STAT shrink space CASCADE; alter table SYS.WRH$_LATCH_MISSES_SUMMARY shrink space CASCADE; alter table SYS.WRH$_DLM_MISC shrink space CASCADE; alter table SYS.WRH$_ROWCACHE_SUMMARY shrink space CASCADE;
Résultat : L’espace occupé par AXR est passé de 6 Go à 1 Go.
select space_usage_kbytes, occupant_name, occupant_desc, schema_name from V$SYSAUX_OCCUPANTS order by space_usage_kbytes desc; Schema SPACE_USAGE_KBYTES Occupant Name OCCUPANT_DESC Name ------------------ ----------------- ------------------------------------------------------ -------- 1050304 SM/AWR Server Manageability - Automatic Workload Repository SYS 142208 SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS 52928 SM/ADVISOR Server Manageability - Advisor Framework SYS 45632 SDO Oracle Spatial MDSYS 13824 ORDIM/ORDDATA Oracle Multimedia ORDDATA Components ORDDATA 13696 LOGMNR LogMiner SYSTEM
Du côté des snapshots, la situation est redenue normale :
SELECT dbid, min(snap_id), max(snap_id) FROM SYS.WRH$_LATCH GROUP BY dbid ORDER BY 1; DBID MIN(SNAP_ID) MAX(SNAP_ID) ---------- ------------ ------------ 2337086367 6088 6290
SELECT COUNT(*) FROM SYS.WRH$_LATCH where snap_id not in (select snap_id from SYS.WRM$_SNAPSHOT) and dbid=(select DBID from v$database);
Juste après la purge cette requête ne ramène pas de lignes mais ensuite le nombre de lignes augmente régulièrement car certains tables ne sont pas purgés par le snap_id mais à une rythme géré par Oracle.
Conclusion
Le patch ne corrige pas forcement notre problème, c’est difficile à dire, néanmoins l’erreur dans la trace MMON n’apparait plus.
Il faut tout de même purger manuellement les tables AWR pour revenir à une volumétrie cohérente, une surveillance régulière de l’évolution permettra de s’assure que le problème est maitrisé en attendant une correction dans un PSU.
