Oracle Illustrated

Feb 21 2010   7:49AM GMT

Migrating from 9i to 11g – Native Compilation

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

As we already know – Oracle has two methods of compilation Interpreted / Native. In Interpreted mode Oracle transforms all the PL/SQL code to C code. From 11g Oracle directly translates PL/SQL source code to DLL for the server. Linking and loading is done by itself by bypassing the file system.
Prior to Oracle 11g, to compile a code as Native lot of parameters needs to be changed. But, from Oracle 11g we can either compile all the programs or specific programs using Native compilation just by changing only one parameter – ‘PLSQL_CODE_TYPE’.

I feel Native compilation in Oracle 9i used to be a bit complicated – Lets take a quick look

Oracle 9i -

Prior to Oracle 11g – following parameters should be changed

ALTER SYSTEM SET plsql_native_make_utility = ‘make';
ALTER SYSTEM SET plsql_native_make_file_name = ‘ ;
ALTER SYSTEM SET plsql_native_library_dir=”;
ALTER SESSION SET plsql_compiler_flags = ‘NATIVE';

To set it to interpreted

ALTER SESSION SET plsql_compiler_flags = ‘INTERPRETED';

From Oracle 11g its way improved and simple to compile it native.

Oracle 11g

Interpreted vs Native – program level

CREATE OR REPLACE PROCEDURE interpreted_test AS
v_value NUMBER;
BEGIN
for i in 1 .. 500000 loop
v_value := i + i;
end loop;
END;
/

exec interpreted_test

CREATE OR REPLACE PROCEDURE native_test AS
v_value NUMBER;
BEGIN
for i in 1 .. 500000 loop
v_value := i + i;
end loop;
END;
/

alter procedure native_test compile plsql_code_type=native

exec native_test

SQL> select plsql_code_type from all_plsql_object_settings where name =’INTERPRE
TED_TEST';

PLSQL_CODE_TYPE
——————————————————————————–

INTERPRETED

Elapsed: 00:00:00.09
SQL> ed
Wrote file afiedt.buf

1* select plsql_code_type from all_plsql_object_settings where name =’NATIVE_T
EST’
SQL> /

PLSQL_CODE_TYPE
——————————————————————————–
NATIVE

Native – for all programs

NAME TYPE VALUE
———————————— ———– —————–
plsql_code_type string INTERPRETED

ALTER SESSION set plsql_code_type = ‘NATIVE’

NAME TYPE VALUE
———————————— ———– —————
plsql_code_type string NATIVE

This will compile all the code to native by default.

To recompile all the code to NATIVE the database should be shutdown, after that parameter should be altered to NATIVE at system level, and also the PLSQL_OPTIMIZER_LVEL should be set to either 2 or 3; startup the database.

From Oracle 11g if PLSQL_CODE_TYPE is set to INTERPRETED then, PLSQL code will be compiled to byte code format and the PLSQL interpreted engine will execute them.

If the parameter is set to NATIVE then, PLSQL source code will be compiled to machine code and executed natively without any interpreter involvement – so execution would be faster.

Default value is INTERPRETED – so all the code would be compiled as byte code. Note that we can also change the PLSQL_CODE_TYPE value to NATIVE or INTERPRETED dynamically using the ALTER SESSION command inside the application.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: