Failed parse elapsed time in AWR Oracle
Failed parses occurred when someone attempts to run an invalid SQL statement which having syntax issues or object not available.
Example of alert log resembles to errors:
PARSE ERROR: ospid=1852, error=936 for statement: ORA-00936: missing expression (Syntax is wrong)
PARSE ERROR: ospid=644, error=942 for statement: ORA-00942: table or view does not exist ( object is missing)
PARSE ERROR: ospid=6428, error=6550 for statement: ORA-06550: Line 1, column 7: (error in pl/sql block)
In AWR report, one of database is showing the FAILED PARSE ELAPSED TIME statistic name is consuming CPU resources.
In AWR Report: You see the Time model statistics block
Time Model Statistics Statistic Name % OF DB TIME % of Total CPU Time sql execute elapsed time 49,470.54 52.59 failed parse elapsed time 682.72 5.19 hard parse elapsed time 508.37 3.87
In Addm report if you found this error, it means Parse errors consuming lot of CPU in hard parsing
Finding: Hard Parse Due to Parse Errors
Hard parsing SQL statements that encountered parse errors was consuming
significant database time.
Solution
For tracing the failed parsing result in alert log from which application module its generated by help of enabling the event
1. Enable the trace at system level
alter system set events '10035 trace name context forever, level 1';
2. Check the alert log its getting error message with OSPID
PARSE ERROR: ospid=1852, error=936 for statement:
2018-12-09T22:42:36.914209-06:00
SELECT image_size, cap_location, perm_location, cap_file_ptr, perm_file_ptr FROM HR.SALES WHERE entity_no = '005028' date_captured = '10-Dec-2018';
3. Use above ospid value into the following query to Get the session detail and module name.
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,15) box,
substr(b.username,1,8) username,
substr(b.osuser,1,10) os_user,
substr(b.program,1,30) program,
substr(b.status,1,10) status
from v$session b, v$process a
where
b.paddr = a.addr and a.spid = '1852'
order by spid;
4. Disable the trace if you found the information
alter system set events '10035 trace name context off';
Note: Temporary Workaround
Oracle also provide temporary work around to use _cursor_features_enabled
parameter. Its default value is 2. It worked to reduce CPU overhead by saving failed parsing query in table SQLERROR$. For enable it you need to set value to 34. It effect on DB restart. It is not dynamic parameter.
-- Check current value
col Name for a25
col Value for a10
col Description for a30
select ksppinm "Name",ksppstvl "Value",ksppdesc "Description" from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm = '_cursor_features_enabled';
--Change to 34
alter system set "_cursor_features_enabled" = 34 scope=spfile;
Example from application team for trace module name
1. Execute the following syntax error command from other session:
Select * from dba_file;
2. In Alert Log getting the error because trace is enabled
PARSE ERROR: ospid=8392, error=942 for statement:
2018-12-13T03:27:21.710995-06:00
select * from dba_file
3. Check session detail as:
select
substr(a.spid,1,5) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,15) box,
substr(b.username,1,8) username,
substr(b.osuser,1,10) os_user,
substr(b.program,1,30) program,
substr(b.status,1,10) status
from v$session b, v$process a
where
b.paddr = a.addr and a.spid = '8392'
order by spid;
PID SID SER# BOX USERNAME OS_USER PROGRAM STATUS ---- --- ----- ------- -------- ------- ----------- ------ 8392 422 28636 XXXX\XX SYS SYSTEM sqlplus.exe ACTIVE