Oracle Pragma INLINE
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Data Dictionary Objects
ALL_SOURCE CDB_SOURCE_AE SOURCE$
ALL_SOURCE_AE DBA_SOURCE USER_SOURCE
CDB_SOURCE DBA_SOURCE_AE USER_SOURCE_AE
Statements Automatically Inlin If PLSQL_OPTIMIZE_LEVEL=2, the subprogram call is not inlined unless you specifically indicate using PRAGMA INLINE.

If PLSQL_OPTIMIZE_LEVEL=3, Oracle automatically inlines when possible.
Statements Affected by Inlining
ASSIGNMENT CONDITIONAL EXIT-WHEN
CALL CONTINUE-WHEN LOOP
CASE EXECUTE IMMEDIATE RETURN
 
Inlining Demo
Inlining Demo PRAGMA INLINE (identifier, '<YES | NO>');
conn / as sysdba

-- check optimizer level
set linesize 121
col name format a50
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name = 'plsql_optimize_level';
-- should be 2 ... if it isn't ... change it to 2

conn uwclass/uwclass

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;
/

-- run it a second time

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;
/

==========================================
CREATE OR REPLACE PROCEDURE regular_proc AUTHID DEFINER IS
 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 regular_proc;
/

CREATE OR REPLACE PROCEDURE inline_proc AUTHID DEFINER IS
 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 inline_proc;
/

SELECT DISTINCT text
FROM user_source
WHERE name = 'REGULAR_PROC';

SELECT DISTINCT text
FROM user_source
WHERE name = 'INLINE_PROC';

SELECT *
FROM user_object_size
WHERE name IN ('REGULAR_PROC', 'INLINE_PROC');

ALTER SESSION SET plsql_optimize_level = 3;

CREATE OR REPLACE PROCEDURE level_three AUTHID DEFINER IS
  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 level_three;
/

SELECT *
FROM user_object_size
WHERE name IN ('REGULAR_PROC', 'INLINE_PROC', 'LEVEL_THREE');

ALTER SESSION SET plsql_optimize_level = 2;

col name format a30

SELECT name, plsql_optimize_level
FROM user_plsql_object_settings;

Related Topics
Functions
Packages
Pipelined Table Functions
Pragmas
Procedures
Table Triggers

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved