Oracle Pragma INLINE
Version 19.3

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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
What's New In 18cR3
What's New In 19cR3

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