Oracle Pragma Inline

Home

Technical Training

Oracle 11g Tutorial

Table of Contents

Oracle Pragma

Oracle Pragma RESTRICT_REFERENCES

Oracle Pragma EXCEPTION_INIT

Oracle Pragma Inline

Page 4 of 4

Author: Saurabh Gupta Published on: 6th Jul 2011

Oracle Pragma

Oracle Pragma Inline

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.

Oracle Pragma Inline Example Syntax

Sample Code

PRAGMA INLINE (subprogram name, [YES | NO]);

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.

Oracle Pragma Inline Example Code

For demonstration purpose, the session has been altered to set PLSQL_OPTIMIZE_LEVEL at 2.

Sample Code

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2
Session altered.

Ads

Oracle Pragma Inline Example Code

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.

Sample Code

CREATE OR REPLACE PROCEDURE P_CALC_SERIES(P_LIMIT NUMBER)
IS
L_SERIES NUMBER := 0;
FUNCTION FACTORIAL (P_NUM NUMBER) RETURN NUMBER IS
L_FACT NUMBER := 1;
BEGIN
FOR I IN 1..P_NUM
LOOP
L_FACT := L_FACT*I;
END LOOP;
RETURN L_FACT;
END FACTORIAL;
BEGIN
FOR J IN 1 .. P_LIMIT
LOOP
PRAGMA INLINE (FACTORIAL, 'YES');
L_SERIES := L_SERIES + FACTORIAL(I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum of the series is '||TO_CHAR(L_SERIES));
END;
/
Procedure created.

1 2 3 4

Read Next: UTL_FILE

This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial

Comments

Exforsys Connect

Exforsys e-Newsletter