Resolving Oracle EMON Process Slowness
Introduction:
In the world of database administration, ensuring the smooth and efficient performance of critical systems is of paramount importance. As an Oracle database administrator, I recently encountered a challenging scenario where a client reported sudden slowness in their system after applying a CPU Patch. The investigation led me to the Oracle EMON (Enterprise Manager Agent) process, which plays a pivotal role in monitoring and managing various components of the Oracle database. In this article, I will delve into the detailed analysis and resolution of the EMON-related performance degradation, as well as uncover an unpublished bug that may have contributed to the issue.
Understanding the Oracle EMON Process:
The Oracle EMON process, also known as the Enterprise Manager Agent, is an essential component in Oracle’s enterprise monitoring infrastructure. It acts as an intermediary between the Oracle database and the Oracle Enterprise Manager (OEM) Cloud Control, facilitating the monitoring, management, and automation of various tasks related to database performance, security, and availability and below is high-level overview of how the Oracle EMON process generally works:
- Event Collection: Oracle EMON (Event Manager On-Demand) is a component of Oracle Enterprise Manager that collects and manages events from various sources, such as databases, applications, and middleware. It monitors these sources for events like performance metrics, availability status, and other important data.
- Event Processing: When an event occurs in a monitored source, such as a performance metric reaching a threshold or an application error, the Oracle EMON process captures the event details. It processes and categorizes these events based on predefined rules and policies.
- Notification Generation: Once events are processed, Oracle EMON can generate notifications, alerts, or incidents based on the severity and impact of the event. These notifications can be sent to administrators, support teams, or other designated individuals or groups.
- Escalation and Resolution: In cases where events indicate a critical issue or require attention, Oracle EMON can initiate automated escalation processes. This could involve notifying higher-level support teams or executing predefined corrective actions to address the issue.
- Reporting and Analysis: Oracle EMON provides reporting and analytical capabilities to help administrators understand the event patterns, trends, and potential areas of improvement. It can generate reports and dashboards to visualize the performance and health of the monitored systems.
- User Interaction: Administrators can interact with the Oracle EMON system through a user interface provided by Oracle Enterprise Manager. This interface allows them to configure event collection rules, manage notifications, review event details, and take manual actions if necessary.
Observations and Initial Analysis:
Upon receiving the client’s report of system slowness, I began my investigation by collecting vital data from the database. The Automatic Workload Repository (AWR) and Active Session History (ASH) reports provided crucial insights into the system’s performance. A notable observation was that the EMON Coordinator process consumed an overwhelming 95.68% of CPU activity for the user SYS. This finding indicated that the EMON process was a key factor in the performance degradation.
AWR Report output:
| Event Waits |
%Time -outs | Total Wait Time (s) |
Avg wait (ms) |
Waits /txn | % bg time |
| library cache lock |
2360 | 5,199 | 22029 | 0.19 | 47.21 |
ASH Report Output:
| SQL ID | Planhash | % Activity | Event | % Event | SQL Text |
| c5brdpybgqss6 | 0 | 95.69 | CPU + Wait for CPU | 49.8 | ** SQL Text Not Available |
| 95.69 | library cache lock | 45.87 | ** SQL Text Not Available |
Top Service/Module
| Service | Module | % Activity | Action | % Action |
| SYS$BACKGROUND | STREAMS | 95.68 | EMON Coordinator | 95.68 |
| UNNAMED | 2.34 | |||
| UNNAMED | 2.34 |
As per the above AWR and ASH report the database is spending most of its time on EMON Coordinator which is about 95.68 % for the user SYS.
Digging Deeper: Waiting for EMON to Process Notifications (NTFNs):
Within the AWR and ASH reports, another intriguing discovery emerged – a significant portion of the EMON Coordinator’s activity was attributed to “waiting for EMON to process notifications (NTFNs).” These notifications are integral to the process of gathering and propagating information between the database and Oracle Enterprise Manager. The high volume of NTFNs could potentially be a contributing factor to the system’s slowness.
To gain further insights into the issue, I decided to examine the subscription records in the database. Subscriptions are associated with various components that EMON monitors, and they play a crucial role in alerting administrators about potential issues and events.
select count (1), subscription_name from sys.reg$ where location_name not in (select location_name from sys.loc$) and subscription_name like '%TAFTSM%' group by subscription_name;
However, to my surprise, when I queried for subscriptions, I found 31,410 records for the subscription name “SYS.”SRVQUEUE”:”TAFTSM”. Further investigation revealed that these subscriptions were nonexistent in the system, raising questions about their presence and potential impact on performance.
Output
| COUNT(1) | SUBSCRIPTION_NAME |
| 31410 | SYS.”SRVQUEUE”:”TAFTSM” |
Performance Restoration and Bug Unveiling:
The immediate impact of the subscription record cleanup was remarkable. The system’s performance was swiftly restored to its optimal state, and users experienced no further slowness issues. This outcome provided compelling evidence that the EMON process, along with its waiting for notifications (NTFNs) and the presence of redundant subscription records, was indeed the root cause of the performance degradation.
Upon further investigation and in consultation with Oracle Support, it became evident that the situation described closely resembled an unpublished Bug 7143299. This bug affected specific versions of Oracle databases, causing significant performance issues when handling redundant subscription records.
The above query gives the count of the subscription. However, in my case there was no subscription configured. Hence, I need to delete all the subscription records.
Use the below query to delete the subscription records:
delete from sys.reg$where location_name like 'net8%' and location_name not in (select location_name from sys.loc$) and subscription_name like '%TAFTSM%';
Output
31410 rows deleted.
commit;
After deleting all the records the, performance was restored back to normal.
Conclusion:
The successful resolution of the slowness issue highlights the critical role of proactive monitoring and meticulous analysis in database administration. By leveraging AWR and ASH reports, I was able to pinpoint the root cause and apply an effective solution. The incident also emphasizes the importance of keeping databases up-to-date, staying vigilant about potential bugs, and engaging with Oracle Support for timely resolutions.
As an Oracle database administrator, it is essential to stay informed about potential bugs and best practices to ensure seamless performance and reliability. Regularly engaging with Oracle Support and proactively seeking resolutions for unexpected behavior can mitigate the impact of such issues and contribute to an optimized and stable database environment. By effectively managing the Oracle EMON process, administrators can ensure a robust and responsive database ecosystem for their organizations’ critical operations.
The successful resolution of the slowness issue highlights the critical role of proactive monitoring and meticulous analysis in database administration. By leveraging AWR and ASH reports, I was able to pinpoint the root cause and apply an effective solution. The incident also emphasizes the importance of keeping databases up-to-date, staying vigilant about potential bugs, and engaging with Oracle Support for timely resolutions.
As an Oracle database administrator, it is essential to stay informed about potential bugs and best practices to ensure seamless performance and reliability. Regularly engaging with Oracle Support and proactively seeking resolutions for unexpected behavior can mitigate the impact of such issues and contribute to an optimized and stable database environment. By effectively managing the Oracle EMON process, administrators can ensure a robust and responsive database ecosystem for their organizations’ critical operations.
Additional Resources
- Oracle Support Documentation: