Über unsMediaKontaktImpressum
Christian Schwitalla 20. November 2018

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
----------
     24000

Es 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       2600

Hier 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
--------------
             4

Hier 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          5

Es 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 = 1

Der 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,00

Einige Statistiken dazu:

                           bisher   neu
consistent gets:            9.366   193
CPU used by this session:       1     2

Die 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.

Christian Schwitalla auf den IT-Tagen 2018

Zum gleichen Thema hält Christian Schwitalla einen Vortrag auf den diesjährigen IT-Tagen – der Jahreskonferenz der Informatik Aktuell.

Projektbericht zu SQL-Tuning mit Analytic Functions
(11.12.2018, 18:00 Uhr)

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)

    Quellen
    1. Oracle-Dokumentation zu Datenbank 18c

    Weitere Informationen:

    Autor

    Christian Schwitalla

    Seit dem Abschluss der Allgemeinen Informatik an der FH Dortmund im Jahre 1993 arbeitet Christian Schwitalla seit 25 Jahren in unterschiedlichen Funktionen im Bereich Oracle DB-Programmierung.
    >> Weiterlesen
    Das könnte Sie auch interessieren
    botMessage_toctoc_comments_9210