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