i remember in tom kytes new book there is a 'softer parse' he was referring to using dbms_sql instead of execute immediate. Im not referring to using dbms_sql when you have to loop and use the same cursor repeatedly so you eliminate all parsing.
he didnt go into great detail on this just gave benchmarks. do you know anymore? > > From: "Cary Millsap" <[EMAIL PROTECTED]> > Date: 2003/11/26 Wed PM 02:39:39 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: Parse Vs Execute > > Don't do this: > > Loop > Parse > Execute > Fetch > End loop > > Do this: > > Parse > Loop > Execute > Fetch > End loop > > If you parse inside your loop, then all that using bind variables will > gain you is a reduced "hard parse" count. If you parse outside the loop > (in which case, you MUST use bind variables), then you reduce your > number of parse calls. A "soft parse" is a little cheaper than a "hard > parse." NO PARSE is a lot cheaper than a "soft parse." > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta > - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > [EMAIL PROTECTED] > Sent: Wednesday, November 26, 2003 12:14 PM > To: Multiple recipients of list ORACLE-L > > Hi List, > > Almost fro all SQLs I am getting Prase count is same as Execute count. > How to reduce parse count? > > 1) We are using bind variable > 2) session_cached_cursors set to 100 > > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > Parse 11 0.01 0.02 0 0 0 > 0 > Execute 11 0.00 0.00 0 0 0 > 0 > Fetch 22 0.01 0.00 0 33 44 > 110 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > total 44 0.02 0.02 0 33 44 > 110 > > Any somebody give more hint on this? > > Thanks > Jay > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
