Chapter 3: Tuning Your Database for Availability

---

When we talk about high availability, we must take into account more than just the basic question of 'is the database up?' Much more than just that simple question is at stake. What really matters is the perceptions of the users. If the database is up but responsiveness of the database is poor, then from the end user perspective, uptime is affected. This is not a revelation to anyone, but it bears repeating, as this is the premise of this chapter, and the reason for its existence within the covers of a high-availability book. Oracle Database 10g has added numerous tuning enhancements, as well as manageability enhancements that will make it easier to detect bottlenecks in the database, and to alleviate those bottlenecks, sometimes automatically.

In this chapter, we will discuss some of these key features, including a better understanding of what they are, and how to set them up to enable the HA DBA to take full advantage of them. Since manageability is a key component of this as well, we will also base many of our examples on the Oracle Database 10g Enterprise Manager. This latest version of EM is web-based, and is greatly enhanced to make identifying and fixing bottlenecks easier and more intuitive than ever before.

Intelligent Infrastructure

Oracle Database 10g has made great strides in utilizing the information that the database knows about its own inner workings, and turning that knowledge into an infrastructure that will allow the HA DBA to easily and automatically take advantage of that information. This intelligent infrastructure, as termed by Oracle, begins with the Automatic Workload Repository. The Workload Repository gathers information on activity in the database at regular intervals. Each run of the Workload Repository is followed by an automatic run of The Automatic Database Diagnostic Monitor (ADDM), which interprets the information supplied by AWR and makes recommendations. One can dig deeper into these recommendations by then manually running one of several advisors that Oracle has provided. In addition, an active session history, or ASH, is maintained to track all events that sessions are waiting for, to allow real-time information gathering. Altogether this is referred to as the intelligent infrastructure. Combined, these tools give the HA DBA more immediate access than ever before into the inner workings of the Oracle RDBMS.

MMON Background Process

The MMON background process is a new background process introduced in Oracle Database 10g, responsible for the automatic monitoring that goes on within the database. MMON is responsible for kicking off the Automatic Database Diagnostic Monitor at the end of each AWR run, to check for bottlenecks or problems identified within the report. In addition, MMON monitors the ALERT_QUE, a queue owned by sys, once a minute, for any new information/alerts. MMON can be used to either automatically take a corrective action or to send an alert via the ALERT_QUE. These alerts are then displayed on the database home page in Enterprise Manager. Additional subscribers (such as third-party or home-grown tools) can be added to the ALERT_QUE using the DBMS_AQADM.ADD_SUBSCRIBER procedure, allowing alerts to be sent to multiple locations. If the ALERT_QUE is unavailable for some reason, alerts will go to the alert.log for the instance.

---