Problem Statement
Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also known as DBCN to be aware of changes in the database, based on these notifications they take further actions. WAIT FOR EMON PROCESS NTFNS can also been seen sometimes and needs attention.
Often clients subscribe to the database and notifications are send back as callback to them. In some cases we will notice database sessions are showing wait event “WAIT FOR EMON PROCESS NTFNS” and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases).
What does EMOM do?
As stated EMON process deals with database notification and it’s spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.
EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In some cases when these notifications are not being send to client we see these wait events. This could be due to multiple reasons and we will discuss some cases here which will be helpful in determining the root cause of this problem.
When the rate of enqueue is faster than the rate at which the EMON can process, the channel gets filled and enqueuer will be waiting for the channel to get freed by EMON.
As such notifications do not spill and when the buffer is full, flow control kicks off and prevents EMON to send new notifications until there is more buffer to process pending notifications. This blocks other sessions doing DMLs against subscribed table on ‘Wait for EMON to process ntfns’.
In order to identify the latency of these notifications, we can use view v$subscr_registration_stats (unfortunately there is no historical view to identify historical statistics).
Solution
To resolve this problem needed patch already available in 12.1 and following, two changes needed to be in place to activate them.
Action 1: This enables automatic un-registration of client process which are not reachable post timeout setting mentioned in action 2.
conn / as sysdba
alter system set "_client_enable_auto_unregister" = TRUE scope=spfile sid='*';
Action 2: This sets the timeout setting for EMON process in milli seconds. By default this value is 10seconds in 12.1 and 2hours in 18c. Below sets the emon timeout to 60 seconds.
alter system set "_emon_send_timeout" = 60000 scope=spfile sid='*';
Note : Above both are underscore parameters and you have to be careful while placing them in production environment, though metalink notes suggests to place them but would suggest to engage with oracle support before changing these parameters.
In order to verify if these underscore parameters are changes use below query:
set line 100
col Parameter for a50
col "Session Value" for a20
col "Instance Value" for a20
SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm in ( '_client_enable_auto_unregister', '_emon_send_timeout' )
/Some documents also suggest to use SQLNET.SEND_TIMEOUT=10 in sqlnet.ora along with above two steps.
In order to identify the currently running EMON process we can use
ps -ef | grep -i e00
and also v$emon viewTo summarize this waiting event could occur due to:
1. Notification enqueue is faster than dequeue.
2. Client is not reachable which has subscribed to notification and timeout is very high.
3. Network problem in between database and server.
References:
- Users Blocked and Waiting on ‘Wait for EMON to Process Ntfns’ When Using DCN (Doc ID 1257264.1)
- Sessions Hang on Wait Event “WAIT FOR EMON PROCESS NTFNS” (Doc ID 1287435.1)
- Other blogs for reference
