Oracle-L: RE: Interesting PL/SQL Puzzle

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Sat, 08 Nov 2003 15:34:26 -0800
Message-ID: <F001.005D619F.20031108153426@fatcity.com>

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

Waleed

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

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

**********4
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------