Hi
Last Thursday we went live with an application using APEX 5.1.3.00.05 running on Oracle 12.2.0.1 database and all seems to be going well for the users. However since go live we've started to get messages similar to this in the database alert log:
----------------------------------------------------------------------------
WARNING: too many parse errors, count=1699 SQL hash=0x4713af8a
PARSE ERROR: ospid=18436, error=6550 for statement:
2018-10-19T13:42:21.899276+01:00
declare
rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
begin
owa.init_cgi_env(:n__,:nm__,:v__);
htp.HTBUF_LEN := 63;
null;
null;
simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%';
simple_list__(3) := 'utl\_%';
simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%';
simple_list__(7) := 'htf.%';
simple_list__(8) := 'wpg_docload.%';
simple_list__(9) := 'ctxsys.%';
simple_list__(10) := 'mdsys.%';
if ((wwv_flow_epg_include_modules.authorize(
'wwv\_flow.ajax'
) = false) or (owa\_match.match\_pattern(p\_string =>
'wwv_flow.ajax'
/* */,p_simple_pattern =>
simple_list__
,p_complex_pattern =>
complex_list__
,p_use_special_chars =>
false)))
then
rc__ := 2;
else
null;
null;
wwv_flow.ajax(p_flow_id=>:p_flow_id,p_flow_step_id=>:p_flow_step_id,p_instance=>:p_instance,p_debug=>:p_debug,p_request=>:p_request,p_json=>:p_json);
if (wpg_docload.is_file_download) then
rc__ := 1;
wpg_docload.get_download_file(:doc_info);
null;
null;
null;
commit;
else
rc__ := 0;
null;
null;
null;
commit;
owa.get_page(:data__,:ndata__);
end if;
end if;
:rc__ := rc__;
end;
Additional information: hd=0x86522f28 phd=0x88539a38 flg=0x110436 cisid=103 sid=103 ciuid=103 uid=103
----------------------------------------------------------------------------
My understanding is that this particular warning message is new in 12.2 database and is intended to highlight high-frequency parsing failures. We've been trying to diagnose the cause of the parse failures but so far without success.
I believe the pl/sql identified in the warning is being executed by the APEX code. There are no errors showing up in the application or APEX logs and no sign of poor performance in the front end. In a development environment I have found that any user actions within the application which cause dynamic actions to fire appear to increment the parse failure count but can't be sure that this is the direct or only cause.
Interestingly the Workload Repository Report for the live instance for a one hour period this morning shows that the pl/sql statement from the warning message was being parsed exactly twice as often as it was executed:
Could APEX be catching the parsing error and resubmitting the statement successfully?
Grateful for any ideas on how to next steps to identify the problem.
Best wishes
Simon