Query Optimizer  «Prev  Next»

Lesson 8Using Oracle Enterprise Manager
ObjectiveUse Oracle Enterprise Manager to monitor Database Activity

Using Oracle Enterprise Manager to monitor Database Activity

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.
The next lesson is the module wrap-up.

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.