While reading a PLSQL book I have stumbled upon this piece of code :
ALTER PROCEDURE pls_test COMPILE PLSQL_CODE_TYPE=INTERPRETED;
Could you please explain how it differs from normal compilation? I was not able to find the answer in the book.
Thank you!
Below description summarizes Native vs Interpreted Compilation. Interpreted Compilation is default compilation on Oracle DB. Natively compiled code does not need to be interpreted at runtime thus it executes faster.
Native compilation is the process by which a stored PL/SQL program is compiled into native code (Pro *C) that does not need to be interpreted at runtime. In comparison, code that is not natively compiled is stored in an intermediate form that must be interpreted at runtime.
PL/SQL native compilation provides the greatest performance gains for computation-intensive procedural operations.
Oracle supports two different compilation modes : INTERPRETED and NATIVE.
INTERPRETED is the default mode, that was introduced at the same time as stored PL/SQL (Oracle 7) :
... each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time
In the code you showed, the INTERPRETED part is redundant, as it corresponds to the default compilation mode (unless the command is executed against a server whose default was changed).
NATIVE was introduced in Oracle 9, then enhanced and simplified in the following versions :
SQL modules (packages, triggers, procedures, function, and types) .. are compiled... into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.
This results in faster execution times, as no interpretation is needed at runtime : improvement factor was forecasted from 1.04 to 2.4 in Oracle 10g.
As a conclusion : unless you have a good reason, you want to use NATIVE over INTERPRETED...
For more details and a possible migration path, read this Oracle 10g FAQ paper and this post on Oracle blog.