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