Oracle: Automatic Subprogram Inlining

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.

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.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 10000000;
  l_start  NUMBER;
  l_return NUMBER;

  FUNCTION add_numbers (p_1 IN NUMBER,
                        p_2 IN NUMBER)
    RETURN NUMBER AS
  BEGIN
    RETURN p_1 + p_2;
  END add_numbers;

BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    --PRAGMA INLINE (add_numbers, 'YES');
    l_return := add_numbers(1, i);
  END LOOP;

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

PL/SQL procedure successfully completed.

SQL>

This results in an elapsed time of 509 hsecs.

Next, we keep the same optimization setting, but include the INLINE pragma with a setting of "YES" for the calls to the ADD_NUMBERS function. We would now expect subprogram inlining to take place.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 10000000;
  l_start  NUMBER;
  l_return NUMBER;

  FUNCTION add_numbers (p_1 IN NUMBER,
                        p_2 IN NUMBER)
    RETURN NUMBER AS
  BEGIN
    RETURN p_1 + p_2;
  END add_numbers;

BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    PRAGMA INLINE (add_numbers, 'YES');
    l_return := add_numbers(1, i);
  END LOOP;

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

PL/SQL procedure successfully completed.

SQL>

This gives an elapsed time of 245 hsec, which is approximately half that of the previous test, implying that subprogram inlining is taking place.

Next, we make sure PLSQL_OPTIMIZE_LEVEL=3 and run the code with no INLINE pragma set. We would now expect the optimizer to implicitly choose to inline the ADD_NUMBERS call.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 10000000;
  l_start  NUMBER;
  l_return NUMBER;

  FUNCTION add_numbers (p_1 IN NUMBER,
                        p_2 IN NUMBER)
    RETURN NUMBER AS
  BEGIN
    RETURN p_1 + p_2;
  END add_numbers;

BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    --PRAGMA INLINE (add_numbers, 'YES');
    l_return := add_numbers(1, i);
  END LOOP;

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

PL/SQL procedure successfully completed.

SQL>

This gives an elapsed time of 245 hsec, which implies that subprogram inlining is still taking place.

Finally, we make sure PLSQL_OPTIMIZE_LEVEL=3 and run the code with an INLINE pragma set to "NO". We would expect there to be no inlining of the ADD_NUMBERS call now.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 10000000;
  l_start  NUMBER;
  l_return NUMBER;

  FUNCTION add_numbers (p_1 IN NUMBER,
                        p_2 IN NUMBER)
    RETURN NUMBER AS
  BEGIN
    RETURN p_1 + p_2;
  END add_numbers;

BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    PRAGMA INLINE (add_numbers, 'NO');
    l_return := add_numbers(1, i);
  END LOOP;

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

PL/SQL procedure successfully completed.

SQL>

This gives an elapsed time of 500 hsecs, which implies that inlining did not take place as we expected.

The INLINE pragma only affects the following types of statements.

• Assignment
• Call
• Conditional
• CASE
• CONTINUE-WHEN
• EXECUTE IMMEDIATE
• EXIT-WHEN
• LOOP
• RETURN

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

The optimizer can choose to ignore an INLINE pragma setting of "YES" if it believes inlining is undesirable, but a setting of "NO" will always prevent inlining.

The compiler inlines subprograms early in the optimization process, which may preventing later, more powerful optimizations taking place. As a result, performance almost always improves with inlining, but in some cases it may not be effective.

show paramaters:

SHOW PARAMATERS SET PLSQL_OPTIMIZE_LEVEL;