The stored proc does not do any DML/DDL. It does strings search/manipulation. I run it 30 million times in 30 minutes using 32 threads.
There is no wait time, it's pure CPU time that simply does not go down except when I remove the code that does not execute. -----Original Message----- Sent: Saturday, November 08, 2003 3: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: [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). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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). -- 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).
