By default, PL/SQL code is compiled and stored in the form of byte code ready for execution.
During the execution process, this byte code
is interpreted, a process which requires time and resources.
The process of native compilation converts PL/SQL stored procedures to Pro*C,
which is then compiled to native code shared libraries, resulting in performance increases for the procedural code.
The extent of the performance increase depends on the content of the PL/SQL,
with the best results shown in code containing loops, logic, mathematical operations and fewer database operations (SQL).
The setup required for native compilation depends on the version of Oracle being used.
In Oracle 9i several parameters must be set and on some platforms the associated makefile may need adjustment,
whereas Oracle 10g has made several parameters obsolete and the makefile rarely needs modification.
-- Oracle 9i setup.
ALTER SYSTEM SET plsql_native_make_utility = 'make';
ALTER SYSTEM SET plsql_native_make_file_name =
ALTER SYSTEM SET plsql_native_library_dir = '/u01/oradata/DB9I/native';
-- Oracle 10g setup.
ALTER SYSTEM SET plsql_native_library_dir = '/u01/oradata/DB10G/native'
SELECT product_id into prod_id
WHERE product_name = 'Dog';
SET log_text = 'This dog must be given special care
and attention since he is partially blind.'
WHERE product_id = (SELECT REF(prod) FROM product_obj_table
prod WHERE product_id = prod_id);