Table of ContentsOracle Pragma
Inlining is an effective feature to gain performance benefits in a procedural and structural code. During compilation, oracle tends to replace the inline subprogram call with its definition itself.
The INLINE pragma is a latest induction in the family. It is used to manually inlining a subprogram when the optimizer level is set as 2. It has to be specified once, just before the subprogram call. The inlining effect would remain persistent for all the subsequent calls to the subprogram; unless and until the NO pragma is specified for the subprogram.
For quick reference, PLSQL_OPTIMIZE_LEVEL parameter specifies the optimization level of the subprogram. If it is 0, then the optimizer follows NO inlining principle.
For the value of 1, the optimizer follows intermediate inlining like working with iterative constants.
For optimizer level 2, the optimizer takes the path of Intelligent inlining i.e. it would inline the subprogram by virtue of its intelligence. This is the appropriate and default level for the optimizer and provides scalability to manually inline the subprograms using PRAGMA INLINE.
At optimizer level 3, optimizer is forced to inline all possible subprograms at high priority, which is not commendable from developers perspective.
For demonstration purpose, the session has been altered to set PLSQL_OPTIMIZE_LEVEL at 2.
Now, for example, I have to calculate sum of factorial series in the format as 1! + 2! + 3! + +N! I create a procedure P_CALC_SERIES and define a local function FACTORIAL to get the factorial of a number. I would inline the local function call using PRAGMA INLINE as shown below.
Oracle 11g Tutorial