If this statement is a loop and the counter works atleast twice, it would show 2 parse calls and 2 executions. You should basically put this statement in the main class and just bind the variables in the callable classes and just execute the statement.
In this way it would be 1 parse call and many executions. Anyway a soft parse is not too bad as compared to Hard parse and the applications have proved them to be reasonable functioning well. The CPU and the memory consumed is significantly just 1% what was compared with the hard parse. Vikas Khanna -----Original Message----- Sent: Tuesday, July 23, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Change the application so that it doesn't parse twice. See www.hotsos.com/dnloads/1.Holt,Millsap2000.03.01-Scaling.pdf for details. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on Oracle(r) System Performance, Feb 9-12 Dallas -----Original Message----- Sent: Monday, July 22, 2002 10:43 PM To: Multiple recipients of list ORACLE-L Cary Millsap， As for the soft parse, it also consumes cpu , how can we eleminate the softparse? Thanks. 2002-07-22 08:58:00 You wrote: >...Because the application requested two parse calls for this statement >from the server. The first one was a hard parse (server had never seen >the statement before), and the second one was a parse call that did not >result in a hard parse. > > >Cary Millsap >Hotsos Enterprises, Ltd. >http://www.hotsos.com > >Upcoming events: >- Hotsos Clinic, Jul 23-25 Chicago >- Miracle Database Forum, Sep 20-22 Middlefart Denmark >- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas > > > >-----Original Message----- >VIVEK_SHARMA >Sent: Monday, July 22, 2002 8:03 AM >To: Multiple recipients of list ORACLE-L > > >Qs Why is Parse = 2 in the following Query ? > >Solaris 8 >Oracle 8.1.7 >SGA = 60 MB >shared_pool_size = 30 MB > >*********************************************************************** * >******** > >select mesg, lchg_user_id, TO_CHAR(lchg_time,'DD-MM-YYYY HH24:MI:SS'), > rcre_user_id, TO_CHAR(rcre_time,'DD-MM-YYYY HH24:MI:SS'), tran_id, > TO_CHAR(tran_date,'DD-MM-YYYY HH24:MI:SS'), NVL(ts_cnt,0), sol_id, > contra_acid, tran_amt||'!'||tran_crncy_code, >TO_CHAR(value_date,'DD-MM-YYYY > HH24:MI:SS'), tran_crncy_code, central_or_local_code, req_advc_ind, > sys_gen_flg, rowid >FROM > TBA_REF_TRN_TBL WHERE cmd = :1 AND cust_or_card_id = :2 AND > system_date_time = TO_DATE( :3 ,'DD-MM-YYYY HH24:MI:SS') AND dcc_id = >:4 > AND sno = :5 > > >call count cpu elapsed disk query current >rows >------- ------ -------- ---------- ---------- ---------- ---------- >---------- >Parse 2 0.06 0.07 1 0 1 >0 >Execute 2 0.00 0.00 0 0 0 >0 >Fetch 2 0.00 0.02 3 6 0 >0 >------- ------ -------- ---------- ---------- ---------- ---------- >---------- >total 6 0.06 0.09 4 6 1 >0 > >Misses in library cache during parse: 1 >Optimizer goal: CHOOSE >Parsing user id: 40 (TBAGEN) > >Rows Row Source Operation >------- --------------------------------------------------- > 0 TABLE ACCESS BY INDEX ROWID REF_TRN_TBL > 1 INDEX UNIQUE SCAN (object id 6561) > > > >Rows Execution Plan >------- --------------------------------------------------- > 0 SELECT STATEMENT GOAL: CHOOSE > 0 TABLE ACCESS (BY INDEX ROWID) OF 'REF_TRN_TBL' > 1 INDEX (UNIQUE SCAN) OF 'IDX_REF_TRN_TBL' (UNIQUE) > >*********************************************************************** * >******** >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: VIVEK_SHARMA > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists >-------------------------------------------------------------------- >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.com >-- >Author: Cary Millsap > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists >-------------------------------------------------------------------- >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). Good luck! chaos [EMAIL PROTECTED] zhu chao DBA of Eachnet.com 86-021-32174588-667 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chaos INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
