RE: High Parse with no execution of SQL statements
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "ric.van.dyke@xxxxxxxxxx" <ric.van.dyke@xxxxxxxxxx>, "angani@xxxxxxxxx" <angani@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 7 Feb 2014 21:15:07 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Ric Van Dyke [ric.van.dyke@xxxxxxxxxx]
Sent: 07 February 2014 20:49
To: angani@xxxxxxxxx; 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
