Native compilation is the process by which a stored PL/SQL program is compiled into native code that does not need to be interpreted at runtime. In comparison, code that is not natively compiled is stored in an intermediate form that must be interpreted at runtime.
The ability to compile PL/SQL code natively has been around since Oracle 9i; however, the implementation has changed significantly with 11g. In 9i and 10g, the compiled program units were stored and executed externally as C programs. The compilation required an external C-compiler and system configuration by a DBA. Starting with 11g, the database can handle the native compilation on its own and an external compiler is no longer needed. The natively compiled program units are stored within the databases in the system tablespace.
Because the natively compiled code does not need to be interpreted at runtime, the expectation is that it will execute faster. However, the native compilation only applies to the procedural code in a PL/SQL program unit, not any embedded SQL statements. As a result, the performance gains to be realized really depend upon how much procedural code exists in relation to SQL code. At a bare minimum, a natively compiled program unit that is SQL heavy should execute at least as fast as its non-natively compiled counterpart. As the volume of procedural code increases, the faster the natively compiled code should be.
To begin, let’s start with four stored procedures utilizing various amounts of procedural and SQL code. The first procedure contains absolutely no SQL, whereas the rest contains increasing amounts of SQL intermixed with the PL/SQL.
The default compilation mode (native or interpreted) can be set at the system level via the PLSQL_CODE_TYPE database initialization parameter. Alternatively, the mode can be changed at the session level using one of the following commands:
The session/system needs to be altered prior to compiling a given program unit. Altering the compilation mode parameter does not impact program units that are already compiled in the database. To ascertain the compilation type from an existing program unit, query the PLSQL_CODE_TYPE column of the USER_PLSQL_OBJECT_SETTINGS dictionary table. Here are the results when the test procedures are compiled in interpreted mode:
Here are the results when the test procedures are compiled natively:
Native compilation provided increased performance across the board, particularly with the program units that contained more procedural logic in relation to SQL commands, as detailed in the table:
With nothing to lose and everything to gain, setting the database default to native compilation would seem to make sense. Any performance degradation caused by it—and I have yet to come across any—should be handled on a one-off basis, shifting individual program units back to native interpretation as needed.
Leave a Reply