DBAtlas · Atlas d'exploitation des bases de données

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.

Ouvrir dans DBAtlas →