More New PL/SQL FeaturesBy Sushma Jagannath
Improve performance in Oracle Database 11g with new PL/SQL features.
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:
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.
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.
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:
You can enable PL/SQL native compilation by using the following statement:
ALTER SYSTEM SET PLSQL_CODE_TYPE = NATIVE;
You changed the values of the following parameters for a session:
PLSQL_OPTIMIZE_LEVEL = 3 PLSQL_CODE_TYPE = NATIVE
Which statements are true?
A. The compiler automatically inlines subprograms.
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.
Sushma Jagannath (firstname.lastname@example.org) is a certification exam development manager at Oracle. She has been with the company since 2000.