RE: High Parse with no execution of SQL statements
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 TotalWaited
- Waited ----------
SQL*Net message to client 319 0.000.00
SQL*Net message from client 319 0.061.09
Thank you
RA
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 07 2014 - 21:49:28 CET