RE: Interesting PL/SQL Puzzle

I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
Mladen Gogala
Sent: Saturday, November 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.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).