Über unsMediaKontaktImpressum
Jürgen Sieben 20. Oktober 2015

To Trigger or not to Trigger

Dieser Artikel ist der erste einer kleinen Reihe von Fundstellen, die mich bei der Lektüre von Fachliteratur zu Widerspruch gereizt haben. Mir geht es natürlich nicht darum, mit dem Finger auf andere Autoren zu zeigen (ein Teil dieser Reihe behandelt auch eine Fundstelle in meinem PL/SQL-Buch), diese Fundstellen stellen vielmehr den Anker dar, an dem ich ein Thema erläutern möchte, von dem ich glaube, dass es für viele Leser interessant sein könnte. Zum Start eine Behauptung über Trigger und die Frage: Wann und wie soll man Trigger denn nun einsetzen?

Die Fundstelle

In einem (ansonsten überaus lesenswerten) Buch über Oracle Performance Tuning stolperte ich über folgende Behauptung:

"Aus Performancegründen sollten Sie natürlich so wenig wie möglich in Triggern gestalten, weil sich dadurch ja alle Transaktionen auf der Tabelle komplexer und auch zeitaufwändiger gestalten. Der zusätzliche Zeitaufwand kann nicht bemerkbar sein oder auch nicht (sic!); was einfach davon abhängt, wie viel Sie in den Trigger packen. Alle Aktionen innerhalb des Triggers erweitern ja die bereits bestehende Transaktion. Allerdings muss man auch sagen, dass Trigger ein erprobtes und vielgenutztes Datenbank-Feature sind, was Sie nicht von deren Einsatz abhalten sollte."

Hmm - der Absatz ist natürlich ein wenig verunglückt, nicht nur wegen des logischen Fehlers in der Verneinung, sondern auch im letzten Satz, bei dem ich mich frage, warum mich die Tatsache, dass der Trigger "erprobt und vielgenutzt" sei, von seinem Einsatz abhalten sollte. Aber darum geht es mir nicht. Mich hat zum Widerspruch gereizt, dass behauptet wird, die Performance-Einbußen eines Triggers hingen (ausschließlich) von der Menge Code ab, die im Trigger ausgeführt wird. Das legt nahe, dass ein Trigger um so schneller ist, je weniger Code in ihm ausgeführt wird.

Nun mag das zwar auch eine Rolle spielen (wenn etwas gemacht wird, dauert das eben immer länger, als wenn man es lässt), doch drängt sich mir die Frage auf, ob Sie in Ihren Trigger Logik "aus Spaß" einfügen, die Sie also auch weglassen könnten. Falls dies so wäre, würde ich mich natürlich der Empfehlung anschließen, diesen überflüssigen Code wegzulassen. Doch was ist, wenn Sie die Logik nun einmal ausführen müssen? Verbieten sich Trigger dann wegen der Codemenge? Grund genug, einmal zu betrachten, wie das mit den Triggern eigentlich genau ist und welche Optionen sich uns bieten.

Das Problem

Wie so häufig, ist der Trigger nicht "grundsätzlich schlecht". Das zentrale Problem, das Trigger auslösen, sind die Umgebungswechsel, wie ich das in meinem letzten Artikel  [1] schon grob geschildert hatte. Diese Umgebungswechsel resultieren daraus, dass die Logik des Triggers in PL/SQL, die Auslösung des Triggers jedoch in SQL implementiert ist, so dass beim Auslösen eines Triggers aus SQL ein Umgebungswechsel zu PL/SQL resultiert. Dies passiert zwar immer und bei jedem Triggertyp, doch ist eine Triggerart für die Performanz von besonderer Bedeutung: Der Zeilentrigger, da dieser für jede, durch die DML-Anweisung in SQL betroffene Zeile, einmal auslöst. Da eine DML-Anweisung sehr viele Zeilen betreffen kann, potenziert sich hier also das grundsätzlich immer vorhandene Problem und macht sich dadurch entsprechend bemerkbar. Ist Ihr Problem jedoch, dass sehr viele Benutzer sehr viele einzelne DML-Anweisungen ausführen, ist auch ein Anweisungstrigger bereits ein Problem, einfach, weil die Umgebungswechsel nun auch auf Anweisungsebene eine signifikante Anzahl erreichen.

Datenbanktrigger, die bei sonstigen Ereignissen auslösen, wie zum Beispiel dem An- oder Abmelden eines Benutzers an der Datenbank, können im Regelfall für die folgende Betrachtung ignoriert werden. Hier treten die triggernden Ereignisse zu selten auf, als dass die Zeit für die Umgebungswechsel bedeutsam würde.

Wie relevant ist dieses Problem?

Um ein Gefühl für die Größe dieses Problems zu erhalten, müssen wir zunächst einmal relevant viel Arbeit in der Datenbank verrichten. Sind die Zeilenmengen oder die Anzahl der einzelnen DML-Anweisungen gering, sind es auch die negativen Auswirkungen der Trigger und die gesamte Antwortzeit dürfte in einem tolerablen Rahmen liegen.

Um die Auswirkungen zu beobachten, erstellen wir uns einen Beispielbenutzer DOAG, dem ich der Einfachheit halber die DBA-Rolle zugewiesen habe. Dadurch hat er Zugriff auf recht viele Datenbankobjekte, mit deren Metadaten ich im folgenden arbeiten möchte. Ich verwende folgende Beispieltabelle:

SQL> create table test_table(
  2    owner varchar2(30),
  3    object_id number,
  4    object_name varchar2(30),
  5    object_type varchar2(30),
  6    constraint pk_test_table primary key(object_id)
  7  );

Tabelle wurde erstellt.

SQL> set timing on;
SQL> select count(*)
  2    from all_objects;

  COUNT(*)
----------
     92248
Abgelaufen: 00:00:00.32

SQL> insert into test_table(owner, object_id, object_name, object_type)
  2  select owner, object_id, object_name, object_type
  3    from all_objects;

92249 Zeilen erstellt.
Abgelaufen: 00:00:00.86

Das Einfügen der Daten inklusive Pflege des Unique-Index für den Primärschlüssel ging recht flott. Wenn Sie diese Daten aktualisieren, geht auch das recht zügig:

SQL> update test_table
  2     set object_name = initcap(object_name);

92249 Zeilen aktualisiert.
Abgelaufen: 00:00:00.35

Lassen Sie uns alternativ einmal einen Blick darauf werfen, wie die Laufzeiten wären, wenn viele Benutzer jeweils eine Zeile in die Tabelle einfügen würden. Wir machen uns die Dinge etwas leichter und simulieren keine parallele Bearbeitung durch viele Benutzer, sondern fügen seriell viele Zeilen in jeweils einer insert-Anweisung ein, und zwar durch diesen Code:

SQL> alter session set plsql_optimize_level = 0;

Session wurde geändert.

SQL> create or replace procedure fill_test_table
  2    authid current_user
  3  as
  4    cursor object_cur is
  5      select owner, object_id, object_name, object_type
  6         from all_objects;
  7  begin
  8    for obj in object_cur loop
  9      insert into test_table(owner, object_id, object_name, object_type)
 10      values (obj.owner, obj.object_id,
 10              obj.object_name, obj.object_type);
 12    end loop;
 13  end fill_test_table;
 14  /

Prozedur wurde erstellt.

Ich musste, um ein realistisches Bild zu zeichnen, den Parameter plsql_optimize_level auf 0 zurückstellen, damit der Compiler keine interne Optimierung und damit eine Mengenverarbeitung durchführen kann. Ich habe die Prozedur zudem als Invokers-Rights-Prozedur ausgeführt, damit die Anzahl der Zeilen in TEST_TABLE zwischen beiden insert-Varianten identisch ist (ansonsten würden über Rollenrechte zugewiesene Datenbankobjekte nicht eingefügt). Die Laufzeit bei diesem Ansatz ist ohne Trigger deutlich langsamer als bei der Mengenverarbeitung. Aber das war zu erwarten und ist nicht wirklich zu ändern:

SQL> call fill_test_table();

Aufruf wurde abgeschlossen.
Abgelaufen: 00:00:04.46

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

Nun jedoch fügen wir einen Zeilentrigger hinzu, der die gleiche Aufgabe wie unsere update-Anweisung von vorhin durchführt:

SQL> create trigger trg_test_table_briu
  2  before insert or update on test_table
  3  for each row
  4  begin
  5    :new.object_name := initcap(:new.object_name);
  6  end;
  7  /

Trigger wurde erstellt.

SQL> update test_table
  2     set object_name = object_name;

92249 Zeilen aktualisiert.

Abgelaufen: 00:00:01.78

Der analoge Aufruf über eine Prozedur update_test_table, die das gleiche Update erzeugt, zeigt, dass auch hier ein zweiter Umgebungswechsel durch den Trigger erforderlich ist:

SQL> call update_test_table();

Aufruf wurde abgeschlossen.
Abgelaufen: 00:00:07.12

Die Datenbank macht aber immer noch einen guten Job, denn hier werden immerhin 92.249 Umgebungswechsel in knapp 2 Sekunden durchgeführt, also in etwa 0,2 tausendstel Sekunden pro Umgebungswechsel. Die PL/SQL-Prozedur hat ja durch die eigene, zeilenweise Arbeitsweise bereits einen Umgebungswechsel hin zu SQL gemacht, der durch den Trigger nun um eine "Rücküberweisung" nach PL/SQL erweitert wird. Das macht sich deutlich bemerkbar.

Noch schlimmer wird es, wenn wir im Trigger noch einen weiteren Umgebungswechsel einfügen. Zum Beispiel durch die Verwendung der Funktion user, die, wie Sie wissen, als select user from dual implementiert ist und daher auf die SQL-Seite muss:

SQL> alter table test_table add (change_user varchar2(30));

Tabelle wurde geändert.

SQL> create or replace trigger trg_test_table_briu
  2  before insert or update on test_table
  3  for each row
  4  begin
  5    :new.object_name := initcap(:new.object_name);
  6    :new.change_user := user;
  7  end;
  8  /

Trigger wurde erstellt.

SQL> update test_table
  2     set object_name = object_name;

92249 Zeilen aktualisiert.

Abgelaufen: 00:00:03.90

Leider ist es im Trigger nicht möglich, den Aufruf der Funktion user irgendwie zu vermeiden, weil der gesamte Code pro betroffener Zeile einmal ausgeführt wird. Der zusätzliche Aufwand ist auch bei unserer Prozedur deutlich zu spüren:

SQL> call update_test_table();

Aufruf wurde abgeschlossen.
Abgelaufen: 00:00:09.75

Die Anweisung zeigt nun bereits eine signifikant längere Laufzeit, sie benötigt bei der Mengenverarbeitung gut zehnmal länger für die im Prinzip gleiche Arbeit (wobei bei diesen geringen Zeiten die Streuung der Messungen relativ groß sein kann). Ohne Trigger hätte die initcap-Funktion ebenso wie der Änderungsbenutzer aus user direkt in die update-Anweisung eingefügt werden können, ohne die Einbuße bei der Performanz. Halten wir aber auch fest: Langsam ist sehr relativ. Wir haben im Beispiel über 90.000 Zeilen in knapp 4 Sekunden aktualisiert, pro Zeile also in etwa 0,4 tausendstel Sekunden. Das ist sehr viel Zeit, wenn Sie 90.000.000 Zeilen aktualisieren müssen (Bei linearer Zunahme der Ausführungszeit entspricht das immerhin beinahe einer dreiviertel Stunde), aber wenig, wenn Sie nur 500 Zeilen zu aktualisieren haben. Aber auch diese Zeit kann als zu lang empfunden werden, wenn Sie zeilenweise arbeiten (müssen), wie unsere Prozedur eindrucksvoll zeigt.

Natürlich wird die Sache noch einmal aufwändiger, wenn Sie einen zweiten Trigger – entweder auf den gleichen oder einen weiteren Event – anfügen, oder wenn die Trigger mehr PL/SQL-Arbeit zu leisten haben. Aber das Prinzip sollte klar sein.

Lösungsansätze

Bei den Lösungsansätzen sollten man zunächst einmal im Blick haben, warum Trigger überhaupt eingefügt wurden. Der große Vorteil eines Triggers besteht in der Zentralisierung von Logik. Ist diese einmal im Trigger definiert, halten sich alle DML-Anweisungen der Anwendung, aber auch ad hoc-Anweisungen, an die dort definierten Regeln. Die einzige Möglichkeit, diese Regeln zu umgehen, besteht darin, einen Trigger zu deaktivieren oder ganz zu löschen. Eine Lösung des Problems sollte diesen Vorteil nach Möglichkeit erhalten.

Andererseits kann man sicherlich auch eine leichte Verschlechterung dieses Komforts akzeptieren, wenn dadurch ein anderer Vorteil größer würde. Und damit meine ich nicht nur die Performanz, sondern die Übersichtlichkeit und Wartbarkeit des Codes. Trigger haben den Nachteil, die Geschäftslogik auf viele unterschiedliche "Programminseln" zu verteilen. Nehmen wir an, Logik beziehe sich auf Daten, die in einer einfachen 1:n-Relation in zwei Tabellen gespeichert werden. Wo legen Sie nun die Logik für einen Datensatz an? Teilen wir die Logik auf mehrere Trigger auf, verschärfen wir nicht nur die Performanzproblematik, sondern zudem wird die Wartung unseres Codes aufwändiger und das Verständnis erschwert. Wenn wir auch dafür eine Lösung hätten, wäre das ein Gewinn.

Vermeidung von Triggern durch SQL

Der erste Lösungsansatz besteht darin, die Aufgaben der Trigger durch SQL-Funktionalität zu ersetzen. Hier ist vor allem die neue Identity-Column zu erwähnen, die es ermöglicht, einen Trigger, der einen Primärschlüssel erzeugt, durch eine Autowertspalte zu ersetzen. Eine weitere Möglichkeit besteht in der Nutzung der erweiterten default-Klausel einer Spalte, aber beide Möglichkeiten beziehen sich auf Datenbankversion 12c und aufwärts. Mit dieser Version ist es möglich, einige Trigger einzusparen und ersatzlos durch SQL zu ersetzen. Lässt sich ein Trigger auf diese Weise einsparen, sollten Sie das in jedem Fall auch tun.

Compound-Trigger

Wenn Trigger schon erforderlich sind, könnte die Anzahl dieser Trigger durch einen Compound-Trigger verringert werden. Diese Trigger, die ab Version 11g zur Verfügung stehen, fassen alle auslösenden Events in einem einzigen Trigger zusammen. Dieser Ansatz sorgt zunächst einmal dafür, dass die gesamte Logik einer Tabelle an einer Stelle versammelt ist, löst aber nicht das Problem von 1:n-Relationen. Er sieht in der Grundform so aus (wobei die Stubs nicht geschrieben werden müssen):

SQL> create or replace trigger trg_test_table_comp
  2    for delete or insert or update on test_table
  3    compound trigger
  4
  5    before statement -- STUB
  6    is
  7    begin
  8      null;
  9    end before statement;
 10
 11    before each row
 12    is
 13    begin
 14      :new.object_name := initcap(:new.object_name);
 15      :new.change_user := user;
 16    end before each row;
 17
 18    after each row -- STUB
 19    is
 20    begin
 21      null;
 22    end after each row;
 23
 24    after statement -- STUB
 25    is
 26    begin
 27      null;
 28    end after statement;
 29  end;
 30  /

Trigger wurde erstellt.

SQL> update test_table
  2     set object_name = object_name;

92249 Zeilen aktualisiert.
Abgelaufen: 00:00:03.95

Dieser Trigger läuft zunächst einmal genauso lange wie der vorher existierende Trigger, den ich für die nächsten Versuche vorab gelöscht habe. Dieser Trigger kann also die Umgebungswechsel nicht verhindern. Deutlich wird dies, wenn der Zweig after row durch weitere (ebenfalls nicht sehr intelligente) Logik erweitert wird:

...
 18    after each row
 19    is
 20    begin
 21      if :new.change_user != user then
 22        raise_application_error(-20000, 'Falscher Benutzer');
 23      end if;
 24    end after each row;
...

Wir verdoppeln den Aufwand, denn nun muss auch nach jeder Zeile ein Umgebungswechsel nach PL/SQL (und von dort zurück nach SQL) durchgeführt werden:

SQL> update test_table
  2     set object_name = object_name;

92249 Zeilen aktualisiert.
Abgelaufen: 00:00:04.61

Doch hat der Compound-Trigger einen großen Vorteil: Durch seine Struktur können wir in ihm auch globale Variablen, Cursor etc. definieren. Nutzen wir diese Möglichkeit und speichern den aktuellen Benutzer in einer globalen Variablen g_user:

SQL> create or replace trigger trg_test_table_comp
  2    for delete or insert or update on test_table
  3    compound trigger
  4
  5    g_user varchar2(30) := user;
...

Nun ersetzen wir die Aufrufe der Funktion user durch eine Referenz auf g_user und sehen, dass die Datenbank die Hälfte der Roundtrips einspart:

SQL> update test_table
  2     set object_name = object_name;

92249 Zeilen aktualisiert.
Abgelaufen: 00:00:03.10

Mit dieser Variante sind wir, trotz zweier SQL-Events, schneller als mit einem normalen Trigger bei nur einem Ereignis. Wenn schon Trigger, dann also zumindest Compound-Trigger, zumal sich mit diesen Trigger auch elegant die lästigen Mutating Table Probleme lösen lassen.

Vermeidung von Triggern durch Table-API

Die "große" Lösung besteht in der Vermeidung von Triggern durch eine Table-API. Voraussetzung hierfür ist, dass nicht unkontrolliert DML-Anweisungen gegen die Tabelle ausgeführt werden können. Leider können Sie dies nicht verhindern, solange Sie sich als der Eigentümer dieser Tabellen an der Datenbank anmelden. Melden Sie sich als ein anderer Benutzer an, müssen Sie vom Eigentümer das Recht erhalten haben, eine Tabelle zu benutzen. Und hier setzt die Lösung an, denn genau das wird der Eigentümer der Tabelle nicht tun. Stattdessen richtet er eine Prozedur ein, auf die Sie ein Ausführungsrecht erhalten. Diese Prozedur wiederum schreibt die Daten in die Tabelle.

Da wir bereits eine solche Prozedur haben, können wir sie auch benutzen, um den Ablauf einer Table-API zu simulieren. Unsere Update-Prozedur macht dabei folgende Dinge: Sie wandelt die Daten vor der Verarbeitung in das gewünschte Format und vermeidet häufige Umgebungswechsel durch Implementierung einer Bulk-Strategie. Der erste der beiden Punkte ist nicht weiter spannend, das hatten wir in einer update-Anweisung ja auch bereits realisiert, der Punkt ist die Bulk-Verarbeitung. Dabei werden mehrere Zeilen im PL/SQL-Code aufbereitet und im Bulk an die SQL-Seite zur Abarbeitung weitergegeben. Wie dies grundsätzlich geschieht, hatte ich bereits im letzten Artikel geschildert [1]. Für unser Beispiel ist es sogar noch einfacher, weil die for record in cursor-Schleife unserer Prozedur "von Hause aus" diese Bulk-Implementierung durchführt, sobald wir den Compiler dies tun lassen. Um zu zeigen, wohin uns unsere Table-API führt, lösche ich also den Trigger, realisiere die Triggerfunktionalität in der Prozedur und kompiliere diese mit dem Setting PLSQL_OPTIMIZE_LEVEL auf mindestens 2:

SQL> drop trigger trg_test_table_comp;

Trigger wurde gelöscht.

SQL> alter session set plsql_optimize_level = 3;

Session wurde geändert.

SQL> create or replace procedure update_test_table
  2  as
  3    cursor object_cur is
  4      select owner, object_id, object_name, object_type
  5         from all_objects;
  6    g_user varchar2(30) := user;
  7  begin
  8    for obj in object_cur loop
  9      update test_table
 10        set object_name = initcap(obj.object_name),
 11            change_user = g_user
 12      where object_id = obj.object_id;
 13    end loop;
 14  end update_test_table;
 15  /

Prozedur wurde erstellt.

SQL> call update_test_table();

Aufruf wurde abgeschlossen.
Abgelaufen: 00:00:01.07

Nun ist unsere Performanz besser als beim zeilenweisen Aufruf ohne Trigger, zudem sind die Funktionen des Triggers implementiert. Im direkten Vergleich zu "reinem" SQL ohne Trigger ist diese API-Lösung dann aber doch nicht ganz überzeugend:

SQL> update test_table
  2     set object_name = initcap(object_name),
  3         change_user = user;

92251 Zeilen aktualisiert.
Abgelaufen: 00:00:00.31

Aber eben immer noch deutlich besser als alle anderen Implementierungen. Zudem bietet die Implementierung der Prozedur noch Raum für Verbesserungen und die reine SQL-Variante ist eben auch nur dann möglich, wenn die Daten bereits in der Datenbank vorhanden sind. Die Idee der Table-API ist es aber, eine Kollektion von Werten aus Anwendungen entgegen zu nehmen und möglichst effizient in die Datenbank zu schreiben.

Zusammenfassung

Wie üblich gibt es bei der Datenbank kein einfaches Wahr oder Falsch. Trigger können, im richtigen Kontext angewendet, sehr sinnvolle Datenbankobjekte sein. Beispiele wären Stammdatentabellen und andere, weniger intensiv genutzte Tabellen. Trigger sind aber mit einer performanten Anwendung nicht in Einklang zu bringen, wenn entweder sehr viele Einzelanweisungen oder Mengenoperationen auf Tabellen bearbeitet werden sollen. Dann eignen sich Table-APIs besser. Table-APIs setzen allerdings eine architektonische Entscheidung voraus, dass nicht jeder Anwendungsbenutzer unmittelbar auf die Tabellen schreiben darf. Das mag durchsetzbar sein oder nicht, aber damit steht und fällt die Strategie. Sobald jedoch die Aufgabe des Triggers auch durch eine SQL-Funktionalität wahrgenommen werden kann, sollte dies in jedem Fall gemacht werden, auch durch entsprechende Refaktorisierung des Codes.

Da dies eine PL/SQL-Kolumne ist, verbietet sich der Hinweis, dass für eine mengenorientierte Verarbeitung PL/SQL nicht unbedingt erforderlich ist, sondern auch in Java oder anderen Sprachen implementiert werden kann. Stichwort wäre hier die batch-update-Methode des Datenbanktreibers.

Gemeinsames Ziel aller Programmierung gegen die Datenbank ist die Implementierung mengenorientierter Ansätze und die Vermeidung zeilenbasierter Programmierung. Die jedoch wird durch Trigger, gerade durch Zeilentrigger, unausweichlich in das Projekt gebracht. Die Menge Code des Triggers hat nur mittelbar mit der gesamten Ausführungszeit zu tun, zumal die dort implementierte ja ohnehin irgendwo gerechnet werden muss. Es ist der Aufwand für den Umgebungswechsel, der hier den limitierenden Faktor darstellt, daher sollte hierauf auch in besonderem Maße geachtet werden.

Autor

Jürgen Sieben

Jürgen Sieben ist Autor des Buchs "Oracle PL/SQL: Das umfassende Handbuch", das bei Galileo erschienen ist und Geschäftsführer der ConDeS GmbH
>> Weiterlesen
Bücher des Autors:

botMessage_toctoc_comments_9210