Beschreibung der Applikation
In einer Applikation werden im ersten Schritt in der Tabelle WORKER_PARAMETER mehrere 100.000 Arbeitsschritte generiert. Danach werden ca. 100 Prozesse gestartet, die diese Arbeitsschritte abarbeiten und, sobald diese Erfolgreich erledigt sind, den Arbeitsauftrag aus der Tabelle WORKER_PARAMETER wieder löschen. Nach einiger Zeit sind dann alle Arbeitsschritte erledigt und die Tabelle WORKER_PARAMETER ist wieder leer. Damit sich die Verarbeitungsprozesse nicht gegenseitig behindern, gibt es eine SPALTE WORKER_PROCESS, die bestimmt, welcher Prozess sich um welchen Arbeitsschritt kümmert. Damit gibt es auch keinerlei unnötige Transaktionswaits. Wenn die Verarbeitung abgeschlossen ist, gibt es einen Batchjob, der die Ergebnisse auswertet und danach alle für die Verarbeitung genutzten Tabellen Truncated.
Auf der Tabelle WORKER_PARAMETER gibt es einen Primary Key auf die Spalte WORKER_PARAMETER_ID.
Der Oracle Server hat 8 vCPUs und die Prozesse, die die Arbeitsschritte bearbeiten, laufen auf eigenen Applikationsservern. In der Datenbank werden nur die Ergebnisse (in verschiedenen Tabellen) persistiert sowie die erledigten Arbeitsschritte gelöscht. Jeder Verarbeitungsschritt sollte dauert in der Regel einige Sekunden und erzeugt dann genau ein INSERT für das Ergebnis sowie ein DELETE um den Arbeitsschritt aus der Tabelle WORKER_PARAMETER zu löschen.
Die Datenbank läuft in der Amazon AWS Cloud als Oracle RDS Instanz.
Problemsympthom und Analyse
Der Datenbankserver steht fast permanent unter sehr hoher CPU Last. Das sieht man auch in der AWS Console, die CPU steht oft für längere Zeit auf 100%. Da Amazon in der AWS Console leider nur sehr wenig Informationen für die Problemanalyse liefert, wurde das Oracle STATSPACK installiert, um Oracle Datenbank Performance Informationen auswerten zu können.
Analyse mittels STATSPACK Report
Viele Prozesse warten auf die CPU
- Statspack: Av Act Sess: 83.7
- cpu_count: 8
Auffällig ist, dass anscheinend (fast) alle Verarbeitungsprozesse gleichzeitig auf der Datenbank auf etwas warten – im Schnitt 83,7 Prozesse (davon vielleicht 1-2 Hintergrundprozesse).
Die Frage ist warum und worauf?
TOP Foreground Wait Event
Schauen wir uns einmal den TOP Wait Event an. Es ist der Resource Manager, der dafür sorgt, dass nicht mehr wie die 8 gebuchten CPU Cores genutzt werden können. Interessant ist, dass jeder Wait im Schnitt 109ms dauert – eine sehr lange Zeit!
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
------------------ ---------- ---- ---------- ------ -------- ------
resmgr:cpu quantum 1,658,897 0 180,308 109 2.4 64.7Die Frage ist, wie die Verteilung dieser Waits aussieht.
Event Histogram
Im Event Histogram sehen wir, dass die Waits großteils zwischen 32ms und einer Sekunde liegen, also warten wirklich die meisten Sessions sehr lange – und nicht nur einzelne, die den Schritt verzerren würden.
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
------------------ ----- ----- ----- ----- ----- ----- ----- ----- -----
resmgr:cpu quantum 1660K .5 .4 .6 1.1 3.3 7.8 86.4Womit wir zur Frage kommen: Welche Statements sind daran schuld?
TOP SQL Statement
Interessanter weise ist es genau ein Statement, dass anscheinend für die ganze Last verantwortlich ist. Es handelt sich dabei um das DELETE Statement, dass über den PK eine Datensatz löschen soll!
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ---------- ---------- ------ -------- ------------ ----------
24431.35 93,470 0.26 87.9 ######## 629,093,473 3777381256
Module: JDBC Thin Client
delete from WORKER_PARAMETER where WORKER_PARAMETER_ID=:1Normalerweise sollte ein DELETE, dass eine Datensatz über den PK Index löscht, maximal 2-4 Buffer Gets pro Ausführung benötigen. Hier sind es aber 629 Millionen Buffer Gets für 93.470 Executions –> also über 6700 Buffer Gets pro Ausführung!
Das muss genauer überprüft werden!
Ausführungsplan für das Statement
Wir nutzen die SQL_ID um uns den Ausführungsplan für das Statement anzusehen.
select * from dbms_xplan.display_cursor('4r6rha82wcmph',NULL);
SQL_ID 4r6rha82wcmph, child number 0
-------------------------------------
delete from WORKER_PARAMETER where WORKER_PARAMETER_ID=:1
Plan hash value: 363999061
---------------------------------------------------------------------...
| Id | Operation | Name | Rows | Bytes |...
---------------------------------------------------------------------...
| 0 | DELETE STATEMENT | | | |...
| 1 | DELETE | WORKER_PARAMETER | | |...
|* 2 | INDEX FAST FULL SCAN| WORKER_PARAMETER_PK | 1 | 26 |...
---------------------------------------------------------------------...
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("WORKER_PARAMETER_ID"=:1)Überraschenderweise findet man im Ausführungsplan einen INDEX FAST FULL SCAN auf den PK Index, wobei Oracle davon ausgeht, dass genau EIN Datensatz im Index erwartet wird. Warum glaubt der CBO das, wenn wir doch wissen, dass mehrere 100.000 Datensätze in der Tabelle WORKER_PARAMETER eingefügt werden, bevor diese abgearbeitet wird?
select NUM_ROWS, LAST_ANALYZED
from DBA_TABLES
where TABLE_NAME='WORKER_PARAMETER';
NUM_ROWS LAST_ANAL
---------- ---------
0 05-FEB-23
select NUM_ROWS, LAST_ANALYZED
from DBA_INDEXES
where INDEX_NAME='WORKER_PARAMETER_PK';
NUM_ROWS LAST_ANAL
---------- ---------
0 05-FEB-23Die Statistik ist von letzter Nacht – Oracle berechnet die Objektstatistiken normalerweise ab 22Uhr – und passt nicht zum aktuellen Füllgrad der Tabelle! Der Cost Based Optimizer geht davon aus, dass die Tabelle leer ist oder nur wenige Zeilen enthält. Hier ist ein INDEX FAST FULL SCAN sicher die beste Wahl!
Lösungsmögichkeiten
Es gibt verschiedene Lösungsmöglichkeiten für das Problem, hier eine Auswahl:
Nutzen eines INDEX_ASC Hints beim Delete Statement, damit kein FAST FULL SCAN genommen wird
Damit verhindert man aber, dass der CBO einen optimalen Zugriffsplan ermittelt. Vernünftiger ist es, wenn man für passende Objektstatistiken sorgt.
Einmaliges erzeugen und Sperren der Objektstatistiken
Eine andere Möglichkeit ist, dass man die Tabelle befüllt und bevor man die Verarbeitungsprozesse startet einmalig mittels DBMS_STATS.GATHER_TABLE_STATS die Tabellenstatistik ermittelt und danach mittels DBMS_STATS.LOCK_TABLE_STATS sperrt. Dadurch würde keine neue Objektstatistik erzeugt werden. In diesem Beispiel macht die Vorgehensweise durchaus sind, weil die Anzahl der Records in der Tabelle immer einige 100.000 beträgt, bevor die Verarbeitung beginnt und der CBO dann auf alle Fälle einen normalen Index Zugriff (und keinen FAST FULL SCAN) nutzen wird.
Der Nachteil von DBMS_STATS.LOCK_TABLE_STATS ist, dass die MIN und MAX Werte pro Spalte in DBA_TAB_COLUMNS ebenfalls nicht aktualisiert werden. Kommen in der Tabelle Spalte vor, die ansteigend/abfallend sind (zb: DATUM, Wert aus Sequence,…) kann das wiederum zu falschen Ausführungsplänen führen. In dieser Applikation ist das aber nicht der Fall, da es lediglich das initiale INSERT und dann die einzelnen DELETES auf die Tabelle gibt und die Spalte WORKER_PARAMETER_ID dabei immer mit 1 beginnt und aufsteigend ist. Somit stimmt die Untergrenze immer und die Obergrenze (einige 100.000) ist nicht wirklich relevant.
Berechnen der Statistik nach dem Befüllen der Tabelle
Der sauberste Weg ist, dass man für die Tabelle WORKER_PARAMETER nach dem Befüllen mittels DBMS_STATS.GATHER_TABLE_STATS die aktuelle Objektstatistik ermittelt, bevor man die Verarbeitungsprozesse startet. Damit hat der Oracle Cost Based Optimizer die richtigen Voraussetzungen für seine Tätigkeit.
Ergebnis: Ausführungsplan mit aktuellen Objektstatistiken
Sobald die Objektstatistiken gerechnet sind, schaut der Ausführungsplan gleich viel besser aus:
------------------------------------------------------------------...
| Id | Operation | Name | Rows | Bytes |...
------------------------------------------------------------------...
| 0 | DELETE STATEMENT | | 1 | 14 |...
| 1 | DELETE | WORKER_PARAMETER | | |...
|* 2 | INDEX UNIQUE SCAN| WORKER_PARAMETER_PK | 1 | 14 |...
------------------------------------------------------------------...Die CPU Auslastung am Datenbank Server während die Verarbeitungsprozesse laufen, bewegt sich im Bereich von 1-2 CPU Cores. Es wird überlegt in Zukunft die Anzahl der vCPUs von aktuell 8 auf 4 zu halbieren. Dadurch würden sich auch die Kosten bei AWS merklich reduzieren.
Zusammenfassung
Fehlende oder falsche Objektstatistiken führen in der Praxis oft zu suboptimalen Ausführungsplänen, wofür der Oracle CBO aber nichts kann! Er hat nur dann eine Chance einen passenden Ausführungsplan zu finden, wenn die benötigten Informationen (Objektstatistik, Systemstatistiken, etc.) korrekt vorliegen.
Datenbank- und Applikation- Performance Tuning kann die Kosten für den Betrieb von Datenbanken in der Cloud deutlich reduzieren!
Weiterführende Themen
Wer mehr über das Thema Datenbank Performance Tuning und Applikations Tuning wissen möchte,
dem kann ich folgende, von mir entwickelte, Workshops ans Herz legen:
- Oracle Datenbank Performance Tuning und Monitoring Workshop für alle Editionen
- Oracle SQL Tuning Workshop
Diese enthalten meine Erfahrungen aus fast 30 Jahren Oracle Datenbank Tuning.