Oracle FAQ

Your Portal to the Oracle Knowledge Grid

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Interesting PL/SQL Puzzle

RE: Interesting PL/SQL Puzzle

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 10 Nov 2003 08:09:42 -0800
Message-ID: <F001.005D639D.20031110080942@fatcity.com>

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: Waleed.Khedr_at_FMR.COM

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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM

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: ListGuru_at_fatcity.com (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). Received on Mon Nov 10 2003 - 10:09:42 CST

This message: [ Message body ]
Next message: Stephane Faroult: "RE: Re: Re: Migration"
Previous message: Jamadagni, Rajendra: "RE: Interesting PL/SQL Puzzle"
Maybe in reply to: Khedr, Waleed: "Interesting PL/SQL Puzzle"
Next in thread: Chelur, Jayadas {PBSG}: "RE: Interesting PL/SQL Puzzle"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US