Re: Interesting PL/SQL Puzzle

Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are.

On 2003.11.08 13:09, "Khedr, Waleed" wrote:
> 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).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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