Chapter 8. Understanding Automatic Database Management
ORACLE DATABASE 10G: ADMINISTRATION II EXAM OBJECTIVES COVERED IN THIS CHAPTER:
Adjust thresholds for tracked metrics.
Automatic Database Management
Use the Database Advisors to gather information about your database.
Use the SQL Tuning Advisor to improve database performance.
Use automatic undo retention tuning.
NOTE
Exam objectives are subject to change at any time without prior notice and at Oracle's sole discretion. Please visit Oracle's Training and Certification website (http://www.oracle.com/education/certification/) for the most current exam objectives listing.
With the release of Oracle Database 10g (Oracle 10g), Oracle has introduced the Common Manageability Infrastructure (CMI) and taken a big step forward in fulfilling their promise of a self-managing and self-tuning database. Composed of the Automatic Workload Repository (AWR), server-generated alerts, automated routine maintenance features, and the advisory framework, the components of the CMI simplify database administration through automation and intelligent design.
The AWR collects and processes performance statistics and metrics to be used for problem detection and self-tuning purposes. It also acts as the central storage repository for all elements of the CMI.
Server-generated alerts proactively monitor database events and metric levels. When metric threshold levels are exceeded, an alert is generated. Server-generated alerts can also send notification via e-mail or pager, execute operating system programs, execute PL/SQL scripts, or generate SNMP traps in response to alert situations.
The automated routine maintenance tasks handle many time-consuming tasks automatically. The automatic statistics collection and automatic DML monitoring features work together to collect and maintain up-to-date optimizer statistics. The Automatic Database Diagnostic Monitor (ADDM) analyzes performance statistics to identify and resolve system bottlenecks. Automatic SQL tuning optimizes SQL statement performance, and automatic undo retention management ensures adequate undo space is always available.
Lastly, the advisory framework provides analysis and recommendations on a number of key administrative areas. The advisors share a common interface to simplify usage and to allow ease of interaction between advisors. The SQL Tuning Advisor, for example, provides an interface to the automatic SQL tuning functionality. It offers analysis and recommendations for optimizing SQL statements to improve performance.
Together, these four components instantiate the CMI and simplify the tasks associated with Oracle database administration.
This chapter discusses each of these components of the CMI. You'll see how the AWR collects and manages performance statistics and metrics and provides a centralized repository for the CMI. You'll study server-generated alerts and learn to assign threshold levels to specific metrics.
This chapter also covers the automated routine maintenance functions, with an emphasis on automatic statistics collection and ADDM. Finally, you'll get an overview of the advisory framework, see how the common interface simplifies advisor usage, and walk through the SQL Tuning Advisor to demonstrate advisor usage. Through it all, we hope to show you how the CMI components interact, communicate, and leverage each other to support the self-managing and self-tuning concept.