Neue PL/SQL Features II - PRAGMA UDF

Der heutige Blogeintrag setzt unsere eine kleine Reihe rund um interessante neue Features in PL/SQL fort. Diesmal geht es um die Performance bei der Ausführung von PL/SQL. Wie die meisten wissen, ist SQL nicht gleich PL/SQL. Wenn also PL/SQL Funktionen von einem SQL-Statement aus aufgerufen werden, dann muss ein sogenannter Context Switch stattfinden. Dieser Switch ist recht aufwändig. Das lässt sich mit einem einfachen Beispiel verdeutlichen. Wir durchlaufen in PL/SQL eine Schleife und weisen einer Variablen einen Wert zu. Zuerst mit SQL-Mitteln, also mit einem Context Switch pro Schleifendurchlauf, und einmal mit reinem PL/SQL.

SQL> declare
  2    v_i number;
  3  begin
  4    for i in 1..1000000 loop
  5      select i into v_i from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:30.64
SQL> declare
  2    v_i number;
  3  begin
  4    for i in 1..1000000 loop
  5      v_i := i;
  6    end loop;
  7  end;
  8  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.09

Man sieht sofort den Unterschied, 30 Sekunden zu 0,1 Sekunden, die reine PL/SQL Ausführung ist also um Faktor 300 schneller. Umgekehrt ist das natürlich genauso. Wenn PL/SQL aus SQL gerufen wird. Und hier setzt ein neues Feature der Version 12.1 an. Wenn klar ist, das eine PL/SQL-Funktion mehr oder weniger ausschließlich aus SQL-Statements aufgerufen wird, dann kann man mittels PRAGMA UDF den Compiler anweisen, einen Teil der zum Context Switch gehörigen Funktionen bereits beim Kompilieren mit einzubauen um den eigentlichenn Context Switch zur Ausführung zu verkürzen. Nehmen wir wieder ein einfaches Beispiel, eine Funktion zum Ermitteln eines Teilstücks aus einem Text. Zum Test habe ich eine Tabelle aus ALL_OBJECTS erstellt.

SQL> create table my_obj as select owner, object_name, object_type from all_objects;

Tabelle wurde erstellt.

SQL> insert into my_obj select * from my_obj;

75645 Zeilen erstellt.

SQL> insert into my_obj select * from my_obj;

151290 Zeilen erstellt.

SQL> insert into my_obj select * from my_obj;

302580 Zeilen erstellt.

SQL> insert into my_obj select * from my_obj;

605160 Zeilen erstellt.

SQL> insert into my_obj select * from my_obj;

1210320 Zeilen erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

Zuerst wird die Funktion wie gewohnt erstellt und die Performance gegen die Testtabelle geprüft.

SQL> create or replace function fnc_betwen_str(p_text in varchar2, p_start in number, p_ende in number) return varchar2
  2  is
  3  begin
  4    return substr(p_text, p_start , p_ende - p_start +1);
  5  end;
  6  /

Funktion wurde erstellt.

SQL> select avg(length(fnc_betwen_str(object_name, 2, 6))) from my_obj;

Abgelaufen: 00:00:10.31

Der zweite Test wird nun mit dem neuen Feature PRAGMA UDF durchgeführt, zu erkennen in Zeile 3 der Funktion.

SQL> create or replace function fnc_betwen_str(p_text in varchar2, p_start in number, p_ende in number) return varchar2
  2  is
  3    PRAGMA UDF;
  4  begin
  5    return substr(p_text, p_start , p_ende - p_start +1);
  6  end;
  7  /

Funktion wurde erstellt.

SQL> select avg(length(fnc_betwen_str(object_name, 2, 6))) from my_obj;

Abgelaufen: 00:00:03.17

Immerhin ist also eine Beschleunigung um Faktor 3 zu erkennen. Und das ganze ohne Tuning oder Änderungen an der Programmlogik. Man muss aber der Fairness dazusagen, dass dieses Feature kein Allheilmittel ist. So hängt es beispielsweise von den verwendeten Datentypen ab. ob das PRAGMA UDF eine Wirkung erzielt oder nicht. Ist ein DATE involviert, bringt es offenbar keine Verbesserung.

SQL> create or replace function trunc_date(p_datum in date) return date
  2  is
  3  begin
  4    return trunc(p_datum);
  5  end;
  6  /

Funktion wurde erstellt.

Abgelaufen: 00:00:00.05
SQL>
SQL> select max( trunc_date(sysdate)) from my_obj;

Abgelaufen: 00:00:10.31

SQL> create or replace function trunc_date(p_datum in date) return date
  2  is
  3    PRAGMA UDF;
  4  begin
  5    return trunc(p_datum);
  6  end;
  7  /

Funktion wurde erstellt.

SQL> select max( trunc_date(sysdate)) from my_obj;

Abgelaufen: 00:00:10.28

Also gilt wie so oft, probieren geht über studieren. Am Ende muss man einfach testen, ob es für den jeweiligen Einsatzzweck eine Verbesserung bedeutet oder nicht. Wir können Sie gerne dabei unterstützen.