RE: High Parse with no execution of SQL statements

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 7 Feb 2014 21:15:07 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDB209_at_exmbx05.thus.corp>

I'd go with the "parse call made but no execute". If the parse raised an error I think you'd see "SQL*Net break/reset to client"

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Ric Van Dyke [ric.van.dyke_at_hotsos.com] Sent: 07 February 2014 20:49
To: angani_at_gmail.com; ORACLE-L
Subject: RE: High Parse with no execution of SQL statements

It is certainly possible. Just depends on how the code is called. With OCI for example you could code something that makes the PARSE call but never makes the EXECUTE call. If that is done I would hope it was some logic in the code that decided not to do the EXECUTE call.

It’s possible the code errors out at execution or at parse time. Is this TKPROF output? Look for the code in the raw trace file, what’s happening there. I “think” it will show as a parse even if it fails. If there is an error you should see that in the raw 10046 trace.

+--+--+--+--+--+--+--+--+--+--+--+--+--+--+ Ric Van Dyke
Education Director
Hotsos Ltd.

Hotsos Symposium March 2-6 2014
Make your plans to be there now!

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Raju Angani Sent: Friday, February 07, 2014 12:48 PM To: ORACLE-L
Subject: High Parse with no execution of SQL statements

Hi,

I'm noticing a strange issue on my rhel5 linux oracle 11.2.0.3 server. I see heavy(few cases 400k) parsing of a sql statement but with zero execution. Is this possible?

Can someone send me some pointers?

Shared Cursors Statistics

Total Parses              409,770
Executions:       0
Hard Parses                3
Child Cursors                       3
Loaded Plans                       3
Invalidations             0
Largest Cursor Size (KB)                29.07

SQL ID: dm18sav0z3u5y Plan Hash: 0

SELECT NAME,PARENTID,DESCRIPTION,ENTITY_NAME,LOCKED_DATA,   STATUS_STATE,CANCELLED,CANCELLABLE,ERROR_DATA,RESULT_DATA,REASON_DATA,   ,START_TIME,EVENTCHAIN_ID
FROM
 XP_ASK1 call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse      319      0.01       0.49          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      319      0.01       0.49          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     319        0.00          0.00
  SQL*Net message from client                   319        0.06          1.09
********************************************************************************

Thank you
RA

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 07 2014 - 22:15:07 CET