IT Innovation

More New PL/SQL Features

Improve performance in Oracle Database 11g with new PL/SQL features.

By Sushma Jagannath

May/June 2009

Oracle Database 11g introduced several new PL/SQL features and tools that help you improve application performance. Continuing the discussion of new PL/SQL features from the previous issue, this column focuses on more new features and also presents sample questions of the type you may encounter when taking the Oracle Database 11g: Advanced PL/SQL exam. Successful completion of this exam enables you to earn the Oracle Advanced PL/SQL Developer Certified Professional certificate.

Automatic Subprogram Inlining

Automatic subprogram inlining is an optimization process that replaces procedure calls with a copy of the body of the procedure. As a result, automatic subprogram inlining can reduce the overhead associated with calling subprograms while leaving your original source code in its modular state.

In Oracle Database 11g, the PL/SQL compiler can automatically identify the subprograms that should be inlined and then do the inlining. This process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. Setting PLSQL_OPTIMIZE_LEVEL to 2 means that you must specify each subprogram to be inlined with the INLINE pragma, and setting PLSQL_OPTIMIZE_LEVEL to 3 means that automatic inlining is attempted beyond those subprograms that you specify.

Within a PL/SQL subroutine, the PRAGMA INLINE value can be set in the following ways:

  • NO indicates that no inlining occurs, regardless of the PLSQL_OPTIMIZE_LEVEL setting and the YES pragmas.
  • YES indicates that automatic inlining is attempted at PLSQL_OPTIMIZE_LEVEL level 2 for a particular call, and it increases the priority of inlining at level 3 for a call.

Which statements are true about the inlining of PL/SQL subprograms?

A. The need to create and initialize the stack frame for the called procedure is eliminated.
B. The copied procedure almost always runs faster than the original call.
C. The optimization can be applied over the combined text of the call context and the copied procedure body.
D. The PL/SQL compiler can automatically find calls that should be inlined and can do the inlining without the user’s having to change any parameters.

The correct answers are A, B, and C. Because the subprogram is a part of the main program, no stack frame initialization is required, and the subprogram executes faster than it would if it were an individual procedure. With subprogram inlining, optimization can be applied on the complete program, including the subprogram. Answer D is incorrect because the process of subprogram inlining is controlled by PLSQL_OPTIMIZE_LEVEL parameter values and the INLINE pragma.

Examine the statements in Listing 1, and identify which one is true.

Code Listing 1: Which statements will be inlined?

PROCEDURE test_inlining (x PLS_INTEGER) IS ...
PRAGMA INLINE (test_inlining, 'YES'); -- call 1
PRAGMA INLINE (test_inlining, 'NO'); -- call 2
PRAGMA INLINE (test_inlining, 'YES';); -- call 3
x:= test_inlining(1) + test_inlining(2) + 17;  

A. test_inlining(1) will be inlined, but test_inlining(2) will not be inlined.
B. Both test_inlining(1) and test_inlining(2) will be inlined; ‘YES’ overrides ‘NO’ for PRAGMA INLINE.
C. Neither test_inlining(1) nor test_inlining(2) will be inlined; ‘NO’ overrides ‘YES’ for PRAGMA INLINE.
D. test_inlining(1) will not be inlined, but test_inlining(2) will be inlined.

The correct answer is C. One PRAGMA INLINE ( identifier , ‘NO’) overrides any number of occurrences of PRAGMA INLINE ( identifier , ‘YES’), and the order of the pragmas is not important.

Native Compilation Method

In earlier versions of PL/SQL, native compilation required developers to translate PL/SQL code to C code. The next step was to compile or translate the C code to native code. This required a C compiler to be present on both the development and production servers. It also required developers to define a PLSQL_NATIVE_LIBRARY_DIR parameter to indicate where the intermediate OS files were created.

Native compilation in Oracle Database 11g does not require a C compiler; the Oracle executable is programmed to transform the machine code precursor directly to the required platform-specific machine code. The result is that PL/SQL programs using native compilation in Oracle Database 11g run much faster than PL/SQL programs using native compilation in Oracle Database 10g.

You can set the PL/SQL native compilation method in Oracle Database 11g by using the following parameters:

  • PLSQL_CODE_TYPE = { INTERPRETED | NATIVE } specifies the compilation mode for the PL/SQL library units.
  • PLSQL_OPTIMIZE_LEVEL = { 0 | 1 | 2 | 3} specifies the optimization level to be used for compiling the PL/SQL library units.

You can enable PL/SQL native compilation by using the following statement:


You changed the values of the following parameters for a session:


Which statements are true?

A. The compiler automatically inlines subprograms.
B. All PL/SQL library units, including anonymous PL/SQL blocks, are compiled into machine code.
C. Changing the value of the PLSQL_CODE_TYPE parameter has no effect on the PL/SQL library units that have already been compiled.
D. All subsequent automatic recompilations of PL/SQL library units use native compilation.

The correct answers are A, C, and D. Setting the value of PLSQL_OPTIMIZE_LEVEL to 3 directs the PL/SQL compiler to automatically inline subprograms. Changing the value of PLSQL_CODE_TYPE to NATIVE will not change the PL/SQL code that has already been compiled, but subsequent recompilations of PL/SQL code will use the native compilation method. Answer B is incorrect because anonymous PL/SQL blocks cannot be compiled into native code.


This column focused on some of the PL/SQL enhancements introduced in Oracle Database 11g that improve the performance of PL/SQL applications:

Automatic subprogram inlining replaces a subprogram call with a copy of the called subprogram to improve program performance. PL/SQL native compilation improves the performance of PL/SQL statements because the native code does not have to be interpreted at runtime and therefore runs faster.

Next Steps

 LEARN more about the Oracle Certification Program

 EXPLORE the certification forum

 READ Inside OCP columns

 READ more about PLSQL
Oracle Database PL/SQL Language Reference


Photography bySérgio Rola,Unsplash