Oracle Pragma Inline

Oracle Pragma INLINE Version 11.2.0.3

General Information

Data Dictionary Objects

ALL_SOURCE	DBA_SOURCE	USER_SOURCE
ALL_SOURCE_AE	SOURCE$	USER_SOURCE_AE
DBA_SOURCE

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

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;