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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
