6

Mladen Gogala,01/26/2013


Native PLSQL Execution

Introduction

In the world of Oracle, a great attention is given to the optimizer and rightfully so. But there are other mechanisms that can also have a profound impact on the application performance. Native PLSQL execution is nothing new, it was introduced in now largely obsolete version 10G. The idea was that Oracle internal compiler would produce an operating system shared libraries, which would then be mapped in the Oracle code itself and execute with much greater speed than the interpreted code. There were some bugs associated with that so the native execution was very rarely used. I have been consulting for numerous companies and haven's seen even a single one using the native execution. Why is that? Let's first see how was the thing done in version 10G. There were two instance parameters, namely PLSQL_CODE_TYPE and PLSQL_NATIVE_LIBRARY_DIR which were used toregulate the whole mechanism. The PLSQL_CODE_TYPE would be set to “NATIVE” on either the system or the session level and the internal compiler would produce the shared libraries in the directory defined by PLSQL_NATIVE_LIBRARY_DIR . Here are the relevant excerpts from the 10G Reference:



PLSQL_CODE_TYPE

Property

Description

Parameter type

String

Syntax

PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }

Default value

INTERPRETED

Modifiable

ALTER SESSION, ALTER SYSTEM



PLSQL_CODE_TYPE specifies the compilation mode for PL/SQL library units.

Values:

PL/SQL library units will be compiled to PL/SQL bytecode format. Such modules are executed by the PL/SQL interpreter engine.

PL/SQL library units (with the possible exception of top-level anonymous PL/SQL blocks) will be compiled to native (machine) code. Such modules will be executed natively without incurring any interpreter overhead.

When the value of this parameter is changed, it has no effect on PL/SQL library units that have already been compiled. The value of this parameter is stored persistently with each library unit.




PLSQL_NATIVE_LIBRARY_DIR

Property

Description

Parameter type

String

Syntax

PLSQL_NATIVE_LIBRARY_DIR = directory

Default value

There is no default value.

Modifiable

ALTER SYSTEM

Range of values

Any valid directory path



PLSQL_NATIVE_LIBRARY_DIR is a parameter used by the PL/SQL compiler. It specifies the name of a directory where the shared objects produced by the native compiler are stored.



In Oracle 11G, there is still the PLSQL_CODE_TYPE parameter, but the PLSQL_NATIVE_LIBRARY_DIR parameter is no more. What has happened? Where are the native libraries put now? Well, as it turns out, Oracle has invented the whole new method of using “dynamic libraries”. The reason for the quotes will become apparent later in the text. The shared libraries ended up in a table named NCOMP_DLL$, which looks like this:



COLUMN_NAME

TYPE

LEN

NULLOK

OBJ#

NUMBER

22

N

VERSION

NUMBER

22

Y

DLL

BLOB

4000

Y

DLLNAME

RAW

1024

Y



This table is the new destination for the shared objects. There is a small problem, however: operating systems usually cannot page from Oracle tables. So, how is the this done? And what objects exactly are in there? Well, it's easy to find that out:



select o.owner,o.object_name,o.object_type

from dba_objects o,ncomp_dll$ n

where o.object_id=n.obj# ;

OWNER OBJECT_NAME OBJECT_TYPE

------------------------------ -------------------- -------------------

TRCADMIN TRCA$G PACKAGE BODY

TRCADMIN TRCA$P PACKAGE BODY

TRCADMIN TRCA$T PACKAGE BODY

TRCADMIN TRCA$I PACKAGE BODY

TRCADMIN TRCA$E PACKAGE BODY

TRCADMIN TRCA$R PACKAGE BODY

TRCADMIN TRCA$X PACKAGE BODY

TRCADMIN TRCA$G PACKAGE

SYSTEM DBA_HELPER PACKAGE BODY


9 rows selected.


Elapsed: 00:00:00.13

SQL>



So, there are only packages that were intentionally compiled using native compilation and no dictionary packages or packages belonging to the Oracle products like Apex. In other words, Oracle itself does not yet use native compilation. Even more, it is possible for the compiled package to use interpreted packages. DBA_HELPER package, written by yours truly, uses DBMS_UTILITY dictionary package, but the DBMS_UTILITY package is not compiled using the native compilation, as seen from the content of the NCOMP_DLL$ table above. Oracle is known to squeeze out every last drop of performance from its software, so this begets the question: what are the effects of the native compilation? What can we expect from it? The procedures that I would expect to benefit the most are the procedures that do numeric calculations, without requiring any other services from the database, like the procedure below:



CREATE OR REPLACE

PROCEDURE test_ncomp

AS

type numtab

IS

TABLE OF NUMBER;

num_tab numtab:=numtab();

num_avg NUMBER:=0;

num_sum NUMBER:=0;

BEGIN

FOR i IN 1..50000000

LOOP

num_tab.extend;

num_tab(i):=i;

num_sum :=num_sum+i;

END LOOP;

num_avg:=num_sum/50000000;

dbms_output.put_line('SUM='||num_sum||' '||'AVG='||num_avg);

num_sum:=0;

FOR i IN 1..50000000

LOOP

num_sum:=num_sum+num_tab(i);

END LOOP;

num_avg:=num_sum/50000000;

dbms_output.put_line('SUM='||num_sum||' '||'AVG='||num_avg);

END test_ncomp;



This procedure computes the sum of numbers from 1 to 50 millions twice and computes the average. It only calls DBMS_OUTPUT to display the results. Let's see what is the difference between the compiled and interpreted versions:



SQL> alter session set plsql_code_type=interpreted;


Session altered.


Elapsed: 00:00:00.00

SQL> alter procedure test_ncomp compile;


Procedure altered.


Elapsed: 00:00:00.05

SQL> exec test_ncomp

SUM=1250000025000000 AVG=25000000.5

SUM=1250000025000000 AVG=25000000.5


PL/SQL procedure successfully completed.


Elapsed: 00:00:23.09

SQL> alter session set plsql_code_type=native;


Session altered.


Elapsed: 00:00:00.00

SQL> alter procedure test_ncomp compile;


Procedure altered.


Elapsed: 00:00:00.03

SQL> exec test_ncomp

SUM=1250000025000000 AVG=25000000.5

SUM=1250000025000000 AVG=25000000.5


PL/SQL procedure successfully completed.


Elapsed: 00:00:20.68

SQL>



The version compiled using native compilation is approximately 10% faster and this is the ideal case: no queries, no repeated calls to the other interpreted procedures. The difference is less than impressive. That is probably the reason why Oracle is not bothering with using it for data dictionary or the products like Apex. In general case, the best one can expect is 2% or 3% boost. One question still remains to be answered: how does Oracle do it? What is the exact mechanism of the native compilation and where have all the flowers and shared libraries gone? The answer to this question can be found using the well known Linux utility called “strace”, similar to “truss” on Solaris. This utility intercepts all the calls to system services and prints them on the standard output or the designated file, along with the corresponding arguments. Here is a relevant example:



[mgogala@medo ~]$ strace pwd

execve("/usr/bin/pwd", ["pwd"], [/* 69 vars */]) = 0

brk(0) = 0x2107000

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fa3f6e9c000

access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)

open("/oracle/product/11.2.0/11.2.0.3/lib/tls/x86_64/libc.so.6", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)

stat("/oracle/product/11.2.0/11.2.0.3/lib/tls/x86_64", 0x7fffa5c2f0d0) = -1 ENOENT (No such file or directory)

open("/oracle/product/11.2.0/11.2.0.3/lib/tls/libc.so.6", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)

stat("/oracle/product/11.2.0/11.2.0.3/lib/tls", 0x7fffa5c2f0d0) = -1 ENOENT (No such file or directory)

open("/oracle/product/11.2.0/11.2.0.3/lib/x86_64/libc.so.6", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)

stat("/oracle/product/11.2.0/11.2.0.3/lib/x86_64", 0x7fffa5c2f0d0) = -1 ENOENT (No such file or directory)

open("/oracle/product/11.2.0/11.2.0.3/lib/libc.so.6", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)

stat("/oracle/product/11.2.0/11.2.0.3/lib", {st_mode=S_IFDIR|0755, st_size=12288, ...}) = 0

open("/etc/ld.so.cache", O_RDONLY|O_CLOEXEC) = 3

fstat(3, {st_mode=S_IFREG|0644, st_size=154536, ...}) = 0

mmap(NULL, 154536, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7fa3f6e76000

close(3) = 0

open("/lib64/libc.so.6", O_RDONLY|O_CLOEXEC) = 3

read(3, "\177ELF\2\1\1\3\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0@\30B^3\0\0\0"..., 832) = 832

fstat(3, {st_mode=S_IFREG|0755, st_size=2065552, ...}) = 0

mmap(0x335e400000, 3892376, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x335e400000

mprotect(0x335e5ac000, 2097152, PROT_NONE) = 0

mmap(0x335e7ac000, 24576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x1ac000) = 0x335e7ac000

mmap(0x335e7b2000, 17560, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x335e7b2000

close(3) = 0

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fa3f6e75000

mmap(NULL, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fa3f6e73000

arch_prctl(ARCH_SET_FS, 0x7fa3f6e73740) = 0

mprotect(0x605000, 4096, PROT_READ) = 0

mprotect(0x335e7ac000, 16384, PROT_READ) = 0

mprotect(0x335e21f000, 4096, PROT_READ) = 0

munmap(0x7fa3f6e76000, 154536) = 0

brk(0) = 0x2107000

brk(0x2128000) = 0x2128000

brk(0) = 0x2128000

open("/usr/lib/locale/locale-archive", O_RDONLY|O_CLOEXEC) = 3

fstat(3, {st_mode=S_IFREG|0644, st_size=105038240, ...}) = 0

mmap(NULL, 105038240, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7fa3f0a46000

close(3) = 0

getcwd("/home/mgogala", 4096) = 14

fstat(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 3), ...}) = 0

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fa3f6e9b000

write(1, "/home/mgogala\n", 14/home/mgogala

) = 14

close(1) = 0

munmap(0x7fa3f6e9b000, 4096) = 0

close(2) = 0

exit_group(0) = ?

+++ exited with 0 +++

[mgogala@medo ~]$



The “pwd” stands for “print working directory” and is a classic Unix/Linux commands, one of the first that are shown in the Unix/Linux tutorials. Even tracing this simple command can tell us how the operating system deals with the shared libraries.

Internals

Before we proceed to doing “strace” on much more complex Oracle processes, let's see how does OS deal with the shared libraries from the little example above. The library in question is the C run-time library called “libc” and embodied in the file called /lib64/libc.so.6 on my system. This shared library contains the actual code for the ubiquitous C-language routines like “sprintf”:



[mgogala@medo ~]$ nm /lib64/libc.so.6|grep sprintf

000000335e450920 T _IO_sprintf

000000335e473c80 t _IO_vasprintf

000000335e46e0d0 T _IO_vsprintf

000000335e46e0d0 t _IO_vsprintf_internal

000000335e508ea0 t __GI___vasprintf_chk

000000335e506b20 t __GI___vsprintf_chk

000000335e450920 t __GI_sprintf

000000335e46e0d0 t __IO_vsprintf

000000335e4509b0 t ___asprintf

000000335e506a80 t ___sprintf_chk

000000335e506b20 t ___vsprintf_chk

000000335e4509b0 T __asprintf

000000335e508e10 T __asprintf_chk

000000335e4509b0 t __asprintf_internal

000000335e450920 t __sprintf

000000335e506a80 T __sprintf_chk

000000335e508ea0 T __vasprintf_chk

000000335e506b20 T __vsprintf_chk

000000335e4509b0 W asprintf

000000335e450920 T sprintf

000000335e473c80 W vasprintf

000000335e46e0d0 W vsprintf



The utility for reading the defined symbols from the library is called “nm”. In order to use routines like getcwd() or printf(), both used in our little “pwd” utility, the OS has to open the file, read its headers and use “mmap” call to map it into the memory. Here is the relevant part of from the strace output from the example above:



open("/lib64/libc.so.6", O_RDONLY|O_CLOEXEC) = 3

read(3, "\177ELF\2\1\1\3\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0@\30B^3\0\0\0"..., 832) = 832

fstat(3, {st_mode=S_IFREG|0755, st_size=2065552, ...}) = 0

mmap(0x335e400000, 3892376, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x335e400000



The library file is open as the file descriptor 3 and mapped into memory using the “memory map” or “mmap” system service. The last two arguments are the file descriptor number, in our case it's the number 3, and the offset in the file where the mapping begins, in our case “0”. So, now we know what to look for in the strace of Oracle processes: open file, followed by a mmap call. Here is what Oracle does:



statfs("/dev/shm/", {f_type=0x1021994, f_bsize=4096, f_blocks=2054336, f_bfree=2053350, f_bavail=2053350, f_files=2054336, f_ffree=2054312, f_fsid={0, 0}, f_namelen=255, f_frsize=4096}) = 0

futex(0x335f607340, FUTEX_WAKE_PRIVATE, 2147483647) = 0

open("/dev/shm/PESLD_O11_340800e_b8bc805101000000", O_RDONLY|O_NOFOLLOW|O_CLOEXEC) = 8

mmap(NULL, 2080, PROT_READ|PROT_EXEC, MAP_PRIVATE, 8, 0) = 0x7fc54e0c9000

close(8)



Oracle reads the BLOB column “DLL” into memory and opens a file on the /dev/shm file system, which is then mapped into memory and used as a shared library. In other words, for those a little bit more familiar with C programming on Unix systems, Oracle has invented its own version of dlopen library routine, with the library being stored in the Oracle tables.

The format of the library is not the usual Linux shared object. I unloaded the BLOB column into a regular file, using a custom made Perl script, but neither nm nor objdump utilities recognize it as a machine object file. I was unable to discern any readable header or file type information from the file.

This part of the code is missing when the procedure is compiled with PLSQL_CODE_TYPE set to “INTERPRETED”.



Conclusion

Oracle has invented its own mechanisms for native compilation. The efficiency of those mechanisms is not great, there is no need to rush into this and recompile all the code using the native compilation method. In the ideal case, it can speed things up for about 10%. Realistically, one can expect between 2% and 5% boost. The mechanism has been significantly changed since the version 10G and the fact that Oracle is not using this mechanism in their own product tells me that I shouldn't do it either. This is in early stages of development is not yet a viable option.

The mechanism is interesting, I am sure that it will develop further, but for now, it's not yet ready for the use in production systems. There is also a penalty for the native compilation. First, as you can see from the “strace” snippet above, the library is mapped int the address space of the invoking process using the MAP_PRIVATE flag. That means – no sharing. The PLSQL objects compiled using native compilation are not placed in the shared pool in the SGA, they are mapped directly into the address space of the invoking process. Besides not being shared, this also increases the address space and the memory consumption of the each invoking process. I haven't done any tests with the native compilation being used on a massive scale, but I suspect that the memory consumption resulting from this would not be insignificant.