PLSQL compilation methods

In your day-to-day life, you often have PLSQL objects that need to be executed with a certain frequency and require a very aggressive speed. In these cases, the first step is to optimize the code and also the instance (if necessary). However, if you still cannot achieve an adequate response time, there is still a solution. The method of compiling your code, interpreted or native, will be discussed in more detail below.

In Oracle version 9i, the ability to compile your PLSQL codes “natively” (directly in C language) was introduced, which makes it much faster than in interpreted mode. At that time, the developer had a certain dependence on the DBA, since it was necessary to define in the SPFILE of the database instance a parameter called “PLSQL_NATIVE_LIBRARY_DIR”, where a directory was pointed to to install a C language compiler. Well, that is now a thing of the past and in the most recent versions (11g / 12c) there is no longer this need, all you need to do is change another parameter called PLSQL_CODE_TYPE, which can be changed directly in the session scope and then take full advantage of this alternative to speed up the execution time of your PLSQL objects, let's see the example below:

PLSQL_CODE_TYPE = 'INTERPRETED': In this mode, the code is compiled in an intermediate area of memory and persisted in the instance data dictionary, to be interpreted at runtime (which takes longer).

PLSQL_CODE_TYPE = 'NATIVE': In this case the code will not be interpreted at runtime, as it will already be persisted in its native form, consequently the execution will be faster.

You can change the value of the PLSQL_CODE_TYPE parameter at the session scope:

ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE?

Or at the time of compiling your PLSQL object:

ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE = NATIVE;

You can check the compilation method of your PLSQL objects through the performance views USER | ALL | DBA_PLSQL_OBJECT_SETTINGS. Now let's get to the practical part of this subject.

SQL> connect hr/hr Connected.

SQL> create or replace function fib (n POSITIVE) return integer is 2 begin 3 if (n = 1) or (n = 2) then 4 return 1; 5 else 6 return fib(n - 1) + fib(n - 2); 7 end if; 8 end fib; 9 /

Function created.

SQL> ALTER SESSION SET plsql_code_type = 'INTERPRETED';

Session altered.

SQL> ALTER FUNCTION fib COMPILE;

Function altered.

SQL> DECLARE 2 X number; 3 begin 4 X := fib(40); 5 dbms_output.put_line(X); 6 end; 7 / 102334155

PL/SQL procedure successfully completed.

Elapsed: 00:01:05.34

SQL> CONNECT / AS SYSDBA Connected.

SQL> ALTER SYSTEM flush shared_pool;

System altered.

SQL> CONNECT hr/hr Connected.

SQL> ALTER SESSION SET plsql_code_type = 'NATIVE';

Session altered.

SQL> ALTER FUNCTION fib COMPILE;

Function altered.

SQL> set serveroutput on

SQL> set timing on

SQL> DECLARE 2 X number; 3 BEGIN 4 X := fib(40); 5 dbms_output.put_line(X); 6 END; 7 / 102334155

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.63 SQL>

Here's the example, now take that process of yours that takes about two hours to execute and cut that time in half! lol. I hope I helped.