8.5. Summary

This chapter took an in-depth look at the Automatic Database Management features available in Oracle 10g. You saw how Oracle's Common Manageability Infrastructure (CMI) has delivered on the promise of a self-managing database. By automating many common administrative tasks and simplifying others, you saw how Oracle 10g has freed you to focus on the bigger issues of database management.

We covered statistics collection by first classifying the types of available statistics (optimizer statistics, performance statistics, dictionary statistics, system statistics, and user-defined statistics). You learned that without adequate, up-to-date statistics, the query optimizer often produces sub-optimal execution plans.

You learned that optimizer statistics could be gathered automatically through the automatic statistics collection feature. You saw that simply setting the initialization parameter STATISTICS_LEVEL to TYPICAL (the default setting) or ALL enables this feature. You also learned that monitoring for stale statistics is also handled automatically, so there is no longer a need for the MONITORING clause to be set on tables.

Optimizer statistics can also be collected manually, when needed, through the use of the DBMS_STATS package. You saw how volatile tables offer special challenges to automatic statistics collection, and you learned some options for better handling them. We also discussed the changes to the DBMS_STATS package, including the DEGREE and GRANULARITY arguments.

This chapter also introduced you to the Automatic Workload Repository (AWR). You saw that the AWR is the primary occupant of the SYSAUX tablespace, and that is responsible for the collection of dynamic performance statistics. AWR also provides the repository for many performance-tuning statistics and objects. You learned that this repository offers persistence to dynamic performance statistics, allowing them to survive across instance shutdowns.

You learned about AWR snapshots and baselines and how to create, modify, and drop them. You learned how snapshots, by default, are created hourly through a job in the Oracle Scheduler and are retained for seven days. You saw how baselines could be created by identifying a range of snapshots that represent a workload. These baselines could then be used for comparisons over time. We also examined the new Memory Monitor (MMON) process and its responsibilities for snapshot retention and purging. You were exposed to many of the AWR views, and you learned to produce AWR reports in HTML and plain text.

Next, we introduced the Oracle 10g advisory framework and provided a brief synopsis of the various advisors. You saw how all the advisors utilized a common interface to allow consistency as well as simplified interaction between the advisors. You learned about using the DBMS_ADVISOR package as a common API for all the advisors. You created, adjusted, and executed advisor tasks, and learned to use the advisor views to view the results.

The Automatic Database Diagnostic Monitor (ADDM) provides proactive database performance monitoring with no human interaction required. ADDM is invoked automatically by MMON any time a snapshot is created. When invoked, ADDM identifies performance bottlenecks in the system and offers recommendations to resolve the problem.

And finally, you learned about using the SQL Tuning Advisor to tune individual SQL statements as well as SQL Tuning Sets. You learned that you could create an STS by extracting SQL text and execution statistics from the Cursor Cache, the AWR, or from another STS.