Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Why Multiple Parses in Trace ?

RE: RE: Why Multiple Parses in Trace ?

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 22 Jul 2002 21:13:22 -0800
Message-ID: <F001.0049EFCF.20020722211322@fatcity.com>


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).
Received on Tue Jul 23 2002 - 00:13:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US