RE: Interesting PL/SQL Puzzle (found the cause)

It seems it's not because of bad code :)

Replace any "LIKE" by "=" in the TEST_PLSQL1 it becomes ten times faster, although none of them gets executed!

Waleed

-----Original Message-----
Sent: Sunday, November 09, 2003 12:39 PM To: Multiple recipients of list ORACLE-L

I had the same thought, also thought that if you have something like this that does not get executed:

if VAR1 like '%abcbdbdbbbfdbfdfdfd%'

The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it.

I'm testing and will update the list, if I managed to find anything!

Thanks

Waleed

-----Original Message-----
Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L

I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me):

Maybe (a big MAYBE) oracle translate: if then... else.... as: If cond then do; else go to line 250.

And the go to line 250 is counting lines until it arrive to the correct line number.
So decreasing the source lines speed up the execution.

Yechiel Adar
Mehish
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, November 08, 2003 8:09 PM

> 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: Yechiel Adar
  INET: adar76_at_inter.net.il

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).
-- 
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).