RE: High Parse with no execution of SQL statements

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Fri, 7 Feb 2014 14:49:28 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0226DFE7_at_WIN02.hotsos.com>

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 - 21:49:28 CET