Re: Interesting PL/SQL Puzzle

Khedr, Waleed wrote:
> I did, but it did not help.
> As I explained there is nothing in the code that gets executed as I
> explained in my code.

I would suggest to consider some simple things:

. data dictionary dependencies
. standard Oracle and your application's package(s) dependencies
. proper datatypes usage
. invoker/definer rights processing

 > <big block for string manipulation, two pages of code (substr, instr, etc)>

What's 'etc' here?

> Two IF statements (IF FALSE THEN) that will be always FALSE. So nothing of
> the code inside the if gets executed.
> Interestingly when I start to remove some of the code inside IF THEN, it
> starts to speed up.

Less number of inexpensive instructions is everytime better (I'm not talking about lines of code).

Trace it -- 10046/12 + dump instructions using appropriate event.

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.



> -----Original Message-----

> Sent: Saturday, November 08, 2003 2:34 PM

> To: Multiple recipients of list ORACLE-L

> 

> 

> Without knowing the actual code, I'd use dbms_profiler and run this test say

> a thousand time. Analysis of collected data will help you find the problem

> spots.

> 

> -----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: Vladimir Begun
  INET: vladimir.begun_at_oracle.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).