Re: RE: Why Multiple Parses in Trace ?

If you don't want to change your application, get the application booster from oraperf. That will do it for you ;-) Setting up one environment variable and run again, this time much faster ......

Anjo.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, July 23, 2002 8:08 AM

> 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: VIVEK_SHARMA_at_infosys.com
> >
> >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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
> >
> >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: ListGuru_at_fatcity.com (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
> chaospku_at_163.net
>
> zhu chao
> DBA of Eachnet.com
> 86-021-32174588-667
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: chaos
> INET: chaospku_at_163.net
>
> 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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
>
> 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: ListGuru_at_fatcity.com (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: vkhanna_at_quark.co.in
>
> 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: ListGuru_at_fatcity.com (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: Anjo Kolk
  INET: anjo_at_oraperf.com

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: ListGuru_at_fatcity.com (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).