PL/ SQL is often used as a thin wrapper for executing SQL statements, setting bind variables and handling result sets. See code sample-1 In such cases the execution speed of the PL/ SQL code is rarely an issue. It is the execution speed of the SQL that determines the performance. (The efficiency of the context switch between the PL/ SQL and the SQL operating environments might be an issue, but that's a different discussion. See the sections on bulk binding and table functions.)
However, we see an increasing trend to use PL/ SQL for computationally intensive database independent tasks. It is after all a fully functional 3GL. See code sample-2 Here it is the execution speed of the PL/ SQL code that determines the performance.
In pre-Oracle9i versions, compilation of PL/ SQL source code always results in a representation (usually referred to bytecode) which is stored in the database and interpreted at run-time by a virtual machine implemented within ORACLE which in turn runs natively on the given platform. Oracle9i introduces a new approach. PL/ SQL source code may optionally be compiled into native object code which is linked into ORACLE. (Note however that an anonymous PL/ SQL block is never compiled natively.)
The sample-2 program runs about 33% faster when compiled in NATIVE mode than when compiled in interpreted mode while the sample-1 program runs about 3% faster when compiled in NATIVE mode. (Each measurement was for about 12 million iterations).
While for data intensive programs native compilation may give only a marginal performance improvement, we have never seen it give performance degradation.
- One-Time DBA Setup
- How does the user choose between interpreted and native compilation modes?
- Upgrading a whole database to native
Business benefits of Native Compilation
- Increased speed and scalability
