Native Compilation of a PLSQL Object – Keep Learning Keep Living

Hi,

you’ve probably heard about “NATIVE” compilation about plsql objects. I wanted to demonstrate how it is work and whether it really does matter or not.

By default Oracle compiles every plsql object as “INTERPRETED” which means, your plsql code transform into a intermediate form which interpreted every time when you run it. This part is important; you compile your code and whenever you run your procedure/function, that code is interpreted by Oracle and executed. This is a huge performance gap because Oracle creates another layer to run your code between your code and cpu/os etc. A NATIVE compiled code, on the other hand, is transformed into a native code which can be run by cpu directly. So a Native compiled plsql object will run much more faster than Interpreted one.

By saying “faster” I must also add “what” will be faster. for example an SQL statement (select, dml etc) will not affected by compilation type because those commands can not be run by cpu directly. They are Oracle Database commands and they must be run by Oracle processes so if your plsql object (procedure/function etc) just contains sql statements then compiling it as NATIVE probably won’t make any difference. Only direct cpu operations will be affected like multiplying a value and assigning it to another variable or loops etc. Basically we can say plsql specific commands will run faster when they compiled as NATIVE.

this compilations is bind to a parameter: PLSQL_CODE_TYPE. By default it’s value is INTERPRETED and you can change it to NATIVE. that way every object that you will create after that will be compiled as NATIVE but not the ones created until now. you can query your objects compilation type on DBA_PLSQL_OBJECT_SETTINGS (instead of DBA you can use, ALL or USER of course). if you don’t want to make NATIVE as your default compilation method then you can compile just one object as you wish:

alter procedure Sp_X compile plsql_object_type=NATIVE;

or 

alter procedure Sp_X compile plsql_object_type=INTERPRETED;

alter procedure Sp_X compile plsql_object_type=NATIVE;

alter procedure Sp_X compile plsql_object_type=INTERPRETED;

this allows you to compile your previous objects with different compilation method than PLSQL_CODE_TYPE parameter. also you can set PLSQL_CODE_TYPE parameter just for your session and the objects that you will create or compile on that session will be compiled as accordingly.

So Let’s make an example and see the difference:

show parameter PLSQL_CODE_TYPE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED

create or replace procedure sp_x(p number, px out number) as 
  x number;
  y number;
  
  function ip_x (t number) return number as
  begin
    if mod(t,2) = 0 then
      return t*2;
    else
      return t+2;
    end if;
  end;
  
begin
  for i in 1..p*100 loop
    x := x + ip_x(x);
    
    for k in 1..1000 loop
      y := ip_x(k+x);
      if y > 10000 then
        y := 0;
      end if;
    end loop;
    
    x := x+ y;
  end loop;
  px := x;
end;
/

show parameter PLSQL_CODE_TYPE

NAME TYPE VALUE

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

plsql_code_type string INTERPRETED

create or replace procedure sp_x(p number, px out number) as

x number;

y number;

function ip_x (t number) return number as

begin

if mod(t,2) = 0 then

return t*2;

else

return t+2;

end if;

end;

begin

for i in 1..p*100 loop

x := x + ip_x(x);

for k in 1..1000 loop

y := ip_x(k+x);

if y > 10000 then

y := 0;

end if;

end loop;

x := x+ y;

end loop;

px := x;

end;

my PLSQL_CODE_TYPE parameter is in it’s default value and I created a procedure called SP_X. That procedure just run some plsql code like loops, if statements and assignments. I just want to remind that oracle optimize plsql codes while compiling them so I just make it a little bit harder to optimize (simply I could just use PLSQL_OPTIMIZE_LEVEL but just leaving everything by default).

When I checked my procedure I confirmed that it is compiled INTERPRETED:

column name format A30
column PLSQL_CODE_TYPE format A30

select name, plsql_code_type from DBA_PLSQL_OBJECT_SETTINGS where name ='SP_X';

NAME                           PLSQL_CODE_TYPE
------------------------------ ------------------------------
SP_X                           INTERPRETED

column name format A30

column PLSQL_CODE_TYPE format A30

select name, plsql_code_type from DBA_PLSQL_OBJECT_SETTINGS where name ='SP_X';

NAME PLSQL_CODE_TYPE

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

SP_X INTERPRETED

So let’s run and see timings. To make sure I will run it 3 times:

sqlplus mustafa/mustafa

set timing on
variable y number;

------------------------------------------------------
exec sp_X(400, :y);
PL/SQL procedure successfully completed.

Elapsed: 00:00:11.47
------------------------------------------------------
exec sp_X(400, :y);
PL/SQL procedure successfully completed.

Elapsed: 00:00:12.42
------------------------------------------------------
exec sp_X(400, :y);
PL/SQL procedure successfully completed.

Elapsed: 00:00:11.11

sqlplus mustafa/mustafa

set timing on

variable y number;

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

exec sp_X(400, :y);

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.47

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

exec sp_X(400, :y);

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.42

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

exec sp_X(400, :y);

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.11

so it is around 11 or 12 seconds to run on my laptop. Results will be different on different machines of course.

Now compile procedure as NATIVE and re run again:

alter procedure sp_x compile plsql_code_type=native;
Procedure altered.

------------------------------------------------
exec sp_X(400, :y);
PL/SQL procedure successfully completed.

Elapsed: 00:00:06.99
------------------------------------------------
exec sp_X(400, :y);
PL/SQL procedure successfully completed.

Elapsed: 00:00:06.76
------------------------------------------------
exec sp_X(400, :y);
PL/SQL procedure successfully completed.

Elapsed: 00:00:07.25
------------------------------------------------

alter procedure sp_x compile plsql_code_type=native;

Procedure altered.

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

exec sp_X(400, :y);

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.99

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

exec sp_X(400, :y);

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.76

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

exec sp_X(400, :y);

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.25

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

it is almost two times faster than INTERPRETED compilation! that is amazing just a simple change can increase your plsql object’s speed.

So the question is why this is not default and why does even INTERPRETED exist? Basic answer is “platform independency” . When you compile your code NATIVE it is depended on your Cpu, OS etc etc. that means if you change those components your code might not work! You must recompile those objects. if you compile your code INTERPRETED, that code will run as long as Oracle runs on a platform. This makes it portable.

You can also compile Oracle database pre build objects as NATIVE (objects in SYS schema for example) this will improve some performance on the core operations of database. of course you need dba privileges to do that like shutdown database and open it in upgrade mode then you can call oracle_home/rdbms/admin/dbmsupgnv.sql file. of course you should set PLSQL_CODE_TYPE as NATIVE on database first. whole steps can be found in “database plsql reference” to compile entire objects in database as NATIVE.

if you have plsql objects which runs very frequently and contains mostly plsql instead of sql then you can compile them as NATIVE.

I wish you healthy days.

Leave a Reply Cancel reply