PRAGMA INLINE in ORACLE 11G

   PRAGMA INLINE:

The INLINE pragma specifies whether a subprogram invocation is to be inlined. Inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit).

Every call to a procedure or function causes a slight, but measurable, performance overhead, which is especially noticeable when the subprogram is called within a loop. Avoiding procedures and functions is not an option, as it goes against the concept of modular programming, making programs bulky and difficult to manage. Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state. This is done by replacing the subprogram calls with a copy of the code in the subprogram at compile time.

The process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. When PLSQL_OPTIMIZE_LEVEL=2 (the default), the INLINE pragma determines whether the following statement or declaration should be inlined or not. When PLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case the INLINE pragma can turn it off inlining for a statement, or increase the likelihood that the optimizer will choose to inline a statement. The relationship is easier to understand when you see the following example.

With PLSQL_OPTIMIZE_LEVEL=2, you must specify each subprogram to be inlined with the INLINE pragma:

PRAGMA INLINE (subprogram, 'YES')

If subprogram is overloaded, then the preceding pragma applies to every subprogram with that name.

With PLSQL_OPTIMIZE_LEVEL=3, the PL/SQL compiler seeks opportunities to inline subprograms. You need not specify subprograms to be inlined. However, you can use the INLINE pragma (with the preceding syntax) to give a subprogram a high priority for inlining, and then the compiler inlines it unless other considerations or limits make the inlining undesirable.

These tests use an anonymous block with a function defined in the declaration block. The function is then called repeatedly in a loop. The settings for PLSQL_OPTIMIZE_LEVEL and the INLINE pragma are altered to switch subprogram inlining on and off. First, we make sure PLSQL_OPTIMIZE_LEVEL=2 and run the code with no INLINE pragma set. With these settings we would not expect to see subprogram inlining taking place.

PLSQL_OPTIMIZE_LEVEL:

• Level 0: no compiler optimizations (PL/SQL compiled as is);
• Level 1: high-level optimizations (such as moving constants out of loops);
• Level 2: default level. Aggressive optimizations (such as rewriting cursor-for-loops as array fetches) and in 11g, also inlining any subprograms that we request with PRAGMA INLINE;
• Level 3: most aggressive level: New in 11g, these will inline all subprograms where possible (excluding those contained in built-in packages).

Example-1:  with PLSQL_OPTIMIZE_LEVEL=2 & with out using Pragma inline

With plsql_optimize_level=2 and without using Pragma inline the following code taken 446 hsecs in ORACLE 11G R2

sql_11g> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

Session altered.

sql_11g> SET SERVEROUTPUT ON

sql_11g> DECLARE

  2    l_loops  NUMBER := 10000000;

  3    l_start  NUMBER;

  4    l_return NUMBER;

  5 

  6    FUNCTION add_numbers (p_1 IN NUMBER,

  7                          p_2 IN NUMBER)

  8      RETURN NUMBER AS

  9    BEGIN

 10      RETURN p_1 + p_2;

 11    END add_numbers;

 12 

 13  BEGIN

 14    l_start := DBMS_UTILITY.get_time;

 15 

 16    FOR i IN 1 .. l_loops LOOP

 17      --PRAGMA INLINE (add_numbers, 'YES');

 18      l_return := add_numbers(1, i);

 19    END LOOP;

 20 

 21    DBMS_OUTPUT.put_line('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

 22  END;

 23  /

Elapsed Time: 446 hsecs

PL/SQL procedure successfully completed.

Example-2:  with PLSQL_OPTIMIZE_LEVEL=2 & with Pragma inline

sql_11g> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

Session altered.

sql_11g>

sql_11g> SET SERVEROUTPUT ON

sql_11g> DECLARE

  2    l_loops  NUMBER := 10000000;

  3    l_start  NUMBER;

  4    l_return NUMBER;

  5 

  6    FUNCTION add_numbers (p_1 IN NUMBER,

  7                          p_2 IN NUMBER)

  8      RETURN NUMBER AS

  9    BEGIN

 10      RETURN p_1 + p_2;

 11    END add_numbers;

 12 

 13  BEGIN

 14    l_start := DBMS_UTILITY.get_time;

 16    FOR i IN 1 .. l_loops LOOP

 17      PRAGMA INLINE (add_numbers, 'YES');

 18      l_return := add_numbers(1, i);

 19    END LOOP;

 20 

 21    DBMS_OUTPUT.put_line('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

 22  END;

 23  /

Elapsed Time: 167 hsecs

PL/SQL procedure successfully completed.

From the above it is proven that use of Pragma inline reduces the time required for execution.

Example-3:  with PLSQL_OPTIMIZE_LEVEL=3 & without Pragma inline

sql_11g> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;

Session altered.

sql_11g>

sql_11g> SET SERVEROUTPUT ON

sql_11g> DECLARE

  2    l_loops  NUMBER := 10000000;

  3    l_start  NUMBER;

  4    l_return NUMBER;

  5

  6    FUNCTION add_numbers (p_1 IN NUMBER,

  7                          p_2 IN NUMBER)

  8      RETURN NUMBER AS

  9    BEGIN

 10      RETURN p_1 + p_2;

 11    END add_numbers;

 12 

 13  BEGIN

 14    l_start := DBMS_UTILITY.get_time;

 15 

 16    FOR i IN 1 .. l_loops LOOP

 17      --PRAGMA INLINE (add_numbers, 'YES');

 18      l_return := add_numbers(1, i);

 19    END LOOP;

 20 

 21    DBMS_OUTPUT.put_line('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

 22  END;

 23  /

Elapsed Time: 162 hsecs

PL/SQL procedure successfully completed.

PLSQL_OPTIMIZE_LEVEL=3 by default inline the sub programs and hence the elapsed time is lesser when compare with settings in first example i.e. PLSQL_OPTIMIZE_LEVEL=2

Example-4:  with PLSQL_OPTIMIZE_LEVEL=3 & with Pragma inline as NO

sql_11g> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;

Session altered.

sql_11g>

sql_11g> SET SERVEROUTPUT ON

sql_11g> DECLARE

  2    l_loops  NUMBER := 10000000;

  3    l_start  NUMBER;

  4    l_return NUMBER;

  5 

  6    FUNCTION add_numbers (p_1 IN NUMBER,

  7                          p_2 IN NUMBER)

  8      RETURN NUMBER AS

  9    BEGIN

 10      RETURN p_1 + p_2;

 11    END add_numbers;

 12 

 13  BEGIN

 14    l_start := DBMS_UTILITY.get_time;

 15 

 16    FOR i IN 1 .. l_loops LOOP

 17      PRAGMA INLINE (add_numbers, 'NO');

 18      l_return := add_numbers(1, i);

 19    END LOOP;

 20 

 21    DBMS_OUTPUT.put_line('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

 22  END;

 23  /

Elapsed Time: 505 hsec

PL/SQL procedure successfully completed

This gives elapsed time as 505 hsecs which means inlining has not taken place as expected.

The INLINE pragma only affects the following types of statements.

·         Assignment

·         Cal

·         Conditional

·         CASE

·         CONTINUE-WHEN

·         EXECUTE IMMEDIATE

·         EXIT-WHEN

·         LOOP

·         RETURN

In each case, it affects every call to specified subprogram from the statement.