1. Générer un rapport AWR sur la période critique
L'Automatic Workload Repository (AWR) est votre meilleur allié. Il capture automatiquement des snapshots du workload toutes les 60 minutes. En production, je recommande de passer à 30 minutes pour les bases critiques.
-- Lister les snapshots disponibles
select snap_id,
to_char(begin_interval_time, 'DD/MM HH24:MI') begin_time,
to_char(end_interval_time, 'DD/MM HH24:MI') end_time
from dba_hist_snapshot
order by snap_id desc
fetch first 20 rows only;Repérez le snapshot qui précède le début de l'incident et celui qui couvre la période de dégradation. Générez le rapport :
@?/rdbms/admin/awrrpt
-- Choisissez le type (HTML de préférence)
-- Entrez begin_snap et end_snap
-- Validez le nom du rapportVous obtenez un rapport HTML complet. Dirigez-vous tout de suite vers la section Top 10 Foreground Events by Total Wait Time.
2. Lire les wait events — le vrai diagnostic
Les wait events vous disent ce que la base attend vraiment. Ne vous dispersez pas sur les autres sections avant d'avoir identifié le goulot principal.
| Wait Event | Cause probable |
|---|---|
log file sync |
Commit trop fréquents ou I/O redo log lent |
read by other session |
Contention sur les blocs — plusieurs sessions lisent le même bloc |
buffer busy waits |
Contention sur un bloc chaud (hot block) |
db file sequential read |
I/O index — généralement normal si pas en tête |
db file scattered read |
Full table scan — requête non indexée |
enq: TX - row lock contention |
Blocage applicatif — une transaction bloque les autres |
enq: TM - contention |
Verrou DDL sur une table |
Règle empirique : si un seul wait event représente plus de 40% du temps d'attente total, c'est lui qu'il faut attaquer en priorité.
3. Identifier le top SQL
Une fois le wait event identifié, descendez à la section SQL ordered by Elapsed Time. Les requêtes les plus consommatrices en temps sont listées avec leur SQL_ID. Prenez le premier.
Pour obtenir le plan d'exécution et les statistiques détaillées :
define begin_snap = <snap_debut>
define end_snap = <snap_fin>
define sql_id = '<sql_id>'
define report_name = rapport_sql_probleme
@?/rdbms/admin/awrsqrptCe rapport vous montre le plan d'exécution, les statistiques par exécution, et l'évolution des performances entre les deux snapshots. Regardez en particulier le nombre d'exécutions et le temps par exécution.
4. Creuser avec Statspack (sans licence Diagnostic Pack)
Vous n'avez pas le Diagnostic Pack Oracle ? Statspack est l'outil gratuit fourni avec Oracle Database. Il fait le même travail qu'AWR sans licence supplémentaire. Je l'utilise encore régulièrement chez des clients qui n'ont pas souscrit aux options de tuning Oracle.
Planifier les snapshots Statspack
-- Exécution automatique toutes les 15 minutes
dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+15/1440,'MI'),
'trunc(SYSDATE+15/1440,''MI'')',
TRUE, :instno);Référence des intervalles courants :
| Intervalle | Expression |
|---|---|
| Toutes les heures | sysdate+1/24 |
| Toutes les 30 min | sysdate+1/48 |
| Toutes les 15 min | sysdate+15/1440 |
| Toutes les 10 min | sysdate+10/1440 |
Générer un rapport Statspack
-- Lister les snapshots
select snap_id, snap_time, snap_level
from stats$snapshot order by 2;
-- Générer le rapport
@?/rdbms/admin/spreportRequêtes avancées sous Statspack
Pour aller plus loin, Statspack stocke les données dans des tables STATS$* que vous pouvez interroger directement :
-- Vérifier un paramètre qui a changé
select * from STATS$PARAMETER
where name='audit_trail'
order by SNAP_ID;
-- Suivre l'évolution d'une ressource
select * from stats$resource_limit
where resource_name='processes'
order by snap_id;
-- Récupérer le SQL complet depuis son hash value
select sql_text
from STATS$SQLTEXT
where old_hash_value=&hash_value
order by piece asc;5. Le plan d'exécution — validation finale
Une fois le SQL identifié, validez son plan d'exécution. Un changement de plan (plan_hash_value différent) est une cause fréquente et soudaine de dégradation.
-- Voir les plans utilisés par une requête
select plan_hash_value, count(*)
from stats$sql_plan_usage
where old_hash_value = &hash_value
group by plan_hash_value;
-- Afficher le plan
select * from stats$sql_plan
where plan_hash_value = &phv;Si le plan a changé, les causes possibles sont : statistiques obsolètes, modification du paramètre optimizer_mode, ou apparition d'un SQL profile non souhaité.
Checkbook en 10 minutes
- Min 0-2 — Générer le rapport AWR ou Statspack sur la période
- Min 2-4 — Identifier le wait event principal (>40%)
- Min 4-6 — Descendre dans le Top SQL, noter le SQL_ID
- Min 6-8 — Vérifier le plan d'exécution et son historique
- Min 8-10 — Rédiger le diagnostic et les recommandations
Ce workflow n'est pas théorique — c'est celui que j'applique depuis plus de 20 ans sur des bases allant de quelques Go à plusieurs To, dans des contextes exigeants (banque, santé, collectivités). Il a fait ses preuves.
À retenir : ne partez pas dans tous les sens. Lisez d'abord les wait events. Le reste découle de ce diagnostic.