Langsame Abfrage: tiefgehende Diagnose
Eine Abfrage oder das ganze System ist langsam. Grenze es Schritt für Schritt ein: Führe an jedem Knoten eine Prüfung aus und wähle den Zweig anhand des Ergebnisses. Am Ende stehen Ursache + Lösung.
Was ist langsam?
Sind eine oder wenige bestimmte Abfragen langsam oder das ganze System? Das teilt die Untersuchung in einen von zwei Pfaden: 'abfragebezogen' oder 'ressourcen-/engpassbezogen'.
- Eine oder wenige bestimmte Abfragen sind langsam
- Das ganze System ist langsam
Läuft die Abfrage gerade?
Läuft/hängt die Abfrage gerade, oder ist sie 'generell langsam'? Sieh dir die aktiven Sessions an.
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;- Läuft / hängt gerade
- Generell langsam; ich will den Plan ansehen
Wartet die Abfrage auf etwas?
Ist sie aktiv, kommt aber nicht voran, wartet sie höchstwahrscheinlich. Ist blocking_session / wait_event gefüllt?
SELECT sid, blocking_session, event, seconds_in_wait
FROM v$session WHERE sid=&target_sid;- Von einer anderen Session blockiert
- Wartet auf eine Ressource (IO / Log / Latch)
- Kein Wait; verbraucht aktiv CPU
Welcher Wait dominiert?
Das dominierende Wait Event zeigt die Art des Engpasses.
SELECT event, total_waits, time_waited FROM v$session_event WHERE sid=&target_sid ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;- IO-Wait (db file read / PAGEIOLATCH)
- Log-/Commit-Wait (log file sync / WRITELOG)
- Sperre / Enqueue (enq: TX / LCK)
Ausführungsplan abrufen (EXPLAIN)
Hol den Plan. Gibt es einen Full Table Scan, eine unerwartete Join-Reihenfolge oder eine Abweichung zwischen geschätzten und tatsächlichen Zeilen?
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));- Full Scan / fehlender Index
- Gestern war sie schnell; der Plan hat sich plötzlich geändert
- Verarbeitet zu viele Zeilen / N+1
- Der Plan sieht gut aus, aber sie ist langsam
Sind die Statistiken aktuell?
Eine große Abweichung zwischen geschätzten und tatsächlichen Zeilen (E-rows vs. A-rows) ist ein Zeichen veralteter Statistiken.
SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name='&TABLE_NAME';- Statistiken sind veraltet / alt
- Statistiken sind aktuell, aber sie ist trotzdem langsam
Systemweit: Wo ist der Engpass?
Ist alles langsam statt nur einer Abfrage, nach einem Ressourcenengpass suchen: CPU, IO, Speicher oder verbreitete Blockierung?
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 nahe 100%
- Hoher IO-Wait
- Speicherdruck / niedrige Cache Hit Ratio
- Viel Blockierung
Ursache: Blockierung
Die Abfrage wartet auf eine Sperre, die eine andere Session hält. Finde die Session am Anfang der Kette (Root Blocker) und löse sie auf.
Ursache: IO-Engpass
Die Abfrage liest viel von der Platte. Meist wegen Full Scans (fehlender Index) oder zu wenig Speicher (Buffer Cache).
Ursache: Commit-/Log-Engpass
Dominiert 'log file sync' / 'WRITELOG', warten Commits auf Redo-/WAL-Schreibvorgänge.
Ursache: Fehlender Index
Der Plan macht einen Full Scan. Ein passender Index auf Filter-/Join-Spalten bringt eine große Beschleunigung.
Ursache: Planregression
Die Abfrage war gestern schnell, heute nicht; der Plan hat sich geändert. Meist veraltete Statistiken oder eine Parameteränderung.
Ursache: Abfragedesign
Der Plan ist vernünftig, aber die Abfrage leistet von Natur aus zu viel Arbeit (zu viele Zeilen, N+1, unnötige Joins).
Ursache: Veraltete Statistiken
Der Optimizer schätzt falsch, weil die Statistiken nicht aktuell sind.
Tiefgehende Analyse
Sind Plan und Statistiken in Ordnung, aber es ist trotzdem langsam, tiefer graben.
Ursache: CPU-Engpass
Das ganze System ist CPU-gebunden. Meist verbrennen einige schlecht geplante Abfragen die CPU.
Ursache: Speicherdruck
Niedrige Cache Hit Ratio und/oder Swapping: Der Speicher reicht nicht aus oder ist falsch verteilt.