I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC.
Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- Mladen Gogala Sent: Saturday, November 08, 2003 2: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: Cary Millsap 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).
