CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Pragma INLINE
Version 11.1
 
General
Related Data Dictionary Objects
source$ dba_source all_source user_source
Automatic Inlining 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 CASE EXIT-WHEN
Call CONTINUE-WHEN LOOP
Conditional EXECUTE IMMEDIATE RETURN
 
Pragma Inline 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

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 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 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 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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [237 users online]    © 2010 psoug.org