RE: Parse taking over 2 minutes

From: Naik, Sandesh <Sandesh.Naik_at_compaq.com>
Date: Thu, 17 Aug 2000 13:12:52 -0500
Message-Id: <10592.114879@fatcity.com>

Is it true for 8.0.6 also? We have recently upgraded one instance from = 8.0.4
to 8.0.6 on windows NT.
=20

-----Original Message-----
From: paquette stephane [mailto:stephane_paquette_at_yahoo.com] Sent: Thursday, August 17, 2000 4:15 AM
To: Multiple recipients of list ORACLE-L Subject: Re: Parse taking over 2 minutes

I had a lot of parsing and some long parse time also . If you're on 8.1.5.x with timed_statistics=3Dtrue than it's normal, it's a bug .

timed_statistics=3Dtrue on 8.1.5.x prevents Oracle from sharing cursors, so you parse like crazy.

Are you on 8.1.5.x ?

• Linda Hagedorn <Linda_at_pets.com> a =E9crit=A0: > This is from a .trc tkprof file. I cannot imagine
  > why a parse would take
  > 115 or 131 seconds. Does anyone have any ideas? I
  > have several of these.
  >=20 >=20
  > Any information or referral is appreciated. Thanks,
  > Linda=20
  >=20 >=20
  > UPDATE BV_PRICED_ITEMS_TABLE SET ITEM_STATE=3D21=20
  > WHERE ORDER_NUMBER =3D :b1
  > call count cpu elapsed disk =20
  > query current
  > rows
  > ------- ------ -------- ---------- ----------
  > ---------- ----------
  > ----------
  > Parse 1 0.00 115.72 0 =20
  > 0 0
  > 0
  > Execute 1 0.00 0.00 0 =20
  > 7 4
  > 0
  > Fetch 0 0.00 0.00 0 =20
  > 0 0
  > 0
  > ------- ------ -------- ---------- ----------
  > ---------- ----------
  > ----------
  > total 2 0.00 115.72 0 =20
  > 7 4
  > 0
  >=20 >=20
  > begin
  > PKG_CSR.CSR_SEARCH_ORDERS(IN_ORDER_NO=3D>:V001,IN_ORDER_DATE=3D>:V002,
  > =20
  > IN_EMAIL_ADDRESS=3D>:V003,IN_PHONE_NO=3D>:V004,IN_ZIP_CODE=3D>:V005,IN_S= HIP_NAME=3D
  > =20
  > >:V006,IN_CARD_NAME=3D>:V007,IN_CARD_NUMBER=3D>:V008,ORDER_NUMBER=3D>:R0= 01C001,
  > =20
  > USER_ID=3D>:R001C002,ORDER_DATE_FMT=3D>:R001C003,CANCEL_FLAG=3D>:R001C00= 4,
  > =20
  > ORDER_STATUS=3D>:R001C005,BILL_NAME=3D>:R001C006,SHIP_NAME=3D>:R001C007,= SHIP_ADDR=3D
  > =20
  > >:R001C008,SHIP_CITY=3D>:R001C009,SHIP_STATE=3D>:R001C010,SHIP_ZIP=3D>:R= 001C011)
  > ;end;
  > call count cpu elapsed disk =20
  > query current
  > rows
  > ------- ------ -------- ---------- ----------
  > ---------- ----------
  > ----------
  > Parse 4 0.00 0.00 0 =20
  > 0 0
  > 0
  > Execute 4 0.02 131.38 0 =20
  > 0 0
  > 4
  > Fetch 0 0.00 0.00 0 =20
  > 0 0
  > 0
  > ------- ------ -------- ---------- ----------
  > ---------- ----------
  > ----------
  > total 8 0.02 131.38 0 =20
  > 0 0
  > 4
  >=20
  > Misses in library cache during parse: 0
  > Optimizer goal: CHOOSE
  > Parsing user id: 41 =20
  > ************************************************************************=

  ---

  
  > ****
  > SELECT BV_MAIN_INVOICE_TABLE.ORDER_NUMBER From
  > BV_MAIN_INVOICE_TABLE WHERE
  > BV_MAIN_INVOICE_TABLE.ORDER_NUMBER =3D'01008204'=20
  > AND BV_MAIN_INVOICE_TABLE.APPDEF2 IS NULL ORDER BY
  > order_number DESC
  >=20
  > call count cpu elapsed disk =20
  > query current
  > rows
  > ------- ------ -------- ---------- ----------
  > ---------- ----------
  > ----------
  > Parse 2 0.01 131.34 0 =20
  > 0 0
  > 0
  > Execute 2 0.00 0.00 0 =20
  > 0 0
  > 0
  > Fetch 4 0.00 0.00 0 =20
  > 10 0
  > 2
  > ------- ------ -------- ---------- ----------
  > ---------- ----------
  > ----------
  > total 8 0.01 131.34 0 =20
  > 10 0
  > 2
  >=20 >=20 >=20
  > --=20
  > Author: Linda Hagedorn
  > INET: Linda_at_pets.com
  >=20
  > 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).
  

=3D=3D=3D=3D=3D
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50

---

Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
--=20
Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D   INET: stephane_paquette_at_yahoo.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 Received on Thu Aug 17 2000 - 13:12:52 CDT