20. Juli 2015

Native Compilation für PL/SQL

Nachdem ich, in meinem monatlichen Oracle Developer Monthly Webcast, im Juli etwas zum Thema PL/SQL Native Compilation vorgestellt hatte, kam mir die Idee, das ganze gleich auf diesem Blog etwas ausführlicher zu behandeln.
Seit geraumer Zeit bietet die Oracle-Datenbank an, PL/SQL Code in Native Code zu übersetzen, natürlich mit dem Ziel, dass der Code schneller ausgeführt wird ("Native Compilation"). Standardmäßig wird beim Kompilieren eines PL/SQL-Objekts ein "Bytecode" generiert, der dann von einer "PL/SQL VM" ausgeführt wird. Natürlich speichert die Datenbank den Quellcode ebenso ab - denn der kann ja über die View USER_SOURCE angesehen werden. So weit, so gut.
In früheren Versionen war das Native Compile noch sehr aufwändig. Das Konzept war, dass der PL/SQL-Code zunächst in C-Code übersetzt wird; aus dem generiert ein C-Compiler dann Native-Code für die jeweilige Plattform, auf welcher die Datenbank läuft. Das hatte allerdings einige Probleme.
  • C-Compiler sind nicht auf allen Plattformen frei verfügbar; oft müssen sie teuer eingekauft werden
  • Der Setup für Compiler und Linker musste mit Makefiles eingerichtet werden, dass war je nach Plattform sehr aufwändig
  • Das entstandene Shared Object (.so oder .dll) lag außerhalb der Datenbank; das hat Auswirkungen auf Backup und Recovery oder allgemein gesagt: auf den Betrieb der Datenbank
Daher wurder der Vorgang mit dem Release 11.1 der Datenbank massiv vereinfacht. Das wichtigste ist, dass externe Compiler und Linker aus dem Vorgang eliminiert wurden - die Datenbank generiert den Native Code selbstständig. Demzufolge fallen auch alle einzurichtenden Makefiles weg. Und schließlich kann der Native-Code als Teil der Datenbank gespeichert werden, so dass keine externen Objekte mehr nötig sind. Das alles wird durch Umstellen eines Session-Parameters aktiviert: PLSQL_CODE_TYPE. Ein gutes Beispiel, um den Effekt einer Native-Compilation zu zeigen, sind rechenintensive Programme - hier mit den Fibonacci-Zahlen ein üblicher Verdächtiger als Beispiel.
create or replace function fib (n number) return number is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err

Funktion wurde erstellt.

select fib(32) from dual
/

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:01.88
Standardmäßig wird PL/SQL-Code nicht nativ kompiliert; im Moment sollten wir also die Laufzeit von interpretiertem Code sehen. Das kann aber auch anhand der Data Dictionary View USER_PLSQL_OBJECT_SETTINGS geprüft werden.
SQL> select name, type, PLSQL_CODE_TYPE from user_plsql_object_settings where name='FIB'

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     INTERPRETED

1 Zeile wurde ausgewählt.
Dann stellen wir die Funktion auf Native-Code um - also Parameter umstellen und das PL/SQL Objekt neu kompilieren.
SQL> alter session set PLSQL_CODE_TYPE=NATIVE;

Session wurde geändert.

SQL> alter function FIB compile;

Funktion wurde geändert.

SQL> select name, type, PLSQL_CODE_TYPE from user_plsql_object_settings where name='FIB';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     NATIVE

1 Zeile wurde ausgewählt.
Nun sollte man ja auch eine Änderung im Laufzeitverhalten sehen ... wir erinnern uns: der folgende Aufruf brauchte im interpretierten Modus 1,88 Sekunden.
select fib(32) from dual;

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.
 
Abgelaufen: 00:00:00.98
Das ganze ist schon mal doppelt so schnell - und wir werden das noch schneller machen. Vorher jedoch noch eine Anmerkung zu den Erwartungen, die man an eine nativ kompilierte PL/SQL-Funktion oder Prozedur haben kann. Bei rechenintensivem Code (wie dem Beispiel hier) verspricht native Compilation gute Ergebnisse. Keine besonderen Vorteile bringt die Native Compilation, wenn die Prozedur sich größtenteils mit I/O beschäftigt - und dieses I/O kann die Arbeit mit Datenbankobjekten (SELECT und DML), dem Netzwerk (UTL_HTTP, UTL_TCP, UTL_SMTP) oder auch dem Dateisystem (UTL_FILE) sein. In all diesen Fällen wird der Effekt der Native Compilation nicht so stark ins Gewicht fallen. Auch hierzu ein Beispiel: Diese Prozedur selektiert die View ALL_OBJECTS und addiert die String-Längen aller Objektnamen zusammen.
create or replace function get_len_allobjects return number is
  l_len number := 0;
begin
  for i in (select object_name from all_objects) loop
    l_len := l_len + length(i.object_name);
  end loop;
  return l_len;
end;
/ 
sho err
Der Löwenanteil dieser Prozedur ist die Cursor-Loop, mit der sie sich durch die Inhalte der View ALL_OBJECTS durcharbeitet; also I/O. Probieren wir es aus - erst mal führen wir das ganze interpretiert aus ...
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:11.95
... danach als Native Code ...
SQL> alter session set plsql_code_type=native;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:11.42
Man sieht keinen gewaltigen Unterschied - und das ist absolut logisch, denn der PL/SQL-Teil, der hier vom Native-Compile profitieren kann, ist im Vergleich zur Cursor-Loop verschwindend gering ... solche Kandidaten lassen sich auch per Native Compile nicht schneller machen.
Aber zurück zu obigem Beispiel mit den Fibonacci-Zahlen. Der PL/SQL-Code verwendet den Datentypen NUMBER, welcher in der Oracle-Datenbank zwar die größten Zahlenraum und die bestmögliche Präzision bietet; die Frage ist aber, ob man das wirklich braucht. Speziell für ganzzahlige Variablen kommen in PL/SQL die Datentypen PLS_INTEGER (BINARY_INTEGER ist ein Synonym) oder SIMPLE_INTEGER in Frage. Besonders der SIMPLE_INTEGER, den es seit Version 11.2 gibt, ist interessant. Er bietet den gleichen Zahlenraum wie PLS_INTEGER, verhält sich im Gegensatz zu diesem aber bei numerischen Überläufen anders und er kennt kein SQL NULL. Er ist vergleichbar mit int-Datentypen in Programmiersprachen außerhalb der Datenbank. Das ist für Native-Code deshalb interessant, weil sich Logik mit diesem Datetypen gut auf die CPU auslagern lässt. Hier ein Auszug aus dem Handbuch zu SIMPLE_INTEGER.
If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.
Probieren wir es aus und ersetzen den NUMBER-Datentypen in der Funktion durch SIMPLE_INTEGER.
create or replace function fib (n simple_integer) return simple_integer is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err
Zur Erinnerung: Mit dem NUMBER-Datentypen lief diese Prozedur 1,88 Sekunden interpretiert, und 0,98 Sekunden als Native-Code - schauen wir uns das mit dem SIMPLE_INTEGER an ...
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:01.43
... also bereits im interpretierten Modus etwas schneller. Mit Native Code sieht das ganze so aus:
SQL> alter session set plsql_code_type=native;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.53
... das ist fast doppelt so schnell wie mit dem NUMBER-Datentypen - die Wahl des richtigen Datentypen kann hier also nochmal einen richtigen Unterschied machen. Die folgende Tabelle zeigt die Performance-Unterschiede für die verschiedenen möglichen Datentypen.
Nochmal zusammengefasst:
  • PLS_INTEGER (BINARY_INTEGER): Liefert einen Integer-Datentypen für PL/SQL, unterstützt aber auch SQL NULL-Handling. Für die Arbeit mit ganzzahligen Werten in PL/SQL ist PLS_INTEGER typischerweise schneller als NUMBER.
  • SIMPLE_INTEGER: Ist ein Subtyp von PLS_INTEGER, kann aber keine NULL-Werte annehmen und hat auch ein anderes Overflow-Verhalten. Passt sehr gut zu nativ kompiliertem Code.
  • BINARY_FLOAT und BINARY_DOUBLE: Diese funktionieren analog zu den float und double-Datentypen in C oder Java. Auch diese Datentypen passen sehr gut zu nativ kompiliertem Code, da moderne CPUs große Teile der Fließkommaarithmetik übernehmen können. Allerdings, im Gegensatz zu NUMBER, können durch die Fließkommaarithmetik Rundungsfehler auftreten (erzeugt euch ein Variable vom Typ BINARY_FLOAT und addiert zehn Mal 0,1). NUMBER ist und bleibt in der Oracle-Datenbank der numerische Datentyp mit der besten Präzision.
Zum Abschluß vielleicht noch ein paar kurze Worte zur konkreten Speicherung des Native Code - eingangs sagte ich ja schon, dass der Code als Teil der Datenbank gespeichert wird. Wer nun ganz neugierig ist, kann sich mal mal die Tabelle SYS.NCOMP_DLL$ ansehen; darin befinden sich die BLOBs mit dem Native Code in der Spalte DLL. Wirklich anfangen kann man damit aber nix ...
Zusammengefasst kann man sagen, dass Native Compilation ein nützliches Werkzeug, aber keine Allzweckwaffe ist. Sie eignet sich gut für rechenintensive Logik, und weniger für Prozeduren, die vor allem mit Datenbank-Cursors arbeiten. Denkt aber (nicht nur bei Native Compilation) über die verwendeten Datentypen nach - wenn Ihr in einer PL/SQL-Prozedur nur mit ganzen Zahlen arbeitet, sind BINARY_INTEGER, PLS_INTEGER oder SIMPLE_INTEGER einfach sinnvoller als der NUMBER. Viel Spaß beim Ausprobieren ...
This blog posting will be about PL/SQL Native Compilation, which is available in the Oracle database for quite a while - the first implementation was in Oracle9i. By default, PL/SQL code is being compiled to "bytecode" which is then being executed by the "PL/SQL engine". This execution is still an interpreted execution, but interpreting compact bytecode is (of course) much more efficient than interpreting source code. The source code itself is also being stored in the database, as we all can see by selecting the data dictionary view USER_SOURCE.
In the early days, using native compilation for PL/SQL was cumbersome: The concept was to generate C code from the PL/SQL source and then to use an external compiler and linker to generate native code for the platform the database runs on. But this approach - in practice - led to problems.
  • C compilers and linkers are freely available on Linux, but not necessarily on other platforms. To use native compilation, some installations would have to purchase compiler licenses
  • Setup for compiler and linker had to be done by using Makefiles. The database then called the make utility in order to generate the native code. Editing Makefiles can be hard
  • The resulting native code exists in an external file (.so on Unix/Linux platforms, .dll on Windows). This has consequences for database backup and recovery in particular and the operating processes in general
So, beginning with release 11.1, native compilation was simplified dramatically. The most important difference between today and the past is that the need for an external compiler and linker had been eliminated: the database generates the platform-specific native code itself. Therefore all compiler-related parts like Makefiles and directories had also been dropped. Today, we simply have one session parameter: PLSQL_CODE_TYPE. I'll now use a fibonacci number example (the usual suspect) to show the simplicity and the effects of enabling native compilation.
create or replace function fib (n number) return number is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err

Function created.

select fib(32) from dual
/

   FIB(32)
----------
   2178309

1 row selected.

Elapsed: 00:00:01.88
By default, PL/SQL code will execute in interpreted mode. So the elapsed time we have seen, should reflect the performance of interpreted PL/SQL. We can also check this using the data dictionary view USER_PLSQL_OBJECT_SETTINGS.
SQL> select name, type, PLSQL_CODE_TYPE from user_plsql_object_settings where name='FIB'

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     INTERPRETED

1 row selected.
Now we'll change this to native execution. Change the parameter and recompile the PL/SQL code.
SQL> alter session set PLSQL_CODE_TYPE=NATIVE;

Session altered.

SQL> alter function FIB compile;

Session altered.

SQL> select name, type, plsql_code_type from user_plsql_object_settings where name='FIB';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
FIB                            FUNCTION     NATIVE

1 row selected.
We now should see a change in elapsed time - remember: in interpreted mode, elapsed time was 1.88 seconds.
select fib(32) from dual;

   FIB(32)
----------
   2178309

1 row selected.
 
Elapsed: 00:00:00.98
This is twice as fast - which is good (but we'll make it even faster). But before moving on, I'd like to say that such an improvement will not happen all the time. If you have PL/SQL code which does computations and nothing else, you'll see the above or similar improvement. If, on the other hand, your PL/SQL is about I/O - and I/O can mean working with database objects (SQL), with the network (UTL_TCP, UTL_HTTP, UTL_SMPT) or with the file system (UTL_FILE), then you might see only a small or no benefit at all. I also have an example for this - the following procedure loops over the rows of the ALL_OBJECTS dictionary view and calculates the sum off all "object name lengths".
create or replace function get_len_allobjects return number is
  l_len number := 0;
begin
  for i in (select object_name from all_objects) loop
    l_len := l_len + length(i.object_name);
  end loop;
  return l_len;
end;
/ 
sho err
The major part of that procedure is the cursor loop for ALL_OBJECTS. The computation part (which could benefit from native compilation) is close to nothing. So - try it out. First execution is interpreted.
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 row selected.

Elapsed: 00:00:11.95
The second run will be native.
SQL> alter session set plsql_code_type=native;

SQL> alter function get_len_allobjects compile;

SQL> select get_len_allobjects from dual;

GET_LEN_ALLOBJECTS
------------------
           1739009

1 row selected.

Elapsed: 00:00:11.42
You don't see a big change - the code part which profited from native compilation was so small that it couln't make a difference. These kind of PL/SQL functions cannot benefit from native compilation.
But let's now get back to the Fibonacci example - we wanted to make this even faster - and to do this, we'll have to look into the data types which are being used by the function. In the example it's the NUMBER data type, which offers the greatest number range and precision, but the question is whether we really need this. And since the function only deals with integer numbers, we have alternatives: PLS_INTEGER (BINARY_INTEGER is a synonym) and SIMPLE_INTEGER. Both types are available in PL/SQL only, so we cannot use them in tables. But when we execute a function with PLS_INTEGER arguments, Oracle automatically converts between NUMBER and the PL/SQL data type.
SIMPLE_INTEGER is interesting. It's available since Oracle 11.2. Compared to PLS_INTEGER, it has different overflow semantics and it does not know about SQL NULL. So it's pretty comparable to the int data type in the C/C++ or Java programming languages. For native compilation it's particularly interesting since it allows to push down more logic to the CPU. Here's a statement on SIMPLE_INTEGER from the documentation.
If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.
So - we'll try this and change the NUMBER data types in the function signature to SIMPLE_INTEGER.
create or replace function fib (n simple_integer) return simple_integer is
begin
  if (n = 1) or (n = 2) then
     return 1;
  else
     return fib(n - 1) + fib(n - 2);
  end if;
end fib;
/
sho err
Remember again. Using the NUMBER data type, the function ran 1.88 seconds in interpreted mode and 0.98 seconds in native mode. Here are the numbers for the SIMPLE_INTEGER type.
SQL> alter session set plsql_code_type=interpreted;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 row selected.

Elapsed: 00:00:01.43
It runs faster, even in interpreted mode. But let's also have a look to native mode.
SQL> alter session set plsql_code_type=native;

SQL> alter function fib compile;

SQL> select fib(32) from dual;

   FIB(32)
----------
   2178309

1 row selected.

Elapsed: 00:00:00.53
This is close to twice as fast - compared to native mode with the NUMBER type. So choosing a different data type can make a significant difference. It tried it also with the other numeric data types - the following table shows the results.
A short summary on numeric data types in PL/SQL.
  • PLS_INTEGER (BINARY_INTEGER) is a data type for integer numbers in PL/SQL only. It does support SQL NULL and is typically faster than NUMBER
  • SIMPLE_INTEGER is a subtype of PLS_INTEGER, but it has a different overflow semantics and it does not support SQL NULL. It's comparable to an integer data type outside a database and suits very well to native compiled code
  • BINARY_FLOAT and BINARY_DOUBLE are data types for floating point numbers. These work similar to the float and double types in programming languages outside the database. Since floating point logic can be pushed down to the CPU, these suit also very well to native code. But (and this is important) opposed to NUMBER, we can see rounding differences - simply declare a BINARY_FLOAT variable and add 0.1 ten times. You'll then see the difference between NUMBER and BINARY_FLOAT. NUMBER is always to most precise numeric data type in Oracle
Finally, a few words on where native code is actually being stored in the database. If you are curious, have a look into the SYS.NCOMP_DLL$ table - it has the BLOB column DLL which contains the actual native code - but ... can you really do something with this ...?
In summary, PL/SQL Native Compilation can be a very handy tool - but it's not the silver bullet performance tuning method. If you have PL/SQL objects doing intensive computations, compiling these to native code can improve performance. Taking care on the used data types can lead to even better results. But if your code is about Cursors, SQL and database tables, native compilation might have close to no effect at all ... have fun while playing around.

9. Juli 2015

Oracle12c Jetzt. Webinare im Sommer und Herbst 2015

This posting is about a webinar series in german language and therefore in german only.
Ab August führen meine Kollegen (unter anderen Ulrike Schwinn und Sebastian Solbach) und ich eine Webinar-Reihe zum Datenbankrelease Oracle12c durch. Inhalte sind das konkrete Upgrade einer älteren Datenbank auf 12c, aber auch Testen, Hochverfügbarkeit, mögliche Datenbankarchitekturen und mehr. Dass Entwicklerthemen wie SQL, PL/SQL, XML, JSON und andere nicht zu kurz kommen, ist dabei meine Aufgabe ☺. Die Reihe nennt sich 12c jetzt - die Teilnahme ist kostenlos. Meldet euch am besten an - wir können euch dann benachrichtigen, kurz bevor ein Webinar startet oder wenn es Änderungen am Programm gibt.


Zusätzlich wird es noch eine LIVE Veranstaltung im Oktober in Köln geben - die Details werden gerade ausgearbeitet - lasst euch überraschen und meldet euch am besten gleich zu 12cJetzt an.

Beliebte Postings