Using Oracle Enterprise Manager to monitor Database Activity

Oracle Enterprise Manager (OEM) provides comprehensive monitoring and management capabilities for Oracle Database 19c. To monitor database activity using OEM, follow these steps:

1. Access Oracle Enterprise Manager (OEM)
   • Open a web browser and enter the OEM URL (e.g., `https://:/em`).
   • Log in with administrative credentials (e.g., `SYSMAN` or another user with necessary privileges).
2. Navigate to Database Monitoring
   • Click on Targets > Databases to view the list of registered databases.
   • Select the Oracle 19c Database you want to monitor.
3. Monitor Database Activity Using Performance Pages
   • OEM provides several performance pages that allow you to monitor various aspects of database activity.
     1. Performance Hub
        • Go to Performance > Performance Hub.
        • The Performance Hub displays real-time and historical session activity, including:
          • Active Sessions
          • Top SQL
          • Wait Events
          • Blocking Sessions
          • Parallel Execution
     2. Active Sessions Monitoring
        • Navigate to Performance > Active Sessions.
        • This page shows real-time activity per session, including:
          • CPU and I/O consumption
          • SQL Execution
          • User Sessions
          • Blocking Locks
     3. SQL Monitoring
        • Click Performance > SQL Monitoring.
        • Identify long-running queries, execution plans, and wait events affecting performance.
     4. Blocking Sessions
        • Navigate to Performance > Blocking Sessions.
        • View which sessions are blocking others, allowing you to resolve contention.
     5. ASH Analytics (Active Session History)
        • Go to Performance > ASH Analytics.
        • Provides historical database workload analysis.
4. Using Automatic Database Diagnostic Monitor (ADDM)
   • Navigate to Performance > ADDM.
   • ADDM provides recommendations for improving database performance, such as:
     • SQL tuning suggestions
     • Indexing recommendations
     • Bottleneck identification
5. Monitoring Database Alerts
   • Click on Target > Databases > Select your database.
   • Navigate to Availability > Metrics and Alerts to check for:
     • Tablespace usage
     • Session Limits
     • CPU and Memory Utilization
     • Error logs
6. Configure Custom Alerts
   • Navigate to Setup > Monitoring > Metric and Collection Settings.
   • Define custom alerts for specific thresholds like:
     • High CPU usage
     • Long-running SQL queries
     • Storage limits
7. Generating Reports
   • Use Performance Hub and AWR (Automatic Workload Repository) Reports.
   • Navigate to Performance > AWR Report to generate detailed performance reports.
8. Enabling Database Tuning
   • Click Performance > SQL Tuning Advisor.
   • Use the SQL Tuning Advisor to optimize queries and indexes.

By leveraging Oracle Enterprise Manager 19c, you can efficiently monitor, diagnose, and optimize database performance in real-time.

One of the handiest tools to use for all types of tasks is Oracle Enterprise Manager (OEM). OEM can be used to examine the current state of the database, but it can also do much more than simply provide a window into the operations of Oracle. In terms of performance monitoring, OEM offers the Tuning Pack, a set of programs that help you to understand and tune your Oracle database. The Tuning Pack includes:

1. Oracle Tablespace Analyzer: This module specializes in identifying fragmentation in tablespaces, tables, and indexes and gives you the ability to correct these problems easily.
2. Auto-Analyze: The ANALYZE command gathers statistics on your database. The Auto-Analyze module lets you schedule ANALYZE jobs so that this process will occur as part of a regular maintenance process.
3. Oracle Expert: Oracle Expert is a comprehensive analysis program that examines the typical usage of your database and recommends changes you could make to improve database performance, such as new indexes that would improve query performance.
4. Oracle Index Tuning Wizard: This wizard analyzes the usage of indexes in your Oracle database and makes recommendations for improving index usage. This wizard also generates scripts to implement suggested improvements.
5. Oracle SQL Analyze: This module analyzes individual SQL statements to display the execution path chosen by the query optimizer. The module will also help you to rewrite SQL queries for better performance.

Continuous monitoring of the system, network, application, and database operations ensures early detection of problems. Early detection improves the user's system experience because problems can be resolved faster. In addition, monitoring captures system metrics to indicate trends in system performance growth and recurring problems. This information can facilitate prevention, enforce security policies, and manage job processing. For the database server, a sound monitoring system needs to measure availability and detect events that can cause the database server to become unavailable and provide immediate notification to responsible parties for critical failures.
The monitoring system itself needs to be highly available and adhere to the same operational best practices and availability practices as the resources it monitors. Failure of the monitoring system leaves all systems that it monitors unable to capture diagnostic data or alert the administrator of problems. Oracle Enterprise Manager provides the management and monitoring capabilities with many different notification options. Recommendations are available for methods of monitoring the environment's availability and performance and for using the tools in response to changes in the environment.
The next lesson concludes this module.