Oracle11g PL/SQL Compiler Options

PL/SQL has traditionally been run-time interpreted. The compiler makes a p-code and this code is stored in the database along with the PL/SQL source code.



Oracle11g has expanded the internal PL/SQL optimizing compiler introduced in Oracle10g.


Oracle10g implemented an optimizing compiler. This compiler tries to eliminate unaccessed code from being compiled, looks for redundancies, and more.

· Level 0 – no optimization (Oracle9 compatible)
· Level 1 – eliminates unaccessed code from compiler output
· Level 2 – restructures code, aggressive optimization


Oracle11g has enhanced the optimizing compiler, adding a level 3 that streamlines inline code and calls to inline code.




The compiler can be invoked via the commands listed above.

Objects and compiler settings can be viewed using the USER_PLSQL_OBJECTS_SETTINGS (for current objects) and V$PARAMETER (to see the PL/SQL compiler settings at the database level). ALTER SESSION can change any of these settings for the duration of a connected session (ie: the session used to create and/or compile code).



The PL/SQL compiler can give useful warnings. These warnings can provide a variety of information such as performance tips to dead code to other coding issues. Follow the instructions above to implement and use.




Oracle does not allow for functions called from SELECT or DML statements to change the table that is referenced in the SELECT or DML. These functions also cannot issue a commit or an implied commit (such is the kind from a DDL statement).





Oracle can insure specific application behavior at the package level using these settings. These settings allow for specific behavior to be enforced when calling these routines either directly or indirectly from SQL statements.


Summary

Oracle continues to enhance the PL/SQL compiler. It is important to understand what the options are and how they can be used to enhance your applications. This section is an excerpt from my Advanced PL/SQL Tips and Techniques course.