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