9.3. Summary

Oracle 10g provides many tools for proactively identifying and fixing potential performance and management problems in the database. At the core of the monitoring system is the Automatic Workload Repository (AWR), which uses the MMON background process to gather statistics from the SGA and store them in a collection of tables owned by the user SYSMAN.

Following each AWR statistics collection interval, the Automatic Database Diagnostic Monitoring (ADDM) feature examines the newly gathered statistics and compares them with the two previous AWR statistics to establish baselines in an attempt to identify poorly performing components of the database. The ADDM then summarizes these findings on the EM Database Control main and Performance screens. Using these screens, you can identify and examine the SQL statements that are contributing the most to DB Time. You can further explore the opportunities for improving the performance or manageability of your database using the EM Database Control advisors, which include the SQL Tuning Advisor, SQL Access Advisor, Memory Advisor, Mean Time To Recover Advisor, Segment Advisor, and Undo Management Advisor.

Using the SQL Tuning Advisor, you can identify the SQL statements that have had the greatest performance impact on the database. You can then examine these statements using the SQL Access Advisor to determine if adjustments can be made to improve the execution paths for these statements and therefore minimize their impact on total DB Time.

The Memory Advisor suggests changes that can potentially improve Oracle's use of memory within the SGA and PGA.

The Mean Time To Recover Advisor helps you determine if your database is properly configured to meet service-level agreements for instance recovery in the event of a server failure or an instance crash.

The Segment Advisor helps you determine which segments are using excess storage space and which might benefit from a shrink operation. Shrinking these segments not only frees storage space for use by other segments, but also minimizes the number of physical I/Os required to access the segments.

Using the Undo Management Advisor, you can monitor and manage undo segments to minimize the likelihood of ORA-01555, Snapshot Too Old error messages, and improve the application's overall read consistency.

You can also configure ADDM alerts to notify you via the EM Database Control or e-mail whenever the performance of the database varies from established baselines or target levels. Available storage space, excessive wait times, and high I/O activity are all examples of events that you can monitor using alerts.

In addition to EM Database Control, you can find indicators of database performance in the database Alert log, user and background trace files, data dictionary views, and dynamic performance views. Some data dictionary views do not contain accurate information about the segments in the database until after statistics are collected on those objects. Therefore, you can automatically collect segment statistics through the use of EM Database Control jobs.

Invalid and unusable database objects also have a negative impact on performance and manageability. You can monitor and repair invalid and unusable objects using the data dictionary and the EM Database Control Administration screen.

EM Database Control summarizes several important performance metrics on the EM Database Control main screen. These metrics include performance statistics for the host server, user sessions, and instance throughput.