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:
|
1 2 3 4 5 |
alter procedure Sp_X compile plsql_object_type=NATIVE; or 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.