Requête lente : diagnostic approfondi
Une requête ou tout le système est lent. Affinez étape par étape : exécutez un contrôle à chaque nœud et choisissez la branche selon le résultat. Vous aboutissez à une cause racine + correction.
Qu'est-ce qui est lent ?
Une ou quelques requêtes précises sont-elles lentes, ou tout le système ? Cela oriente l'analyse vers l'une de deux pistes : « centrée sur la requête » ou « centrée sur les ressources/le goulot ».
- Une ou quelques requêtes précises sont lentes
- Tout le système est lent
La requête s'exécute-t-elle en ce moment ?
S'agit-il d'une requête en cours/bloquée en ce moment, ou d'une requête « généralement lente » ? Regardez les sessions actives.
SELECT sid, serial#, sql_id, last_call_et AS running_sec, event, blocking_session
FROM v$session WHERE status='ACTIVE' AND type='USER' ORDER BY last_call_et DESC;- En cours / bloquée en ce moment
- Généralement lente ; je veux examiner son plan
La requête attend-elle quelque chose ?
Si elle est active mais ne progresse pas, elle attend très probablement. blocking_session / wait_event est-il renseigné ?
SELECT sid, blocking_session, event, seconds_in_wait
FROM v$session WHERE sid=&target_sid;- Bloquée par une autre session
- En attente d'une ressource (IO / journal / latch)
- Aucune attente ; consomme activement du CPU
Quelle attente domine ?
L'événement d'attente dominant indique le type de goulot.
SELECT event, total_waits, time_waited FROM v$session_event WHERE sid=&target_sid ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;- Attente IO (db file read / PAGEIOLATCH)
- Attente journal / commit (log file sync / WRITELOG)
- Verrou / enqueue (enq: TX / LCK)
Obtenir le plan d'exécution (EXPLAIN)
Récupérez le plan. Y a-t-il un full table scan, un ordre de jointure inattendu, ou un écart entre lignes estimées et réelles ?
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));- Full scan / index manquant
- C'était rapide hier ; le plan a changé soudainement
- Traite trop de lignes / N+1
- Le plan semble bon mais c'est lent
Les statistiques sont-elles à jour ?
Un grand écart entre lignes estimées et réelles (E-rows vs A-rows) est le signe de statistiques obsolètes.
SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name='&TABLE_NAME';- Les statistiques sont obsolètes / anciennes
- Les statistiques sont à jour mais c'est toujours lent
À l'échelle du système : où est le goulot ?
Si tout est lent plutôt qu'une seule requête, cherchez un goulot de ressources : CPU, IO, mémoire, ou blocages généralisés ?
SELECT metric_name, value FROM v$sysmetric WHERE metric_name IN ('Host CPU Utilization (%)','Average Active Sessions') AND intsize_csec=(SELECT MAX(intsize_csec) FROM v$sysmetric);- CPU proche de 100 %
- Attente IO élevée
- Pression mémoire / faible taux de succès du cache
- Nombreux blocages
Cause racine : blocage
La requête attend un verrou détenu par une autre session. Trouvez la session en tête de chaîne (root blocker) et résolvez-la.
Cause racine : goulot IO
La requête lit beaucoup sur disque. Généralement à cause de full scans (index manquant) ou d'une mémoire insuffisante (buffer cache).
Cause racine : goulot commit / journal
Si 'log file sync' / 'WRITELOG' domine, les commits attendent les écritures redo/WAL.
Cause racine : index manquant
Le plan effectue un full scan. Un index adapté sur les colonnes de filtre/jointure apporte un gain important.
Cause racine : régression de plan
La requête était rapide hier mais pas aujourd'hui ; le plan a changé. Généralement des statistiques obsolètes ou un changement de paramètre.
Cause racine : conception de la requête
Le plan est raisonnable mais la requête effectue intrinsèquement trop de travail (trop de lignes, N+1, jointures inutiles).
Cause racine : statistiques obsolètes
L'optimiseur fait de mauvaises estimations car les statistiques ne sont pas à jour.
Analyse approfondie
Si le plan et les statistiques sont corrects mais que c'est toujours lent, creusez davantage.
Cause racine : goulot CPU
Tout le système est limité par le CPU. Généralement, quelques requêtes mal planifiées consomment le CPU.
Cause racine : pression mémoire
Faible taux de succès du cache et/ou swap : la mémoire est insuffisante ou mal répartie.