Debugging APEX_DEBUG

TLDR: If you are writing a LOT of debug messages but can't find some of them in the debug log (but know they should be there) you are hitting the APEX_DEBUG flood control limit.

I recently had a conversation with a super knowledgable and effective APEX expert about an issue he was having with APEX_DEBUG.MESSAGE, though it also applies to .INFO, .ERROR, etc. The issue was that some messages simply were not showing up in the debug log. He went so far as to have two lines of code:

for i in (select id, ... from ...) loop
  apex_debug.info('id: ' || i.id);
  -- autonomous transaction that just inserts into a log table
  log_this('id: ' || i.id); 
...
end loop;

log_this logged all of the messages but apex_debug.info did not.

Well, this blog is apexdebug.com, so I should know the answer...but it took a little while to figure it out. In short, we all might think that APEX_DEBUG.MESSAGE is some simple procedure that just does an autonomous transaction and writes to the APEX log table. We'd be wrong. APEX_DEBUG.MESSAGE is a complex bit of magic that bundles up messages and does bulk inserts in order to improve performance. It will split really long messages into 4000 character chunks and write multiple rows in order the message into the varchar2 column. And, importantly, it also has flood control to make sure that the logs don't get overwhelmed by a runaway process. In the case above, the flood control was kicking in. The APEX engine has a limit of how many debug messages it will allow from a page in a given amount of time. I don't know all of the logic, but I know that this query will get you the limit

select *
  from wwv_flow_platform_prefs
  where name = 'DEBUG_MESSAGE_PAGE_VIEW_LIMIT'

which is almost certainly 50,000. Exactly how long a period 50,000 messages are allowed in, and other details, remain a mystery to me...and not one I'm going to pursue because it's enough for me to simply know that if I'm missing a few debug messages it's because flood control kicked in. I probably should write fewer messages--I'm unlikely to sift through 50,000 anyway.

As far as I know there is not any user interface to set DEBUG_MESSAGE_PAGE_VIEW_LIMIT to another value, but in a pinch you could update the table--just don't tell anyone that I recommended that.