• Entwicklung/Architektur
  • Methoden
  • Programmiersprachen
• Betrieb
  • Datenbanken
  • Server
  • IT-Security
  • Speicher
  • Netzwerke
  • Virtualisierung
  • Verfügbarkeit
  • Künstliche Intelligenz
• Management und Recht
  • Projektmanagement
  • Digitalisierung
  • IT-Recht
  • IT-Selbständige
• Aktuelles
• Termine
• IT-Jobs

• Entwicklung
• Betrieb
• Management und Recht
• News
• IT-Jobs
• Newsletter

Ein wesentlicher Vorteil von SQL ist die Möglichkeit, Funktionen in SQL-Abfragen verwenden zu können. Die Oracle-DB bietet nicht nur zahlreiche SQL-Standardfunktionen an, sondern erlaubt auch, eigene PL/SQL-Funktionen in SQL-Anweisungen zu verwenden. Wichtig ist dabei jedoch, die Performanceaspekte im Auge zu behalten und die entsprechenden Oracle-Features zu kennen.

Ein typischer Tag im Leben eines Oracle-Consultants: Ich sitze auf dem Balkon (es ist Sommer) und versuche für einen Kunden das Performanceproblem einer Datenbankabfrage zu lösen. Die SQL-Abfrage ist sehr komplex und beinhaltet zahlreiche Funktionsaufrufe von SQL- und PL/SQL-Funktionen. Vor allem die PL/SQL-Aufrufe bereiten mir Kopfzerbrechen, denn in den Funktionen werden weitere SQL-Abfragen ausgeführt, die wiederum Funktionsaufrufe enthalten. Definitiv keine gute Idee! Aber beginnen wir vor vorne und untersuchen etwas genauer, wie Oracle mit Funktionsaufrufen in SQL umgeht.

Betrachten wir zuerst einfache skalare SQL-Funktionen wie UPPER, SUBSTR, TO_CHAR, etc., wie sie in zahlreichen SQL-Abfragen vorkommen. Die gute Nachricht: Solche Funktionen können problemlos im SELECT-Teil einer Abfrage verwendet werden und verursachen keine nennenswerten Performanceeinbußen.

Anders sieht die Situation aus, wenn SQL-Funktionen in WHERE-Bedingungen verwendet werden. Hier haben wir es mit zwei Herausforderungen zu tun:

• Ein Index kann nicht verwendet werden, wenn das indizierte Attribut verändert wird.
• Der Oracle Optimizer kann die Selektivität eines Funktionsaufrufs nicht korrekt ermitteln.

Um diese zwei Punkte genauer zu untersuchen, betrachten wir ein sehr einfaches Beispiel: Auf eine Tabelle ADDRESSES mit 23941 Zeilen werden verschiedene Abfragen ausgeführt, die jeweils die Adressen eines Landes anhand des Ländercodes (CTR_CODE) einschränken. Auf dem Attribut CTR_CODE existiert ein Index. Die Statistiken für den Query Optimizer sind aktuell.

In Listing 1 führen wir zwei Abfragen aus, eine für Groß-Britannien (357 Adressen) und eine für Deutschland (9344 Adressen). Aufgrund der aktuellen Statistiken konnte der Oracle Optimizer korrekte Schätzungen durchführen. Die geschätzte Anzahl Zeilen (E-Rows), auch Cardinality genannt, stimmt genau mit der tatsächlichen Anzahl Zeilen (A-Rows) überein. Wie die Ausführungspläne zeigen, hat sich der Optimizer bei der ersten Abfrage für einen Indexzugriff entschieden. Für eine Selektivität von 1.5% (357/23941) ist dies die perfekte Wahl. Im zweiten Fall ist die Selektivität ca. 39% (9344/23941). Deshalb wird hier ein Full Table Scan verwendet – ebenfalls die beste Wahl.

Listing 1: SQL-Abfragen ohne Funktionsaufrufe

SQL> SELECT * FROM addresses WHERE ctr_code = 'GB';

…

357 rows selected.


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats last'));

---------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |    357 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESSES    |      1 |    357 |    357 |
|*  2 |   INDEX RANGE SCAN                  | ADR_CTR_CODE |      1 |    357 |    357 |
---------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CTR_CODE"='GB')


SQL> SELECT * FROM addresses WHERE ctr_code = 'DE';

…

9344 rows selected.


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats last'));

------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |   9344 |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |      1 |   9344 |   9344 |
------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CTR_CODE"='DE')

Wie sieht nun die Situation aus, wenn wir Funktionsaufrufe in der WHERE-Bedingung verwenden? Dazu führen wir nochmals die gleichen zwei Abfragen aus, diesmal jedoch mit der Funktion UPPER auf den Ländercode.

An den Resultaten ändert sich nichts (die Ländercodes sind im Beispiel alle in Großbuchstaben gespeichert). Aber die Ausführungspläne sehen nun anders aus, wie Listing 2 zeigt:

• In beiden Fällen wird ein Full Table Scan ausgeführt. Der Index auf CTR_CODE kann für den Funktionsaufruf nicht verwendet werden.
• Die geschätzte Anzahl Zeilen ist in beiden Fällen 239. Dies entspricht einer Selektivität von 1% (0.01 * 23941). Ohne zusätzliche Maßnahmen geht der Optimizer bei Funktionsaufrufen immer von einer Selektivität von einem Prozent aus.

Listing 2: SQL-Abfragen mit Funktionsaufrufen

SQL> SELECT * FROM addresses WHERE UPPER(ctr_code) = 'GB';

…

357 rows selected.


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats last'));

------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |    357 |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |      1 |    239 |    357 |
------------------------------------------------------------------ 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CTR_CODE"='GB')


SQL> SELECT * FROM addresses WHERE UPPER(ctr_code) = 'DE';

…

9344 rows selected.


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats last'));

------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |   9344 |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |      1 |    239 |   9344 |
------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CTR_CODE"='DE')

Vor allem die falsche Schätzung der Selektivität ist eine der Hauptursachen für Performanceprobleme in komplexen SQL-Abfragen. Sie kann dazu führen, dass sich der Optimizer für eine falsche Join-Reihenfolge, ungeeignete Join-Methoden und Zugriffsmethoden (Full Table Scans/Index Scans) entscheidet. Ein Funktionsaufruf ist für den Optimizer wie eine "Black Box". Aufgrund der fehlenden Informationen zur Schätzung der Selektivität befindet sich der Query Optimizer sozusagen im "Blindflug".

Die einfachste Möglichkeit, um dieses Problem zu lösen: Funktionsaufrufe in WHERE-Bedingungen vermeiden. In unserem Beispiel könnte durch einen Check-Constraint sichergestellt werden, dass Ländercodes immer in Großbuchstaben gespeichert werden. Dann ist die UPPER-Funktion in den Abfragen nicht mehr notwendig.

Auf Funktionen zu verzichten, ist aber nicht in allen Fällen möglich. Doch zum Glück gibt es in der Oracle-Datenbank verschiedene technische Hilfsmittel, mit denen wir dem Optimizer helfen können, seine Schätzungen zu verbessern. Diese sollen nachfolgend erläutert und verglichen werden.

Wenn der Funktionsaufruf tatsächlich erforderlich ist und nicht vermieden werden kann, besteht eine Lösung darin, einen Function-based Index zu erstellen. Dabei wird nicht ein Attribut indiziert, sondern ein Ausdruck – in unserem Fall der Funktionsaufruf.

Der zusätzliche Index löst aber nur eines unserer Probleme. Um sicherzustellen, dass der Optimizer die Selektivität richtig schätzen kann, müssen nach Erstellung des Function-based Indexes die Statistiken der Tabelle neu berechnet werden. Dazu wird das Package dbms_stats verwendet, wie in Listing 3 aufgeführt.

Listing 3: Erstellung von Function-based Index und Neuberechnung der Statistiken

CREATE INDEX adr_upper_ctr_code_fbi
ON addresses (UPPER(ctr_code));

BEGIN
   dbms_stats.gather_table_stats
      (ownname => USER,
       tabname => 'ADDRESSES',
       method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
       no_invalidate => FALSE);
END;
/

Wenn wir die zwei Beispielabfragen erneut ausführen und die Ausführungspläne in Listing 4 ansehen, können wir einige interessante Details erkennen:

1. Die geschätzte Kardinalität in der Spalte E-Zeilen ist jetzt korrekt. Der Optimierer ist nun in der Lage, korrekte Schätzungen vorzunehmen.
2. Für die erste Abfrage wird der Function-based Index ADR_UPPER_CTR_CODE_FBI verwendet. Dabei wird ein Zugriffsprädikat "SYS_NC00010$"='GB' verwendet. Wir werden später sehen, woher dieses genau kommt.
3. Für die zweite Abfrage wird ein Full Table Scan ausgeführt, obwohl ein Function-based Index zur Verfügung stehen würde. Dies liegt an der hohen Selektivität (39%), die nun korrekt berechnet wird.

Listing 4: Abfragen mit Funktionsaufrufen und Function-based Index

SQL> SELECT * FROM addresses WHERE UPPER(ctr_code) = 'GB';

…

357 rows selected.


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats last'));

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |      1 |        |    357 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESSES              |      1 |    357 |    357 |
|*  2 |   INDEX RANGE SCAN                  | ADR_UPPER_CTR_CODE_FBI |      1 |    357 |    357 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ADDRESSES"."SYS_NC00010$"='GB')


SQL> SELECT * FROM addresses WHERE UPPER(ctr_code) = 'DE';

…

9344 rows selected.


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats last'));

------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |   9344 |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |      1 |   9344 |   9344 |
------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(UPPER("CTR_CODE")='DE')

Virtual Column

Das gleiche Verhalten kann auch mit einer Virtual Column erreicht werden. Virtual Columns sind zusätzliche Attribute einer Tabelle, die jedoch nicht physisch in der Datenbank gespeichert werden. Hinter einer Virtual Column verbirgt sich eine Formel, ein Ausdruck oder ein Funktionsaufruf, der sich auf Attribute der gleichen Zeile beziehen. Listing 5 zeigt, wie die vorherige Lösung mittels Virtual Column implementiert werden kann. Dazu wird der Tabelle ADDRESSES ein neues virtuelles Attribut UPPER_CTR_CODE hinzugefügt. Auf dieses Attribut wird danach ein Index erstellt. Damit der Optimizer die Selektivitäten korrekt ermitteln kann, müssen auch hier die Statistiken neu berechnet werden.

Listing 5: Gleiche Lösung mit Virtual Column

ALTER TABLE addresses
ADD (upper_ctr_code VARCHAR2(2) AS (UPPER(ctr_code)) VIRTUAL);

CREATE INDEX adr_upper_ctr_code
ON addresses(upper_ctr_code);

BEGIN
   dbms_stats.gather_table_stats
      (ownname => USER,
       tabname => 'ADDRESSES',
       method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
       no_invalidate => FALSE);
END;
/

Die Abfragen können nun entweder den Namen der Virtual Column oder den Ausdruck, der sich dahinter verbirgt, verwenden:

• SELECT * FROM addresses WHERE UPPER(ctr_code) = 'GB'
• SELECT * FROM addresses WHERE upper_ctr_code = 'GB'

In beiden Fällen wird die Kardinalität korrekt geschätzt und der Index ADR_UPPER_CTR_CODE verwendet. Für die gleichen Abfragen auf Adressen in Deutschland wird wie zuvor ein Full Table Scan ausgeführt. Das Verhalten ist somit exakt das gleiche wie mit einem Function-based Index.

Die dritte Möglichkeit, den Optimizer bei der Berechnung der korrekten Selektivität zu unterstützen, sind Extended Statistics. Dies sind zusätzliche Statistiken für Ausdrücke (wie einen Funktionsaufruf) oder Gruppen korrelierender Attribute. Extended Statistics können mit der Funktion dbms_stats.create_extended_stats erstellt werden. Diese Funktion berechnet nicht die Statistiken, sondern dient nur zur Vorbereitung der zusätzlichen Metadaten für die Extended Statistics. Nach dem Funktionsaufruf müssen wir also dbms_stats.gather_tables_stats erneut aufrufen, wie in Listing 6 gezeigt.

Listing 6: Definition und Berechnung von Extended Statistics

SQL> SELECT dbms_stats.create_extended_stats(USER,'ADDRESSES','(UPPER(ctr_code))') from dual;
 
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'ADDRESSES','(UPPER(CTR_CODE))')
----------------------------------------------------------------------
SYS_STU9J40#VS#IMBAGKHVY2Q7U04
 
SQL> BEGIN
  2     dbms_stats.gather_table_stats(ownname => USER,
  3                                   tabname => 'ADDRESSES',
  4                                   method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
  5                                   no_invalidate => FALSE);
  6  END;
  7  /
 
PL/SQL procedure successfully completed.

Mit Hilfe von Extended Statistics ist der Optimizer in der Lage, die Selektivität für die Abfragen mit UPPER(ctr_code) korrekt zu berechnen und daraus die richtige Anzahl Zeilen zu schätzen.

Was aber bei dieser Variante nicht geht, ist die zusätzliche Erstellung eines Function-based Indexes. Dies führt zu einer Fehlermeldung "ORA-54018: A virtual column exists for this expression". Das mag überraschend erscheinen, lässt sich aber erklären, wie wir gleich sehen werden.

Die einfachste und beste Lösung ist nach wie vor, Funktionsaufrufe in WHERE-Bedingungen zu vermeiden. Für unveränderte Attribute ist es viel einfacher für den Optimizer, die Selektivität richtig zu schätzen. Sind wir jedoch auf Funktionsaufrufe angewiesen, gibt es die erwähnten drei Möglichkeiten. Doch welche davon ist nun die beste?

Die überraschende Antwort: Es spielt keine Rolle, denn technisch gesehen sind alle drei Varianten gleich:

• Function-based Index: Hier wird eine unsichtbare Virtual Column erstellt. Solche "hidden" Columns sind in der Tabellendefinition oder bei Abfragen nicht sichtbar, können aber über die View USER_TAB_COLS (nicht USER_TAB_COLUMNS) angezeigt werden. Der Name der Virtual Column in unserem Beispiel ist "SYS_NC00010$" und war als Zugriffsprädikat im Ausführungsplan in Listing 4 sichtbar.
• Virtual Column: Wird eine Virtual Column explizit definiert, wie in unserem Beispiel mit UPPER_CTR_CODE, handelt es sich um genau die gleiche Implementierung. Der einzige Unterschied ist hier, dass der Attributname sichtbar ist und auch direkt abgefragt werden kann. Wird ein Index auf eine Virtual Column erstellt, handelt es sich um einen Function-based Index.
• Extended Statistics: Werden zusätzliche Statistiken erstellt, wird im Hintergrund ebenfalls eine unsichtbare Virtual Column erstellt. Der Name in unserem Beispiel ist "SYS_STU9J40#VS#IMBAGKHVY2Q7U04", wie in Listing 6 ersichtlich. Ein Function-based Index konnte nicht erstellt werden, da mehrere Virtual Columns auf den gleichen Ausdruck nicht möglich sind.

Welche dieser Optionen verwendet werden soll, ist eine Frage des persönlichen Geschmacks. Ich ziehe es vor, eine explizite Virtual Column zu erstellen, da sie in der Tabellendefinition sichtbar ist und auch wie ein normales Attribut der Tabelle verwendet und bei Bedarf indexiert werden kann.

Wie sieht nun aber die Situation aus, wenn wir statt Standard-SQL-Funktionen eigene PL/SQL-Funktionen verwenden? Können dann die hier beschriebenen Massnahmen ebenfalls verwendet werden?

Auch für PL/SQL-Funktionen können Function-based Indexes, Virtual Columns und Extended Statistics eingesetzt werden. Allerdings nicht in allen Fällen. Es gibt einige Einschränkungen und Voraussetzungen, die zu beachten sind.

Als zweites Beispiel möchten wir verschiedene Preiskategorien in einer Tabelle ORDER_ITEMS selektieren. Abhängig von der Menge und dem Stückpreis einer Bestellposition ist die Preiskategorie entweder «high», «medium» oder «low». Die (einfache) Geschäftslogik ist in einer PL/SQL-Funktion (siehe Listing 7) definiert.

Listing 7: PL/SQL-Funktion zur Ermittlung von Preiskategorien

CREATE OR REPLACE FUNCTION f_price_category
   (in_quantity order_items.quantity%TYPE,
    in_price order_items.price_per_unit%TYPE) RETURN VARCHAR2
IS
   v_total_price order_items.price_per_unit%TYPE;
   v_category VARCHAR2(6);
BEGIN
   v_total_price := in_quantity * in_price;
   IF v_total_price < 100 THEN
      v_category := 'low';
   ELSIF v_total_price < 1000 THEN
      v_category := 'medium';
   ELSE
      v_category := 'high';
   END IF;
   RETURN v_category;
END f_price_category;
/

Verwenden wir diese Funktion in der SQL-Abfrage in Listing 8, so stellen wir fest, dass die Abfrage auf die Tabelle ORDER_ITEMS (828270 Zeilen) etwa 10 Sekunden dauert. Diese Antwortzeit soll verbessert werden.

Listing 8: SQL-Abfrage mit PL/SQL-Funktion

SELECT COUNT(*), SUM(quantity)
  FROM order_items
 WHERE f_price_category(quantity, price_per_unit) = 'high';
 
  COUNT(*) SUM(QUANTITY)
---------- -------------
     81115        268471

Elapsed: 00:00:10.275

Deterministische Funktionen

Können wir die Performance dieser Abfrage mit den vorher beschriebenen Möglichkeiten verbessern? Der Versuch, einen Function-based Index zu erstellen, scheitert mit einer Fehlermeldung, wie in Listing 9 ersichtlich.

Listing 9: Function-based Index auf PL/SQL-Funktion

CREATE INDEX ordi_price_cat_fbi
ON order_items(f_price_category(quantity, price_per_unit));
 
ORA-30553: The function is not deterministic

Was bedeutet die Fehlermeldung ORA-30553? Eine Funktion ist deterministisch, wenn sie für dieselben Eingabewerte immer dasselbe Ergebnis liefert. Dies ist bei den meisten SQL-Funktionen der Fall, kann aber bei einer selbstgeschriebenen PL/SQL-Funktion nicht garantiert werden. Um Oracle mitzuteilen, dass unsere Funktion deterministisch ist, müssen wir das Schlüsselwort DETERMINISTIC in der Funktionsdeklaration hinzufügen. Dies ist für alle Funktionen erforderlich, die in einem Function-based Index, einer Virtual Column oder in Extended Statistics verwendet werden.

Es liegt in unserer Verantwortung als Entwickler, sicherzustellen, dass die Funktion wirklich deterministisch ist.

Listing 9: Definition einer deterministischen PL/SQL-Funktion

CREATE OR REPLACE FUNCTION f_price_category
   (in_quantity order_items.quantity%TYPE,
    in_price order_items.price_per_unit%TYPE) RETURN VARCHAR2
DETERMINISTIC
IS
…

Aber aufgepasst! Das Schlüsselwort DETERMINISTIC ist nur eine Deklaration, d. h. der PL/SQL-Compiler prüft nicht, ob die Logik der Funktion tatsächlich deterministisch ist oder nicht. Es liegt in unserer Verantwortung als Entwickler, sicherzustellen, dass die Funktion wirklich deterministisch ist. Wenn in der Funktion beispielsweise globale Variablen, Aufrufe von SYSDATE oder SQL-Abfragen vorkommen, ist dies nicht der Fall. Wird die Funktion trotzdem als DETERMINISTIC definiert, kann das zu falschen Ergebnissen der Abfragen führen.

In unserem Beispiel ist die Funktion deterministisch. Nachdem wir also das zusätzliche Schlüsselwort hinzugefügt haben, kann der Function-based Index erstellt werden. Die Antwortzeit der Abfrage ist nun deutlich schneller (in meinen Tests noch 0.3 Sekunden), weil die PL/SQL-Funktion nicht mehr für jede Zeile aufgerufen wird. Stattdessen kann das vorberechnete Ergebnis aus dem Index gelesen werden.

Der Hauptgrund für die schlechte Performance ist in diesem Beispiel jedoch nicht der fehlende Index, sondern die Kombination von SQL und PL/SQL in einer Abfrage. Sie führt zu sogenannten "Context Switches".

Wird innerhalb einer SQL-Anweisung eine PL/SQL-Funktion aufgerufen oder in einem PL/SQL-Block ein SQL-Befehl ausgeführt, findet ein "Context Switch" – ein Kontextwechsel zwischen SQL-Engine und PL/SQL-Engine – statt. Obwohl der Aufwand für den Wechsel zwischen den beiden Umgebungen in den letzten Oracle-Versionen optimiert wurde, kann er nach wie vor zu langen Ausführungszeiten führen, wenn viele solcher Context Switches stattfinden.

In den neueren Oracle-Versionen gibt es verschiedene Möglichkeiten, solche Context Switches zu vermeiden. Mit Oracle 12c wurden User Defined Functions (UDF) eingeführt und ab Oracle 21c können skalare SQL-Macros verwendet werden.

Eine Möglichkeit besteht darin, eine User Defined Function (UDF) zu erstellen. Die PL/SQL-Funktion in Listing 10 sieht immer noch gleich aus, aber das zusätzliche PRAGMA UDF sagt dem Compiler, dass die Funktion hauptsächlich in SQL-Anweisungen verwendet wird und deshalb anders kompiliert werden soll.

Listing 10: Definition einer User Defined Function

CREATE OR REPLACE FUNCTION f_price_category
   (in_quantity order_items.quantity%TYPE,
    in_price order_items.price_per_unit%TYPE) RETURN VARCHAR2
IS
   PRAGMA UDF; 
   v_total_price order_items.price_per_unit%TYPE;
   v_category VARCHAR2(6);
BEGIN
   …

Mit PRAGMA UDF ist die Abfrage etwa gleich schnell wie mit dem Function-based Index zuvor, aber ohne den Overhead des Indexes. Wie die ursprüngliche Abfrage führt sie einen Full Table Scan in der Tabelle ORDER_ITEMS durch, allerdings in 0.3 statt 10 Sekunden. Mit anderen Worten: 97% der Zeit in der ursprünglichen Abfrage wurde mit Kontextwechseln verbracht.

Skalares SQL-Macro

Wer bereits mit Oracle 21c arbeitet, hat eine weitere effiziente Variante zur Verfügung: Skalare SQL-Macros.

Im Gegensatz zu einer PL/SQL-Funktion wird ein SQL-Macro nicht während der Ausführung eines SQL-Befehls aufgerufen, sondern zum Parse-Zeitpunkt im SQL-Code ersetzt. Die ausgeführte Anweisung ist also reiner SQL-Code, so dass kein Context Switch mehr erforderlich ist. Es gibt zwei Arten von SQL-Macros:

• SQL Table Macros können in der FROM-Klausel einer SQL-Abfrage verwendet werden. Sie stehen bereits in Oracle 19c zur Verfügung.
• Skalare SQL-Macros können in SELECT-, WHERE-, GROUP BY- und ORDER BY-Klauseln verwendet werden. Sie können ab Oracle 21 verwendet werden.

Für unseren Zweck ist ein skalares SQL-Macro die geeignete Variante. Da der ersetzte Teil der Anweisung SQL-Syntax enthalten muss, müssen wir die Logik unseres Beispiels umschreiben. Anstelle einer IF ... THEN ... ELSE-Anweisung in PL/SQL schreiben wir einen CASE-Befehl, der im SQL-Macro als String zurückgegeben wird.

Listing 11: SQL-Macro als Ersatz für die PL/SQL-Funktion

CREATE OR REPLACE FUNCTION f_price_category_macro
   (in_quantity VARCHAR2,
    in_price VARCHAR2) RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
   v_category VARCHAR2(4000);
BEGIN
   v_category := 'CASE 
                     WHEN in_quantity * in_price < 100 THEN ''low''
                     WHEN in_quantity * in_price < 1000 THEN ''medium''
                     ELSE ''high''
                  END';
   RETURN v_category;
END f_price_category_macro;
/

Mit dem skalaren SQL-Macro läuft unsere Beispielabfrage ebenfalls schnell. Es findet kein Kontextwechsel zwischen SQL und PL/SQL statt. Die Antwortzeit ist in etwa die gleiche wie mit der User Defined Function.

Der Fachbereich ist nun zufrieden mit den Antwortzeiten der Abfragen, wünscht sich aber mehr Flexibilität. Anstelle einer ausprogrammierten Logik für die Ermittlung der Preiskategorien soll eine Konfigurationstabelle CATEGORIES verwendet werden, in welcher Minimal- und Maximalpreis pro Kategorie definiert werden können. Die Anpassung unserer Funktion F_PRICE_CATEGORIE ist einfach, wie Listing 12 zeigt: Anstelle einer ausprogrammierten IF ... THEN ... ELSE-Anweisung enthält sie nun eine SQL-Abfrage auf die Tabelle CATEGORIES.

Listing 12: PL/SQL-Funktion mit eingebetteter SQL-Abfrage

CREATE OR REPLACE FUNCTION f_price_category
   (in_quantity order_items.quantity%TYPE,
    in_price order_items.price_per_unit%TYPE) RETURN VARCHAR2
IS
   PRAGMA UDF;
   v_total_price order_items.price_per_unit%TYPE;
   v_category VARCHAR2(6);
BEGIN
   v_total_price := in_quantity * in_price;
 
   SELECT category
     INTO v_category
     FROM categories
    WHERE v_total_price >= min_price
      AND v_total_price <  max_price;
 
   RETURN v_category;
END f_price_category;
/

Die Lösung scheint einfach und elegant, das Ergebnis der Abfrage ist dasselbe, aber die Antwortzeit ist alles andere als erfreulich: Die SQL-Abfrage dauert nun mehrere Minuten!

Wo liegt hier das Problem? Obwohl die Funktion eine PRAGMA UDF enthält, gibt es dennoch viele Kontextwechsel. Für jede der 828270 Zeilen in der Tabelle ORDER_ITEMS findet ein Context Switch für die SQL-Anweisung statt und die Abfrage auf die Tabelle CATEGORIES muss jedesmal ausgeführt werden. Dies führt zu einem drastischen Anstieg der Antwortzeit.

Wie kann man dieses Problem lösen? Ein Function-based Index ist nicht möglich, da die Funktion nicht mehr deterministisch ist. Ein Index auf der Tabelle CATEGORIES hilft auch nicht weiter, da die Tabelle sehr klein ist. Etwas wie PRAGMA UDF innerhalb des SQL-Aufrufs ist nicht möglich. Es gibt keine einfache Lösung für dieses Problem.

Das Beispiel erinnert mich an die eingangs erwähnte Situation mit dem Performanceproblem, das ich für meinen Kunden lösen musste. Aufrufe von PL/SQL-Funktionen mit eingebetteten SQL-Aufrufen führen bei großen Datenmengen zu sehr langen Antwortzeiten. Die einzig praktikable Lösung besteht darin, die SQL-Abfrage umzuschreiben, um Funktionsaufrufe zu vermeiden. Für unsere Beispielabfrage können wir das auf verschiedene Weise tun. Eine einfache Implementierung ist die Umwandlung des eingebetteten SQL-Aufrufs in eine Subquery, wie Listing 13 zeigt. Das funktioniert, ist aber nicht sehr schnell (aber immer noch viel schneller als die PL/SQL-Lösung).

Listing 13: Umgeschriebene SQL-Abfrage mit Subquery

SELECT COUNT(*), SUM(quantity)
  FROM order_items
 WHERE (SELECT category 
          FROM categories
         WHERE quantity * price_per_unit >= min_price
           AND quantity * price_per_unit <  max_price) = 'high';
 
...
 
Elapsed: 00:00:01.797

Eine bessere und schnellere Lösung besteht darin, eine einfache SQL-Abfrage zu schreiben, welche die Tabelle ORDER_ITEMS und die Konfigurationstabelle CATEGORIES mit einem Non-Equi Join verknüpft. Die Antwortzeit der SQL-Abfrage in Listing 14 ist schneller als alle anderen zuvor beschriebenen Implementierungen.

Listing 14: Umgeschriebene SQL-Abfrage mit Non-Equi Join

SELECT COUNT(*), SUM(quantity)
  FROM order_items oi, categories cat
 WHERE cat.category = 'high'
   AND oi.quantity * oi.price_per_unit >= cat.min_price
   AND oi.quantity * oi.price_per_unit <  cat.max_price;
 
...
 
Elapsed: 00:00:00.127

Fazit

Werden SQL-Funktionen in WHERE-Bedingungen von SQL-Anweisungen verwendet, müssen wir dem Optimizer Hilfsmittel zur Verfügung stellen, um die Selektivität berechnen zu können. Ob wir dazu Function-based Indexes, Virtual Columns oder Extended Statistics verwenden, spielt keine große Rolle. In allen drei Varianten werden intern Virtual Columns erstellt.

Bei PL/SQL-Funktionen können die gleichen Hilfsmittel verwendet werden, sofern die Funktionen deterministisch sind und entsprechend deklariert werden. Zusätzlich müssen Context Switches zwischen SQL und PL/SQL vermieden werden. Dies kann mit User Defined Functions oder SQL-Macros erreicht werden.

Auf jeden Fall vermieden werden sollten Aufrufe von PL/SQL-Funktionen in SQL-Anweisungen, wenn diese wiederum SQL-Befehle ausführen. Solche verschachtelten SQL-Aufrufe können zu enormen Performanceeinbußen führen.

Dani Schnider

Dani Schnider arbeitet als Principal Oracle & DWH Consultant bei Callista, einem Schweizer IT-Beratungsunternehmen. Er ist seit über 20 Jahren im Datenbank-Consulting tätig, hauptsächlich im Bereich D
>> Weiterlesen

MySQL HeatWave – OLTP, OLAP und ML für MySQL

Attribute Clustering, unterschätztes Oracle-Feature (Teil 3)

Attribute Clustering, unterschätztes Oracle-Feature (Teil 2)

Attribute Clustering, ein weithin unterschätztes Feature der Oracle-Datenbank

Partitionierung aus Designsicht – Eine praktische Anleitung

JSON in der Oracle-Datenbank mit NoSQL-APIs und Datenbank-API für MongoDB