Oracle Enterprise Manager (OEM) provides a set of tools and utilities to manage and monitor the performance of an Oracle database. The following are the steps to manage and monitor an Oracle database’s performance using OEM.
1. Login to OEM: Login to the Oracle Enterprise Manager using a web browser by navigating to the URL http://<hostname>:<port>/em (replace <hostname> with the host name or IP address where the OEM is installed and <port> with the port number of the OEM).
2. Navigate to Performance Monitoring: In the OEM home page, click on the "Targets" menu and select "Databases". Click on the database that you want to manage and monitor. In the database home page, click on the "Performance" tab.
3. View Performance Information: The performance tab shows summary information about the database performance, such as CPU usage, memory usage, I/O operations, and network usage. You can also view the historical performance data by clicking on the "History" sub-tab. You can select different time periods to analyze the performance information.
4. View Top Activity: Click on the "Top Activity" sub-tab to view the top SQL statements that are consuming CPU, memory, or I/O resources. You can drill down to the SQL statements and view the execution plans to understand how the SQL is being executed.
5. View SQL Plan Baselines: Click on the "SQL Plan Baselines" sub-tab to view the SQL plan baselines for the SQL statements. SQL plan baselines capture the execution plans for the SQL statements that have been optimized by the database optimizer. You can manage the SQL plan baselines and activate the best plans.
6. View Advisors: Click on the "Advisors" sub-tab to view the different advisors available in OEM. Advisors provide recommendations to improve the database performance. For example, the SQL Tuning advisor provides recommendations to improve the performance of SQL statements.
7. Setup Alerts: Click on the "Alerts" sub-tab to set up alerts to be notified when the database performance degrades. You can set up alerts for various performance metrics, such as CPU usage, memory usage, or I/O operations. You can also set up custom thresholds to trigger alerts.
Overall, using OEM, you can manage and monitor the performance of your Oracle database and proactively identify and resolve performance problems.