Is the problem still evident when the parameters are defined as "in" as opposed to "in out".
Cheers Connor --- Cary Millsap <[EMAIL PROTECTED]> wrote: > 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). ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ________________________________________________________________________ Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
