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