Below are two dummy procs that are good enough to explain the issue (Jared
forgive me for posting this big code).
All the code in proc test_plsql1 is inside an IF clause that will not run.
Testing the proc call is done using this:
-------------------
declare
a varchar2(1000);
b varchar2(1000);
c varchar2(1000);
d varchar2(1000);
e date := sysdate;
begin
for i in 1..1000000 loop
test_plsql5(a,b,c,d);
end loop;
dbms_output.put_line( (sysdate - e) * 24 * 60 * 60);
end;
/
(runs in 4 sec)
-----------------------
declare
a varchar2(1000);
b varchar2(1000);
c varchar2(1000);
d varchar2(1000);
e date := sysdate;
begin
for i in 1..1000000 loop
test_plsql1(a,b,c,d);
end loop;
dbms_output.put_line( (sysdate - e) * 24 * 60 * 60);
end;
/
(runs in 38 sec)
Here are two procs:
----------------------------------------------------------------------------
---------------------------------------------------------
CREATE OR REPLACE PROCEDURE test_plsql5 (var1 in out varchar2, var2 in out
varchar2, out1 in out varchar2, out2 in out varchar2) as
pat1 varchar2(1000) := '%tttttttttttttttttt%';
pat2 varchar2(1000) := 'lllllllllllllllllllllll';
pat3 varchar2(1000) := '%dfddddddddddddddddddiii%';
pat4 varchar2(1000) := 'yyyyyyyyyyyyyyyyyyyyyyyyy';
begin
if false then
null;
end if;
end;
/
----------------------------------------------------------------------------
----------------------------------------------------------
CREATE OR REPLACE PROCEDURE test_plsql1 (var1 in out varchar2, var2 in out
varchar2, out1 in out varchar2, out2 in out varchar2) as
pat1 varchar2(1000) := '%tttttttttttttttttt%';
pat2 varchar2(1000) := 'lllllllllllllllllllllll';
pat3 varchar2(1000) := '%dfddddddddddddddddddiii%';
pat4 varchar2(1000) := 'yyyyyyyyyyyyyyyyyyyyyyyyy';
begin
if false then
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
end if;
end;
/
---------------------------------------------
-----Original Message-----
Sent: Saturday, November 08, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
I have a weird problem. It seems that execution speed of pl/sql proc can
slow down dramatically as the size of the proc goes up even if nothing gets
executed.
Let me explain:
I have a proc that looks like:
Proc test_1 (p1 in out varchar2, p2 in out varchar2) as
<some declared variables>
begin
if condition1 then
<big block for string manipulation, two pages of code (substr, instr,
etc)>
end if;
if condition2 then
<another big block for string manipulation, two pages of code (substr,
instr, etc)>
end if;
end;
If I change the proc to do nothing by altering it this way:
Proc test_2 (p1 in out varchar2, p2 in out varchar2) as
<some declared variables>
begin
if false then
<big block for string manipulation>
end if;
if false then
<another big block for string manipulation>
end if;
end;
The execution speed goes up a little bit but is still at least 50 percent
slower than if I change the proc by removing the code in the "if" clause,
look below:
Proc test_3 (p1 in out varchar2, p2 in out varchar2) as
<some declared variables>
begin
if false then
null;
end if;
if false then
null;
end if;
end;
proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20
minutes.
Also test_2 required more CPU resources while running.
Also I tried native compilation, which did not do a lot (only 10 % faster).
When I looked at the C code generated by the native compilation, I was not
very pleased the way native compilation works.
Does anybody have a clue why?
I tried to include the proc in a package and pin it but there was no
difference.
Thanks
Waleed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
