SQL-Performance-Tuning mit Analytic Functions – ein Projektbericht
Die seit Jahren auf Hochtouren laufende Marketing-Maschinerie könnte man vielleicht dahingehend missverstehen, dass demnächst Software immer performant läuft, sobald sie die neuen Technologien wie Cloud, InMemory oder Exadata nutzt. Ergo: Warum sollte man sich überhaupt noch Gedanken zu dem Thema Performance machen? Ich glaube, das stimmt nicht. Durch höhere Datenmengen und ganz neue Anforderungen der Anwender ist die Performance immer noch ein wichtiges Thema. Hinzu kommt: Performance-Probleme, die auf schlechter Architektur oder suboptimaler Implementierung beruhen, werden nicht dauerhaft durch die Cloud "geheilt". Es macht also nach wie vor Sinn, die Performance einer Anwendung schon während der Entwicklung im Auge zu behalten.
Performance-Tuning? Warum? Wir haben doch Cloud, InMemory und Exadata!
Dabei sollte man nicht erst dann einschreiten, wenn es offensichtliche Probleme mit der Performance einer SQL-Abfrage gibt. Scheinbar zufriedenstellend laufende Abfragen können sich schon morgen als echte Probleme erweisen. Mögliche Gründe dafür könnten in folgenden Bereichen liegen:
- Hoher Verbrauch von System-Resourcen,
- schlechte Parallelisierbarkeit oder
- nicht lineare Skalierbarkeit bei wachsenden Datenmengen.
Daher macht es auch Sinn, Abfrage von Anfang an performant zu gestalten. "Do not tune – write fast SQL!"
Was sind eigentlich SQL Analytic Functions?
Worüber reden wir eigentlich? Was sind SQL Analytical Functions? Analytical Functions bilden eine Familie von SQL-Funktionen. Sie sind auch als Fenster- oder Windowing-Funktionen bekannt, weil sie eine "Frame-Sicht" auf die Daten bieten. Analytical Functions arbeiten auf der Ergebnismenge einer Abfrage. Dabei verfügen sie über besondere Eigenschaften.
Die Oracle-Dokumentation sagt dazu Folgendes: "Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time."
Die Oracle-Dokumentation zu Datenbank 18c listet 46 Funktionen auf [1]: AVG, CLUSTER_DETAILS, CLUSTER_DISTANCE, CLUSTER_ID, CLUSTER_PROBABILITY, CLUSTER_SET, CORR, COUNT, COVAR_POP, COVAR_SAMP, CUME_DIST, DENSE_RANK, FEATURE_DETAILS, FEATURE_ID, FEATURE_SET, FEATURE_VALUE, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, LISTAGG, MAX, MIN, NTH_VALUE, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, PREDICTION, PREDICTION_COST, PREDICTION_DETAILS, PREDICTION_PROBABILITY, PREDICTION_SET, RANK, RATIO_TO_REPORT, REGR_ (Linear Regression) Functions, ROW_NUMBER, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP und VARIANCE.
Analytic Functions sind nach wie vor nicht so bekannt wie sie es verdient hätten – schade. Das Einsatzgebiet wird eher in BI oder DWH vermutet, weniger in Performance-Tuning operativer SQL-Abfragen. Dabei sind sie seit Oracle Database 8i (8.1.6, Januar 2000) verfügbar und das in allen Datenbank-Editionen, auch in XE. Darüber hinaus wurden sie längst ins SQL-Standards ANSI/ISO SQL:2003 und SQL:2008 (Windowing Functions) aufgenommen. Daher unterstützen auch relationale Datenbanken anderer Hersteller inzwischen Analytic Functions: DB2, Oracle, Sybase, PostgreSQL und SQL Server, um nur ein paar Beispiele zu nennen.
Besondere Eigenschaften anhand von 2 Beispielen
Viele der Analytic Functions kennt man auch als Aggregationsfunktionen, z. B. die Funktion MAX() – "MAX returns maximum value of expr. You can use it as an aggregate or analytic function".
Hier die Aggregation-Variante von MAX():
SELECT MAX(salary) "Maximum" FROM employees;
Maximum
----------
24000Es wird der Höchstwert von salary ermittelt. Da keine WHERE-Klausel verwendet wird, ist das der Höchstwert in der gesamten Tabelle employees.
Hier die analytische Variante von MAX():
SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max FROM employees ORDER BY manager_id, last_name, salary; MANAGER_ID LAST_NAME SALARY MGR_MAX ---------- --------- ---------- ---------- 100 Zlotkey 10500 17000 101 Baer 10000 12000 101 Whalen 4400 12000 102 Hunold 9000 9000 103 Austin 4800 6000
Es wird pro Window, in dem Fall pro manager_id der Höchswert von salary ermittelt. Hier wird bereits eine interessante Eigenschaft der Analytic Functions sichtbar: Sie bieten Zugriff auf Werte aus anderen Zeilen der Ergebnismenge. Das können klassische Aggregationsfunktionen nicht.
Ein weiteres Beispiel ist die Funktion LAG(): "LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position."
SELECT hire_date, last_name, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY hire_date;
HIRE_DATE LAST_NAME SALARY PREV_SAL
--------- ------------------------- ---------- ----------
18-MAY-03 Khoo 3100 0
24-JUL-05 Tobias 2800 3100
24-DEC-05 Baida 2900 2800
15-NOV-06 Himuro 2600 2900
10-AUG-07 Colmenares 2500 2600Hier wird salary des Vorgängers (im Sinne der Sortierung des Analytic Windows) ermittelt. Und wieder verblüffen die Analytic Functions, denn sie schaffen dies ohne ein mehrfaches Lesen der Daten, z. B. durch Subselects oder Self Joins.
Einsatzbeispiel aus einem Projekt
In einem Projekt standen wir vor der Aufgabe, eine Reihe von SQL-Statements zu tunen. Es handelte sich um "Langläufer", die das nächtliche Batch-Fenster immer häufiger gesprengt haben. Die Statements haben Daten aus Quell-Tabellen in Materialized Views (Snapshots) geladen. Die Statements waren als Views gekapselt und nach einem gemeinsamen Schema aufgebaut. Ihre Aufgabe bestand darin, die korrekte Version eines Objektes aus der Quell-Tabelle herauszufinden. Um das Prinzip zu veranschaulichen, betrachten wir ein fiktives Beispiel:
- Lesen von Adressen
- pro Kunde nur eine Adresse
- Auswahl über TYP der Adresse:
- bevorzugt Adresse mit TYP = FIRMA
- wenn nicht vorhanden, dann Adresse mit TYP = POSTFACH
- wenn nicht vorhanden, dann Adresse mit TYP = PRIVAT
- Tabelle ADRESSEN
- ID (PK)
- KUNDEN_ID (UK)
- TYP [ FIRMA | POSTFACH | PRIVAT ] (UK)
- STRASSE
- HAUSNUMMER
- POSTFACH
- PLZ
- ORT
Beispieldaten:
KUNDEN_ID TYP ------------- --------- 90810846361 PRIVAT 90810846361 POSTFACH 217537839264 PRIVAT 481491166609 PRIVAT 481491166609 POSTFACH 481491166609 FIRMA
Das bisherige SQL-Statement:
SELECT A.KUNDEN_ID, A.TYP,
A.STRASSE, A.HAUSNUMMER, A.POSTFACH, A.PLZ, A.ORT
FROM ADRESSEN A
WHERE
( ( A.TYP = 'FIRMA' ) OR
( A.TYP = 'POSTFACH'
AND NOT EXISTS ( SELECT 1 FROM ADRESSEN B
WHERE B.KUNDEN_ID = A.KUNDEN_ID
AND B.TYP = 'FIRMA' ) ) OR
( A.TYP = 'PRIVAT'
AND NOT EXISTS ( SELECT 1 FROM ADRESSEN C
WHERE C.KUNDEN_ID = A.KUNDEN_ID
AND C.TYP IN ( 'FIRMA' , 'POSTFACH' ) ) ) )Der Execution-Plan des bisherigen SQL-Statements:
Vorgang Optionen Objekt
SELECT STATEMENT
FILTER
TABLE ACCESS STORAGE FULL ADRESSEN
INDEX UNIQUE SCAN ADRESSEN_UK
INDEX RANGE SCAN ADRESSEN_UK Laufzeiten des bisherigen SQL-Statements in Sekunden (Performancetests mit Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit unter VirtualBox auf einem Office-Laptop):
100 Rows: 0,02
10.000 Rows: 0,21
1.000.000 Rows: 12,58
100.000.000 Rows: 1190,00
Jeder Satz in der Tabelle ADRESSEN muss daraufhin überprüft werden, ob er in der richtigen Version vorliegt, oder ob ggf. zu dem gleichen Kunden eine andere Adresse vorliegt, die Vorrang vor dem aktuellen Satz hat. Daher die beiden NOT EXISTS-Klauseln. Durch die Index-Scans skaliert die Antwortzeit recht gut, jedoch waren die Antwortzeiten immer länger geworden, da die Anzahl der zu betrachteten Sätze stetig gewachsen ist (in unserem Projekt mehrere Millionen Rows).
Gelöst hat das Problem der Einsatz der Analytical Function RANK(): "RANK calculates the rank of a value in a group of values. As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause."
RANK() kann also auch als Aggregationsfunktion eingesetzt werden:
SELECT RANK(15500) WITHIN GROUP (ORDER BY salary DESC) AS "Rank of 15500"
FROM employees;
Rank of 15500
--------------
4Hier wird ermittelt, welchen Rang der Wert von 15500 als salary bei absteigender Sortierung annehmen würde.
Und hier die analytische Variante von RANK():
SELECT department_id, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary) AS RANK
FROM employees
WHERE department_id = 60
ORDER BY RANK, last_name;
DEPARTMENT_ID LAST_NAME SALARY RANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 4
60 Hunold 9000 5Es wird für alle Mitarbeiter mit department_id = 60 anhand der Spalte salary ein Rang gebildet. Mit dem Einsatz von RANK() geht das kinderleicht!
Hier die neue Version des SQL-Statements:
SELECT KUNDEN_ID, TYP, STRASSE, HAUSNUMMER, POSTFACH, PLZ, ORT
FROM
(
SELECT KUNDEN_ID, TYP,
STRASSE, HAUSNUMMER, POSTFACH, PLZ, ORT,
RANK() OVER (PARTITION BY KUNDEN_ID
ORDER BY CASE TYP WHEN 'FIRMA' THEN 1
WHEN 'POSTFACH' THEN 2
WHEN 'PRIVAT‘ THEN 3 END ) AS RANK
FROM ADRESSEN
)
WHERE RANK = 1Der Execution-Plan des neuen SQL-Statements:
Vorgang Optionen Objekt SELECT STATEMENT VIEW WINDOW SORT PUSHED RANK TABLE ACCESS STORAGE FULL ADRESSEN
Jetzt werden die Daten lediglich in einem Full Table Scan gelesen. Kein weiterer Zugriff auf die Daten der Tabelle ADRESSEN ist notwendig. Der Eintrag WINDOW SORT PUSHED RANK zeigt, dass die Analytic Function ausgeführt wird. Dies geschieht im Temp-Bereich, d. h. in Memory, abhängig von der Datenmenge ggf. auch in TEMP-Tablespace. Das spiegelt sich in den Ausführungszeiten in Sekunden wieder:
bisher neu
100 Rows: 0,02 0,02
10.000 Rows: 0,21 0,09
1.000.000 Rows: 12,58 6,23
100.000.000 Rows: 1190,00 630,00Einige Statistiken dazu:
bisher neu
consistent gets: 9.366 193
CPU used by this session: 1 2Die Ausführungszeit ist signifikant gesunken. In unserem Beispiel um Faktor 2, im Projekt haben wir sogar Verbesserungen um Faktor 8 beobachtet! Damit haben wir erfolgreich das Performance-Problem der zu lange laufenden Batch-Jobs gelöst. Die Eingriffe in das bestehende System waren auf die SQL-Statements begrenzt.
Die Implementierung und die Tests waren recht einfach. Beim Testen nutzen wir den Set-Operator MINUS, um festzustellen, ob die Ergebnismengen des alten und des neuen SQL-Statements identisch sind.
Das neue SQL-Statement ist nicht nur signifikant performanter. Weitere Vorteile des neuen SQL-Statements sind:
- einfacher Aufbau, dadurch verständlicher und wartbarer,
- in der Ausführung weniger ressourcenintensiv,
- besser parallelisierbar und
- besser auf wachsende Datenmenge vorbereitet.
Analytical Functions bieten dank besonderer Eigenschaften viel Power zum (fast) Nulltarif. Sie sind mein Geheimfavorit für so manches Performance-Problem. Es gibt kaum Gründe, die gegen ihren Einsatz sprechen. Sie sind ein Beispiel für die stetige Evolution von SQL.
Analytical Functions sind nicht nur für Statistiker, BI- oder DWH-Developer interessant, sondern für alle SQL-Entwickler. Sie sind aus meiner Sicht eines der meist unterschätzten SQL-Features.
Es gibt SQL vor Fenster-Funktionen und SQL nach Fenster-Funktionen.
(Dimitri Fontaine, PostgreSQL Major Contributor)
Weitere Informationen:
- L. Eder; 2016: 2000 Zeilen Java oder 50 Zeilen SQL, Java Aktuell
- D. Burleson: SQL tuning: Rewrite exists subquery using rank and partition analytics
