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 thePLSQL_OPTIMIZE_LEVELparameter and theINLINEpragma. WhenPLSQL_OPTIMIZE_LEVEL=2(the default), theINLINEpragma determines whether the following statement or declaration should be inlined or not. WhenPLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case theINLINEpragma 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 forPLSQL_OPTIMIZE_LEVELand theINLINEpragma are altered to switch subprogram inlining on and off. First, we make surePLSQL_OPTIMIZE_LEVEL=2and run the code with noINLINEpragma 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 theINLINEpragma with a setting of "YES" for the calls to theADD_NUMBERSfunction. 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 surePLSQL_OPTIMIZE_LEVEL=3and run the code with noINLINEpragma set. We would now expect the optimizer to implicitly choose to inline theADD_NUMBERScall.

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 surePLSQL_OPTIMIZE_LEVEL=3and run the code with anINLINEpragma set to "NO". We would expect there to be no inlining of theADD_NUMBERScall 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.

TheINLINEpragma 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 anINLINEpragma 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;