Optimierung der Oracle-Datenbank für große Transaktionssysteme

Letztes Jahr übernahmen wir die Verwaltung einer Oracle-Datenbank für einen unserer Kunden — ein großes tschechisches Finanzinstitut. Das System verarbeitete über eine Million Transaktionen pro Tag und starb langsam. Die Antwortzeiten verschlechterten sich jeden Monat, und Batch-Jobs schafften es nicht, über Nacht fertig zu werden. Hier ist die Geschichte, wie wir das Ruder herumrissen.

Diagnose: AWR-Reports lügen nicht¶

Das Erste, was man tut, wenn eine Oracle-Datenbank nicht so performt wie sie sollte, ist ein Blick auf die AWR-Reports (Automatic Workload Repository). In unserem Fall war es wie das Öffnen eines medizinischen Befunds — ein klares Bild des Problems.

Die Top Wait Events zeigten massives db file sequential read — ein klassisches Symptom für schlechte Execution Plans und fehlende Indizes. Die Buffer Cache Hit Ratio lag bei 87 %, was für ein OLTP-System mit 64 GB SGA miserabel ist. Und der Shared Pool Free Memory pendelte um 2 %, was ständige Hard Parses bedeutete.

Schritt 1: Stabilisierung der Execution Plans¶

Der Oracle Optimizer ist intelligent, aber manchmal zu intelligent. In der Produktion erlebten wir immer wieder, dass sich nach gather stats die Execution Plans radikal änderten — Abfragen, die in einer Sekunde liefen, dauerten plötzlich Minuten.

Lösung: SQL Plan Baselines. Wir haben gute Execution Plans erfasst und sie mit DBMS_SPM fixiert. Neue Pläne müssen erst verifiziert werden, bevor der Optimizer sie verwenden darf. Ein etwas konservativer Ansatz, aber in einem Bankensystem will man keine Überraschungen.

Der zweite Schritt war die Einrichtung von Pending Statistics. Statistiken werden zuerst in einen Pending-Status veröffentlicht, auf einer Staging-Umgebung getestet und erst dann in der Produktion aktiviert. Ja, es ist mehr Arbeit. Ja, es lohnt sich.

Schritt 2: Partitionierung — Grundlage für große Tabellen¶

Die Haupttransaktionstabelle hatte 800 Millionen Zeilen und wuchs weiter. Ohne Partitionierung war es wie die Suche nach der Nadel im Heuhaufen — selbst mit Index. Partitionierung ist ein Enterprise-Edition-Feature (und daher teuer), aber für Tabellen dieser Größe ist es eine Notwendigkeit.

Wir wählten Range Partitioning nach Monat auf der Spalte TRANSACTION_DATE. Ältere Partitionen (>2 Jahre) werden mit COMPRESS FOR OLTP komprimiert. Das Ergebnis: Partition Pruning reduzierte den I/O für die meisten Abfragen um 90 %, da eine typische Abfrage Daten des letzten Monats anfordert.

Die Archivierung ist jetzt trivial — ALTER TABLE DROP PARTITION statt DELETE mit Millionen von Zeilen. Batch-Jobs, die zuvor 6 Stunden liefen, dauern jetzt 40 Minuten.

Schritt 3: Index-Strategie¶

Ein klassischer Fehler: Die Tabelle hatte 23 Indizes, von denen 8 ungenutzt waren. Jedes INSERT musste alle 23 Indizes aktualisieren — und bei Millionen von Inserts pro Tag kostete das Leistung.

Wir haben V$SQL_PLAN durchgearbeitet und die tatsächlich genutzten Indizes identifiziert. Acht haben wir entfernt, drei unnötig breite zusammengesetzte Indizes durch gezieltere ersetzt und zwei funktionsbasierte Indizes für häufige Abfragen mit TRUNC(transaction_date) hinzugefügt.

Ergebnis: Der INSERT-Durchsatz stieg um 35 %. Und paradoxerweise — auch SELECT-Abfragen waren schneller, weil der Optimizer weniger Indizes zu berücksichtigen hatte und aufhörte, die falschen auszuwählen.

Schritt 4: PGA- und Temp-Management¶

Batch-Jobs erzeugten massive Sorts und Hash Joins, die auf die Festplatte überliefen (Temp Tablespace). PGA_AGGREGATE_TARGET war auf 2 GB eingestellt, was für einen Server mit 128 GB RAM lächerlich war.

Wir erhöhten PGA auf 16 GB und verlegten den Temp Tablespace auf schnelle SSDs (als Ersatz für alte SAS-Platten). Batch-Jobs, die zuvor Temp füllten und abstürzten, laufen jetzt sauber im Arbeitsspeicher.

Schritt 5: Connection Pooling und anwendungsseitige Änderungen¶

Auf der Anwendungsseite (Java EE, GlassFish) stellten wir fest, dass der Connection Pool auf maximal 200 Verbindungen konfiguriert war, aber durchschnittlich nur 15 genutzt wurden. Die übrigen Verbindungen belegten nur Session-Speicher auf der Oracle-Seite.

Wir reduzierten den Pool auf 50, fügten Statement Caching hinzu (PreparedStatement-Cache im JDBC-Treiber) und aktivierten implizites Statement Caching auf der Oracle-Seite. Hard Parses sanken von 500/s auf 20/s.

Ergebnisse nach drei Monaten¶

• Durchschnittliche Antwortzeit: von 2,3 s auf 0,4 s
• Buffer Cache Hit Ratio: von 87 % auf 99,2 %
• Batch-Job-Laufzeit: von 6 Std. auf 40 Min.
• Hard Parses: von 500/s auf 20/s
• Temp-Tablespace-Auslastung: von 98 % auf 15 %

Kein Hardware-Upgrade. Keine neue Lizenz. Nur die richtige Konfiguration und ein Verständnis dafür, was Oracle tatsächlich unter der Haube macht.

Erkenntnisse¶

Oracle Database ist ein mächtiges Werkzeug, erfordert aber Pflege. Lesen Sie AWR-Reports regelmäßig — nicht erst wenn es brennt. Investieren Sie Zeit in Partitionierung bei großen Tabellen. Und vor allem: Bevor Sie neue Hardware kaufen, prüfen Sie, ob Sie das Vorhandene richtig nutzen.