The PL/SQL compiler converts a piece of PL/SQL code that is written in user-readable language semantics to system code. At a higher level, the code compilation is a three-step process that includes code parsing, parameter binding, and translation into machine code (M-code). The compiler raises a syntax or compilation error if a PL/SQL construct or statement is incorrectly used. Once the PL/SQL program code is error-free and argument binding is done, the system code is generated and stored in the database.
Until Oracle 10g Release 1, the M-code was generated without any code optimization. Starting from Oracle Database10g Release 2, M-code uses the PL/SQL optimizer to apply code formatting for better performance. The PL/SQL optimizer level is governed by a compilation parameter known as PLSQL_OPTIMIZE_LEVEL. The parameter value can be set at the system or session level as a number between 1 to 3. By default, the parameter value is 2.
The PL/SQL optimizer uses multiple techniques...