Oracle Pragma INLINE
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
SUPPRESS_WARNING_6009
Table Triggers
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx