Native PL/SQL compilation

Native PL/SQL compilation

Native compilation is a way of speeding up PL/SQL procedures by compiling them into native code residing shared libraries. The procedures are translated into C code and then compiled with an by Oracle certified C-compiler and linked into the Oracle process.Procedures compiled this way will work in all server environments as dedicated connections, shared connections (formerly known as MTS) and starting with 10g even within a Real Application Cluster (RAC) environment.


connect scott/tiger
CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2);
END IF;
END fib;
/

connect / as sysdba
alter system set plsql_native_library_dir='/home/oracle/native10g' scope=both;

connect scott/tiger
alter session set plsql_code_type = 'INTERPRETED';
alter function fib compile;
select object_name,param_name,param_value
from user_stored_settings
where object_name = 'FIB';
set serveroutput on
set timing on
declare
x number;
begin
x := fib(40);
dbms_output.put_line(x);
end;
/

set timing off
connect / as sysdba
alter system flush shared_pool;

connect scott/tiger
alter session set plsql_code_type = 'NATIVE';
alter function fib compile;
select object_name,param_name,param_value
from user_stored_settings
where object_name = 'FIB';
set serveroutput on
set timing on
declare
x number;
begin
x := fib(40);
dbms_output.put_line(x);
end;
/

INTERPETED
Elapsed: 00:02:05.08
NATIVE
Elapsed: 00:01:12.39



SQL> connect scott/tiger
Connected.
SQL> CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2);
END IF;
END fib;
/ 2 3 4 5 6 7 8 9

Function created.

SQL> connect / as sysdba
Connected.
SQL> alter system set plsql_native_library_dir='/home/oracle/native10g' scope=both;

System altered.

SQL> connect scott/tiger
Connected.
SQL> alter session set plsql_code_type = 'INTERPRETED';
alter function fib compile;

Session altered.

SQL>
Function altered.

SQL> set serveroutput on
set timing on
declare
x number;
begin
x := fib(40);
dbms_output.put_line(x);
end;
/SQL> SQL> 2 3 4 5 6 7

102334155

PL/SQL procedure successfully completed.

Elapsed: 00:02:05.08
SQL> SQL> set timing off
connect / as sysdba
alter system flush shared_pool;SQL> Connected.
SQL>

System altered.

SQL>

SQL> connect scott/tiger
Connected.
SQL> alter session set plsql_code_type = 'NATIVE';
alter function fib compile;
Session altered.

SQL>

Function altered.

SQL> set serveroutput on
set timing on
declare
x number;
begin
x := fib(40);
dbms_output.put_line(x);
end;
/SQL> SQL> 2 3 4 5 6 7
102334155

PL/SQL procedure successfully completed.

Elapsed: 00:01:12.39