9.1. Proactive Database Maintenance

You can monitor your systems for management and performance problems in essentially two ways: reactively and proactively.

Reactive monitoring involves monitoring a database environment after a performance or management issue has arisen. For example, you start gathering performance statistics using third-party tools, Enterprise Manager, or home-grown scripts after users call to tell you that the system is slow. Obviously, this type of monitoring leaves a lot to be desired, because a problem has already arisen and the users of the system are already impacted. You can use the techniques discussed in this chapter for reactive monitoring, but they are most effective when used to perform proactive monitoring.

Proactive monitoring allows you to identify and respond to common database performance and management issues before, during, or immediately after they occur. Most of the new features in Enterprise Manager (EM) Database Control are geared toward proactive monitoring.

The monitoring tools available in EM Database Control collect their information from a variety of sources (usually the same sources from which your existing third-party tools and home-grown scripts collect their monitoring information): data dictionary views, dynamic performance views, and the operating system. Oracle 10g also makes extensive use of the costbased optimizer statistics for its proactive monitoring. All these sources of information are accessed by the Automatic Workload Repository feature described in the next section.

9.1.1. Automatic Workload Repository

Oracle 10g introduces two new background processes—Memory Monitor (MMON) and Memory Monitor Light (MMNL). These processes work together to collect performance statistics directly from the System Global Area (SGA). The MMON process does most of the work by waking up every 60 minutes and gathering statistical information from the data dictionary views, dynamic performance views, and optimizer and then storing this information in the database. The tables that store these statistics are called the Automatic Workload Repository (AWR). These tables are owned by the user SYSMAN and are stored in the SYSAUX tablespace.

To activate the AWR feature, you must set the PFILE/SPFILE parameter STATISTICS_LEVEL to the appropriate value. The values assigned to this parameter determine the depth of the statistics that the MMON process gathers. Table 9.1 shows the values that can be assigned to the STATISTICS_LEVEL parameter.

Table 9.1. Specifying Statistics Collection Levels

Collection Level	Description
BASIC	Disables the AWR and most other diagnostic monitoring and advisory activities. Few database statistics are gathered at each collection interval when operating the instance in this mode.
TYPICAL	Activates the standard level of collection activity. This is the default value for AWR and is appropriate for most environments.
ALL	Captures all the statistics gathered by the TYPICAL collection level, plus the execution plans and timing information from the operating system.

Once gathered, the statistics are stored in the AWR for a default duration of 7 days. However, you can modify both the frequency of the snapshots and the duration for which they are saved in the AWR. One way to modify these intervals is by using the Oracle-supplied package DBMS_WORKLOAD_REPOSITORY. The following SQL command shows the DBMS_WORKLOAD_ REPOSITORY package being used to change the AWR collection interval to 1 hour and the retention period to 30 days:

SQL> execute dbms_workload_repository.modify_snapshot_settings
  (interval=>60,retention=>43200);

PL/SQL procedure successfully completed.

You can also change the AWR collection interval, retention period, and collection depth using the EM Database Control. Choose Administration Automatic Workload Repository Edit on the main screen to open the Edit Settings screen shown in Figure 9.1.

In Figure 9.1, the retention period for statistics gathered by the MMON process is set to 10 days, and statistics are collected every 15 minutes. You can also modify the depth at which statistics are collected by the AWR by clicking the Collection Level link. Clicking this link opens the Initialization Parameters screen in which you can specify any of the three pre-defined collection levels shown in Table 9.1. Figure 9.2 shows the AWR collection level being changed from TYPICAL to ALL.

Take care when specifying the AWR statistics collection interval. Gathering snapshots too frequently requires additional space in the SYSAUX tablespace and adds additional database overhead each time the statistics are collected.

Once AWR snapshots are taken and stored in the database, the Automatic Database Diagnostic feature uses the statistics as described in the next section.

9.1.2. Automatic Database Diagnostic Monitoring

Following each AWR statistics collection process, the Automated Database Diagnostic Monitoring (ADDM) feature automatically analyzes the gathered statistics and compares them to the statistics gathered by the previous two AWR snapshots. By comparing the current statistics to these two previous snapshots, the ADDM can easily identify potential database problems such as CPU and I/O bottlenecks, resource-intensive SQL or PL/SQL, lock contention, and the utilization of Oracle's memory structures within the SGA.

Based on these findings, the ADDM may recommend possible remedies. The goal of these recommendations is to minimize DB Time. DB Time is composed of two types of time measures for non-idle database users: CPU time and wait time. This information is stored as the cumulative time that all database users have spent either using CPU resources or waiting for access to resources such as CPU, I/O, or Oracle's memory structures. High or increasing values for DB Time indicate that users are requesting increasingly more server resources and may also be experiencing waits for those resources, which can lead to less than optimal performance. In this way, minimizing DB Time is a much better way to measure overall database performance than Oracle's old ratio-based tuning methodologies.

Once ADDM completes its comparison of the newly collected statistics to the previously collected statistics, the results are stored in the AWR. You can use these statistics to establish baselines against which future performance will be compared, and you can use deviations from these baseline measures to identify areas that need attention. In this manner, ADDM allows you to not only better detect and alert yourself to potential management and performance problems in the database, but also allows you to take corrective actions to rectify those problems quickly and with little or no manual intervention.

The following sections introduce the interfaces, features, and functionality of ADDM and explain how you can use this utility to monitor and manage database storage, security, and performance. We'll begin by examining the EM Database Control tools that you can use to view the results of ADDM analysis.

9.1.2.1. Using EM Database Control to View ADDM Analysis

EM Database Control graphically displays the results of the ADDM analysis on several screens, including:

• The Performance Findings link under the Diagnostic Summary section of the EM Database Control main screen

• The Performance tab of the EM Database Control main screen

• The ADDM screen located by clicking the Advisor Central link at the bottom of the EM Database Control main screen

Sample output from each of the EM Database Control screens is shown in the following sections.

9.1.2.1.1. The EM Database Control Performance Findings Link

The EM Database Control main screen contains a section called Diagnostic Summary. One of the links under this section is called Performance Findings. Figure 9.3 shows this section.

The output in Figure 9.3 shows that ADDM discovered 10 performance-related findings. Clicking the link for these 10 performance findings displays the ADDM summary screen, at the bottom of which is displayed the Performance Analysis section, as shown in Figure 9.4.

The output in Figure 9.4 shows that the greatest impact to performance is due to bottlenecks related to disk I/O (described by ADDM as "The throughput of the I/O subsystem was significantly lower than expected"). By clicking this link, you can view ADDM's recommendation for correcting this problem, which is shown in Figure 9.5.

ADDM suggests three options for improving the performance of the I/O on this system:

• Stripe and mirror (also known as SAME) all datafiles across multiple disk drives.

• Increase the number of physical disk drives.

• Consider implementing Oracle's Automatic Storage Management feature.

Figure 9.4 also shows that a large portion of our I/O problems are related to specific database tables or indexes: "Individual database segments responsible for significant physical I/O were found." Clicking this link displays the detailed ADDM findings shown in Figure 9.6.

ADDM has essentially identified the SALES_HISTORY table as the source of excessive I/O and recommends that you run the Segment Advisor utility against this table to generate recommendations for improving its performance. The Segment Advisor is described later in this section.

9.1.2.1.2. The EM Database Control Performance Tab

You can also click the Performance tab on the EM Database Control main screen to view performance data collected by AWR and analyzed by ADDM. ADDM uses its findings to populate the Sessions: Waiting And Working section of the Performance screen, as shown in Figure 9.7.

Using this section of the Performance screen, you can drill down into detailed information in 11 areas that have been identified as having an impact on performance, from User I/O thorough CPU Used. By clicking the User I/O link, you can drill down into detailed information about user I/O, as shown in Figure 9.8.

The graph in Figure 9.8 shows the times at which the snapshots were taken along its X axis. The lines on the graph show which of the events in the graph's legend experienced the most activity during that snapshot period. The graph output indicates that most of the user I/O activity is experiencing waits for the database event "db file scattered read." This event is caused by the I/O activity that occurs when Oracle experiences a wait while performing a sequential disk read of contiguous blocks from a datafile into the buffer cache—usually when a table is being accessed using a full table scan or fast full index scan.

The Performance screen, shown in Figure 9.9, also contains a Performance Overview section near the bottom that summarizes, in pie graphs, the top SQL and top session wait events identified by ADDM.

Clicking the links in the boxes next to either of these graphs displays details about that item. For example, clicking the link for the SQL statement that experienced the most wait time (35 percent on the graph) shows the output in Figure 9.10.

The output in Figure 9.10 shows that ADDM identified the SQL statement SELECT count(*) FROM SALES_HISTORY as experiencing the most waits during processing. Clicking the link at the bottom of this same screen allows you to view the execution plan for this statement.

If the execution plan for this query shows that a full table scan of the 900,000+ row SALES_ HISTORY table is occurring, then you can see how it might experience I/O waits while retrieving its rows. To view the tuning recommendations that ADDM has generated for this statement, click the Run SQL Tuning Advisor button at the bottom of the screen.

9.1.2.1.3. The Advisor Central Screen

The Advisor Central screen also contains ADDM findings. The link for the Advisor Central screen is at the bottom of the EM Database Control main screen. Click this link to display the Advisor Central screen, the top portion of which is shown in Figure 9.11.

Click the ADDM link in the Advisors section of this screen to display a graph, shown in Figure 9.12, that shows all the recent AWR snapshots taken by the MMON process.

As stated earlier, the ADDM automatically compares the most recent AWR snapshot with the last two AWR snapshots when formulating its recommendations. However, you can use this Create ADDM Task screen to manually select any two AWR snapshot times and formulate ADDM recommendations for activity that occurred between those two points in time. To start this process, click the Period Start Time radio button and then select a start date and time by clicking the point in the graph's timeline that corresponds to the beginning period that you want to use. Repeat this process to specify the end process time stamp. Figure 9.13 shows that the start and end time for ADDM analysis have been specified so that they correspond to the two points in time that surround the spike shown in the graph.

Click OK to analyze the database for possible performance problems between the two specified points in time.

The results of this analysis is displayed at the bottom of the ADDM screen that is displayed when the analysis is complete. Figure 9.14 shows an example of the ADDM results for the time interval we chose.

Notice that these findings are similar in nature to the ones displayed by the EM Findings link shown earlier in Figure 9.4. The difference between the two ADDM results is that those in Figure 9.4 are for the last three AWR collection periods as they existed when that page was viewed, whereas the results in Figure 9.14 are for our manually specified time frame. By manually specifying the ADDM analysis period in this way, you can "go back in time" and review previous spikes in performance that may have been missed with real-time monitoring like that shown on the EM Findings link.

Although using EM Database Control to view ADDM results is by far the simplest way to review ADDM recommendations, you can also query the ADDM data dictionary views directly as well. Some of these data dictionary views are discussed in the following section.

9.1.2.2. Using Data Dictionary Views to View ADDM Analysis

You can use more than 20 data dictionary views to examine the results of ADDM's activities. Four commonly used ADDM views that store the recommendation information we saw in the EM Database Control pages are described in Table 9.2.

Table 9.2. ADDM Data Dictionary Views

View Name	Description
DBA_ADVISOR_FINDINGS	Describes the findings identified by the ADDM analysis
DBA_ADVISOR_OBJECTS	Describes the objects that are referenced in the ADDM findings and recommendations
DBA_ADVISOR_RECOMMENDATIONS	Describes the recommendations made based on ADDM findings
DBA_ADVISOR_RATIONALE	Describes the rationale behind each ADDM finding

The following SQL statement shows a sample query on the DBA_ADVISOR_FINDINGS data dictionary view that identifies the type of performance problem that is causing the most impact on the database:

SQL> SELECT task_id, type, message
  2  FROm dba_advisor_findings
  3  WHERE impact= (select MAX(impact) FROM dba_advisor_findings);

TASK_ID TYPE       MESSAGE
------- ---------  ----------------------------------------------------
    164 PROBLEM    SQL statements consuming significant database time
                   were found.

The output from this query shows that SQL statements being executed in the database are contributing to the poor database performance. By itself, the DBA_ADVISOR_FINDINGS data dictionary view does not identify which SQL statements are consuming the database time. Instead, these are shown in the DBA_ADVISOR_OBJECTS data dictionary view and are identified by the TASK_ID value shown in the query on DBA_ADVISOR_FINDINGS. A query on that view, using the TASK_ID of 164 returned by the ADDM session that had the potential for the greatest database impact, returns the SQL statements shown here:

SQL> SELECT attr4
  2  FROM dba_advisor_objects
  3  WHERE task_id = 164;

ATTR4
-----------------------------------------------------------------------
UPDATE customers SET credit_limit=credit_limit*1.15 WHERE cust_id = :B1

DELETE FROM sales WHERE time_id BETWEEN

UPDATE sales_history SET quantity_sold = quantity_sold+10 WHERE
   CHANNEL_ID := B1

SELECT COUNT(*) FROM Sales_history;

SELECT DISTINCT channel_id FROM sales_history;

This query shows all the SQL statements that were captured by the AWR during the snapshot period and that were used in the ADDM analysis for that same period.

The DBA_ADVISOR_ACTIONS data dictionary view shows the ADDM recommendations for each finding. The following query shows the recommendations for correcting the performance issues associated with TASK_ID 164, which was identified earlier as being the costliest database activity:

SQL> SELECT TRIM(attr1) ATTR1, TRIM(attr2) ATTR2, TRIM(attr3) ATTR3
  2  FROM dba_advisor_actions
  3  WHERE task_id = 164;

ATTR1          ATTR2    ATTR3
----------     ------   ----------
log_buffer     262144   15728640
db_cache_size  25165824 50331648
undo_retention 900      363

This output indicates that ADDM recommends that the values for LOG_BUFFER, DB_CACHE_ SIZE, and UNDO_RETENTION all be changed from their current values to 15,728,640 bytes, 50,331,648 bytes, and 363 seconds, respectively.

If you want to see the rationale behind each of the actions shown in DBA_ADVISOR_ACTIONS, query the DBA_ADVISOR_RATIONALE data dictionary view. The DBA_ADVISOR_RATIONALE view stores the ADDM recommendations that ADDM has formulated based on the AWR data like those stored in DBA_ADVISOR_FINDINGS and DBA_ADVISOR_OBJECTS. The following example shows a sample query on the DBA_ADVISOR_RATIONALE view using the TASK_ID of 164 identified earlier:

SQL> SELECT message
  2  FROM dba_advisor_rationale
  3  WHERE task_id = 164;

MESSAGE
-----------------------------------------------------------------------
Buffer cache writes due to small log files were consuming significant

database time.

The buffer cache was undersized causing significant read I/O.

The value of "undo retention" was 900 seconds and the longest running
  query lasted only 330 seconds. This extra retention caused
  unnecessary I/O.

To gain further insight into the recommendations and information gathered by the ADDM, Oracle 10g also provides several advisor utilities in the EM Database Control. These advisors are discussed in the next section.

9.1.3. ADDM Diagnostic Advisors

The ADDM utility also provides several tuning and diagnostic advisors that you can use to examine several common problem areas in your database and then offer suggestions for improving those areas. The diagnostic and tuning advisors include the following:

• SQL Tuning Advisor

• SQL Access Advisor

• Memory Advisor

• Mean Time To Recover Advisor

• Segment Advisor

• Undo Management Advisor

The links to all these advisors are available by clicking the Advisor Central link at the bottom of the EM Database Control main screen. Each of the links listed on the Advisor Central screen is described in the following sections.

9.1.3.1. The SQL Tuning Advisor

As you saw earlier, the ADDM utility allows you to drill down and view the actual SQL of the statements that are contributing to increasing DB Times. Once the SQL has been identified, you can use the SQL Tuning Advisor to attempt to formulate more efficient SQL execution plans for the offending SQL. Figure 9.15 shows the SQL Tuning Advisor Links screen.

There are four options for the SQL Tuning Advisor: Top SQL, SQL Tuning Sets, Snapshots, and Preserved Snapshot Sets. Table 9.3 compares these options.

Table 9.3. Types of Analysis for the SQL Tuning Advisor

Link Name	Description
Top SQL	Allows you to identify and tune the most resource-intensive SQL statements
SQL Tuning Sets	Allows you to group several related SQL statements together for analysis
Snapshots	Allows you to select a specific snapshot to analyze
Preserved Snapshot Sets	Allows you to create and analyze a collection of related snapshots

The following examples use the Top SQL option of the SQL Tuning Advisor. You can view the Top SQL statements in two ways, Spot SQL and Period SQL; each are represented by a tab in the Top SQL screen.

Spot SQL graphically displays all the resource wait, I/O, and CPU statistics for SQL statements that have been active in the most recent five-minute interval. By examining the graphical output, you can readily identify which SQL statements caused spikes in these three areas. Figure 9.16 shows a sample Spot SQL screen.

Period SQL allows you to examine the SQL that occurred within the last 24 hours, between two points in time. By examining the graphical output on this screen, you can also easily identify which periods of time experienced spikes in the areas of resource waits, I/O, and CPU. Figure 9.17 shows a sample Period SQL screen.

Regardless of whether you isolate your problem SQL statements using ADDM, Spot SQL, or Period SQL, the process of analyzing that SQL using the SQL Advisor is the same. First, you need to decide which statement you wish to analyze and then click the Run SQL Tuning Advisor button on the SQL Details page shown in Figure 9.18 after selecting that statement from ADDM, Spot SQL, or Period SQL.

Clicking the Run SQL Tuning Advisor button opens the Schedule Advisor screen. You use this screen to formulate the job that will be submitted to the database when the advisor is actually executed. You specify three elements when running the SQL Tuning Advisor: Description, Scope, and Schedule. Each of these elements is described in Table 9.4.

Table 9.4. Elements for Scheduling a SQL Tuning Advisor Job

Job Element	Description
Description	Description of the SQL statement that is being analyzed. Although this is an optional element for submitting a job, it is useful because otherwise Oracle uses a system-generated name for the SQL statement—85zq7jwf3x3qg in Figure 9.18.
Scope	The depth to which the advisor should examine the statement. Possible values are Limited and Comprehensive. The deeper the analysis, the greater the potential for uncovering additional tuning options. You can specify the maximum time that the advisor should spend performing a comprehensive analysis.
Schedule	Specifies when to execute the analysis job. The default value is Immediately, but you can also schedule the job to execute at some time in the future.

Each of these elements is shown in Figure 9.19, which displays a portion of the Schedule Advisor screen.

After you specify the job submission elements, click OK to begin analyzing the specified statement.

A screen similar to the one shown in Figure 9.20 is displayed once the SQL Tuning Advisor completes its analysis of the specified statement.

In the example in Figure 9.20, the SQL Tuning Advisor is recommending that you add an index to the SALES_HISTORY table. To implement this recommendation, click the Implement button, which will display the Implementation Recommendation screen, as shown in Figure 9.21.

The output in Figure 9.21 shows that the B-Tree index called IDX$_00001 will be created on the CUST_ID column of the SALES_HISTORY table. To review the SQL that will be issued to create this index, click the Show SQL button. If desired, you can modify the generated SQL to rename the index or change its storage parameters. Otherwise, clicking OK submits the job to create the new index. Like the SQL Tuning Advisor, the job can be executed immediately or scheduled to run some time in the future.

Like the SQL identifier, each recommendation is also assigned a systemgenerated ID made up of a combination of 13 numbers and lowercase letters.

You'll notice that the output in Figure 9.20 also recommends that you use the SQL Access Advisor to further improve the physical design of the schema. This utility is discussed in the next section.

9.1.3.2. The SQL Access Advisor

You've seen how you can use the SQL Tuning Advisor to identify and create an index to minimize the DB Time for a particular statement. The SQL Access Advisor provides additional support for finding potential schema modifications that you can use to reduce the amount of I/O, CPU, and wait time for a given SQL statement. Figure 9.22 shows the SQL Access Advisor main screen.

The SQL Access Advisor shows four ways in which you can select the SQL statement to be analyzed. Table 9.5 compares these four techniques.

Table 9.5. Comparison of Four Techniques for Selecting SQL to Analyze

Collection Method	Description
Current And Recent SQL Activity	Lets you select the SQL to analyze from what is currently in the SGA
Import Workload From SQL Repository	Lets you analyze a SQL tuning set that was created using the SQL Tuning Advisor
User-Defined Workload; Import SQL From A Table	Lets you perform a tuning analysis on a workload that is not currently running in the database
Create A Hypothetical Workload From The Following Schemas And Tables	Lets you specify a schema against which to generate SQL tuning recommendations

Select the Current And Recent SQL Activity radio button and click Next to display the Recommendation Options screen shown in Figure 9.23.

The Advisor can focus its efforts on three options.

• To assess whether the SQL statements will benefit from the addition of an index

• To assess whether the SQL statements will benefit from the addition of a materialized view

• To check for both indexes and materialized views

You can specify the depth level of the analysis as either Limited or Comprehensive.

Because Comprehensive tuning analysis can consume a lot of server resources, schedule it when user activity against the database is at its lowest.

Clicking Next after selecting the analysis options displays the scheduling options. Like previous EM Database Control scheduling screens, the time that the analysis will begin, its duration, and its frequency can all be defined on this page.

Finally, clicking Next displays the SQL Access Advisor: Review screen, as shown in Figure 9.24. This screen displays all the options that you specified before clicking Submit to actually begin the analysis.

When the analysis is complete, you return to the Advisor Central screen and a link to the results of the analysis is displayed at the bottom of the screen, as shown in Figure 9.25.

Click the link in the completed analysis table to display the recommendations that the SQL Access Advisor created for SQL statements that were analyzed. Figure 9.26 shows a Recommendations summary screen; the SQL Access Advisor proposed a single recommendation.

Clicking Recommendation ID (1) shown in Figure 9.26 displays the details of the SQL Access Advisor recommendation, as displayed in Figure 9.27.

This recommendation indicates that building an index will improve the access path of the SQL statement shown at the bottom of the screen. You can change the default index name of _IDX$$_ 02930001 suggested by the advisor by typing a new name in its place. Additionally, you can also specify the tablespace where the new index should be stored. Clicking OK returns you to the recommendation summary screen shown in Figure 9.26. Click the Schedule Implementation button to schedule the actual creation of the index.

9.1.3.3. The Memory Advisor

Both the SQL Tuning and Access advisors focus on identifying and tuning the SQL that is having the greatest impact on increasing overall DB Time. Alternatively, you can use the Memory Advisor to gather more global tuning recommendations about all aspects of Oracle's memory structures, including the SGA and user memory structures. You can also access the Memory Advisor from the Advisor Central screen. Figure 9.28 shows the Memory Advisor main screen.

The sizes of four components of the SGA—the Shared Pool, Buffer Cache, Large Pool, and Java Pool—are summarized in tabular and in graphical form. Clicking the Advice buttons next to the Shared Pool and Buffer Cache values tells the Memory Advisor to formulate tuning recommendations for that memory structure. Figure 9.29 shows the Memory Advisor's advice for the Buffer Cache portion of the SGA.

This output shows that as the size of the buffer cache is increased from its current size of 24MB to the recommended size of 48MB, the overall physical reads are reduced by 20 percent (a relative decline from 1.0 to 0.80). This would be beneficial because reads from memory are thousands of times faster than reads from disk.

If you enable Automatic Shared Memory Management (Figure 9.29 shows that it is disabled for this instance), the ADDM can automatically adjust the size of the Buffer Cache based on its findings.

The main Memory Advisor screen also has a second tab that shows the results of the advisor's analysis of the PGA (Program Global Area) memory that is allocated to each user process. Figure 9.30 shows the PGA tab in the Memory Advisor screen.

Like the SGA screen, the PGA recommendations screen also has an Advice button; click it to view the Memory Advisor's recommendations for improving PGA performance.

The output in Figure 9.31 indicates that the Memory Advisor estimates that if the limit for the aggregate amount of space used for all PGAs were increased from 24MB to 150MB, the resulting cache hit ratios would improve from approximately 75 percent to nearly 100 percent.

Using cache hit ratios as the primary basis for performance analysis is not recommended. In several situations, high cache hit ratios can exist even when overall performance is poor.

9.1.3.4. The Mean Time To Recover (MTTR) Advisor

The preceding advisors focused primarily on improving database performance by minimizing user waits for I/O, CPU, and other resources with the goal of minimizing each user's overall DB Time. The Mean Time To Recover (MTTR) Advisor is not concerned with minimizing DB Time, but instead tries to formulate recommendations that minimize the time it takes to perform instance recovery in the case of instance failure. Instance failure can occur when the host server crashes, when any critical SGA background process fails, or if the instance is shut down using the ABORT option. Instance recovery occurs automatically on the first startup following the instance failure. During instance recovery, Oracle uses the undo segments and online redo logs to roll back any uncommitted transactions that were "in flight" when the instance crashed to ensure that all committed transactions are written to disk. As a DBA, you often try to minimize the time it takes to perform this instance recovery so that the database can be brought up quickly.

In previous Oracle releases, you could use the FAST_START_MTTR_TARGET initialization parameter to specify the maximum allowable instance recovery time (in seconds). This parameter must be set to a non-zero value for the new features described next to work.

The MTTR Advisor analyzes the database during regular processing and makes recommendations about database and instance parameters that can be modified in order to meet your instance recovery goals. Figure 9.32 shows a portion of the MTTR Advisor recommendation screen.

The output in Figure 9.32 shows that the MTTR Advisor estimates that the current MTTR is 33 seconds. If this MTTR is not acceptable because a Service Level Agreement (SLA) requires a 15-second instance recovery time, then you can specify a new MTTR value of 15 in the Desired Mean Time To Recover box.

9.1.3.5. The Segment Advisor

You use the Segment Advisor to identify segments that might benefit from a shrink operation. Segments that can be shrunk are those that the Segment Advisor has found to be needing less space than they are currently allocated. By shrinking or compressing these segments, space is returned to the database for use by other objects, and the total number of I/Os needed to access these objects is reduced, potentially improving the performance of SQL statements that access these objects. Figure 9.33 shows the Segment Advisor main screen.

You can analyze potentially compressible segments either at the segment level or at the tablespace level. In addition, you can also specify the degree to which the segments are examined at two levels: Limited and Comprehensive. If you select the Schema Objects and Limited options and then click Continue, the Segment Advisor: Schema Objects screen in Figure 9.34 is displayed.

Initially, no segments are listed for analysis, but click the Add button to specify which segments you want the Segment Advisor to examine. Figure 9.35 shows the screen that is displayed after you select the SUPPLEMENTARY_DEMOGRAPHICS table for segment analysis.

On the screen in Figure 9.35, you can specify how much time the Segment Advisor can take when analyzing the specified segments and how long to store the results of the analysis in the repository. Click Next on this and subsequent screens to display the familiar job scheduling and submissions screens. Once the analysis of the selected segments or tablespaces is complete, the results are displayed at the bottom of the Advisor Central screen, along with all other submitted job results. Click the Segment Advisor Job link to display the Segment Advisor Task screen, which is shown in Figure 9.36.

The output on this screen shows that the SUPPLEMENTARY_DEMOGRAPHICS table owned by the user SH has been identified as a segment that will benefit from a shrink operation, reducing its allocated space from 4MB to the 1.193MB that the table actually needs to store its data. When shrinking an object identified by the Segment Advisor, there are two shrink options: Compact Segments and Compact Segments And Release Space.

The Compact Segments option compacts the rows in the SUPPLEMENTARY_DEMOGRAPHICS table, but does not release the newly freed space back to the tablespace. This option allows you to put off the more resource-intensive operation of actually releasing the space until later.

The Compact Segments And Release Space option compacts the space in the SUPPLEMENTARY_DEMOGRAPHICS table and also releases the unused space back to the tablespace at the same time. Choosing either of these two compression options displays the familiar job submission screen, which submits the compaction job in the background.

In order for the Segment Advisor to modify segments effectively, you need to enable the ROW MOVEMENT attribute of the affected segments. You can do so using the Options tab in the Edit Table screen or the ALTER TABLE ... ENABLE ROW MOVEMENT command.

9.1.3.6. The Undo Management Advisor

The Undo Management Advisor helps you monitor and proactively respond to potential problems in a common trouble area of any transactional database system: undo segments. When a user starts a DML (Data Manipulation Language) transaction, the before-image of the changed data is buffered in the Database Buffer Cache in the SGA. Copies of these buffers are also written to an undo segment, which is stored in the database's undo tablespace. The before-image data stored in the undo segment is used for three important purposes:

• It can be used to restore the original state of the data if the user performing the DML command issues a ROLLBACK command.

• It provides a read-consistent view of the changed data to other users who access the same data prior to the DML user issuing a COMMIT command.

• It is used during instance recovery to undo uncommitted transactions that were in progress just prior to an instance failure.

Once a transaction is assigned to an undo segment, the transaction never switches to a different undo segment, even if the original undo segment was not the most appropriate choice. Because of this, undo segment tuning can be one of the most elusive aspects of database administration. Even when undo activity has reached a steady state and no problems are apparent, the right combination of transactions can cause an undo segment error. This can lead to frustrating undo segment–tuning problems that never completely go away. The goals of undo segment tuning usually ensure the following:

• That database users always find an undo segment to store their transaction before-images without experiencing a wait

• That database users always get the read-consistent view that they need to complete their transactions

• That the database undo segments do not cause unnecessary I/O

The most common undo segment–related error message is ORA-01555: Snapshot Too Old. This error can occasionally occur when some users are running long queries and others are simultaneously modifying the data being queried. This scenario can cause the session running the long-running query to be unable to build a read-consistent view of the database, thus causing the ORA-01555 error message.

The Undo Management Advisor helps prevent undo-related problems in the database by monitoring and analyzing undo activity before making recommendations for improving undo performance.

To open the Undo Management Advisor, which is shown in Figure 9.37, click the Undo Management link in the Advisor Central screen in EM Database Control.

The output in Figure 9.37 summarizes the current configuration of the undo management in the database. For example, it shows that the database is currently configured to retain undo information for up to 15 minutes past the time that the transaction that generated the undo was committed or rolled back. It also shows that the current undo tablespace is called UNDOTBS1, is 200MB in size, and does not have the AUTOEXTEND option turned on for its datafiles. It also indicates that the current undo tablespace is too small to support the requested undo retention period of 16 minutes. To meet this undo retention period, the Undo Management Advisor recommends increasing the size of the undo tablespace to 703MB. For details, click the Undo Advisor button to display the screen shown in Figure 9.38.

This output graphically shows how the undo retention time (in minutes along the bottom of the graph) increases to 16 minutes if the size of the undo tablespace increases to 431MB. The graph also shows that the best possible undo retention time that can be obtained with the current undo tablespace size of 200MB is 6 minutes. Implementing these recommendations will minimize database management and performance problems related to undo segments.

9.1.4. ADDM Alerts

In addition to monitoring and making recommendations on SQL, memory, mean time to recover, segments, and undo activity, ADDM can also be used to proactively monitor the database for other types of problems related to memory, I/O, and CPU utilization, as well as security and space management. To do so, you use ADDM alerts.

ADDM alerts are also an integral part of the ADDM architecture. They notify you when a management or performance issue occurs and begin taking corrective actions—if you configured such actions. By default, the alert notifications are sent to the EM Database Control main screen, as shown in Figure 9.39.

You can also configure alerts so that they are sent to you via e-mail. To do so, click the Setup link at the top of the EM Database Control screen to display the Setup screen shown in Figure 9.40.

Click the Notification Methods link on the left to open the Notification Methods screen, which is shown in Figure 9.41. As shown in Figure 9.41, you'll need to supply three pieces of information:

• The IP address of your network's SMTP mail server

• The name of the user from whom the e-mail address will be sent

• The e-mail address of the user sending the notification e-mails

The example in Figure 9.41 uses the server smtp.acme.com as the SMTP mail server. This is the server through which EM Database Control will send the ADDM alert e-mails. The name of the user from whom the e-mail address will be sent is shown as OEM. The e-mail address of the user who will be sending the notification e-mails appears in the From box when you receive an alert notification via e-mail. The example in Figure 9.41 shows that the notifications will be using the [email protected] user account. After you add the e-mail configuration entries, click Apply to save them.

Click the Test Mail Servers button on the Notification Methods screen to confirm that the configuration you've entered is correct.

Once EM Database Control knows how to send notification e-mails when ADDM alert events occur, you need to tell EM Database Control to whom the notification e-mails should be sent. For the user SYS, click the Preferences link at the top of the EM Database Control main screen. To configure e-mail notification information for other users, click the Edit button on the Administrators screen, shown earlier in Figure 9.40.

You can then enter the e-mail address of the DBA who should receive the notification in the box under the E-mail Address column before clicking Apply to save the change. Click the Test button to send a test e-mail message to the address supplied and confirm the e-mail connectivity between EM Database Control and the DBA's e-mail address.

After you configure the notification methods, alerts are sent to both the EM Database Control main screen and to the e-mail address specified.

An alert is triggered whenever a monitored event occurs or when a specified database threshold, called a metric, is surpassed. Metrics are the statistical performance measurements that are collected and stored in the AWR repository. The ADDM utility then gathers additional database statistics and compares them against the baseline metrics in order to monitor, diagnose, and remedy management problems or poor database performance. There are four default ADDM alerts configured in each database as described in Table 9.6.

Table 9.6. Default ADDM Alerts

Alert	Description
Tablespace Space Usage	Alerts you whenever a tablespace's free space falls below 15 percent and again when it falls below 3 percent.
Snapshot Too Old	Alerts you whenever the ORA-01555 error message (described earlier) occurs.
Recovery Area Low On Free Space	Alerts you when the Flash Recovery area is low on free space. See Chapter 10 for details on using the Flash Recovery area.
Resumable Session Suspended	Alerts you whenever an operation that can be resumed goes into a suspended state.

Oracle 10g has several additional predefined ADDM alerts, which require a small amount of additional configuration before using. These alerts are defined on the Manage Metrics screen of the EM Database Control. To open this screen, click the Manage Metrics link at the bottom of the EM Database Control main screen. Figure 9.42 shows a portion of the predefined alerts found on the Manage Metrics screen.

Some of the alerts shown in Figure 9.42 include the following:

• The archive destination is more than 80 percent full.

• The archive process is hung and returns an error message.

• The superuser SYS is connecting to the database.

Figure 9.42 also shows that each alert can have two levels of severity: Warning and Critical. These two alert levels allow you to achieve greater granularity. For example, you might want two thresholds set up with regard to the archive destination. One might be a warning threshold that triggers an alert when the archive destination is 80 percent full—causing a message to be displayed on the EM Database Control main screen. In addition, you might want to set up a critical threshold so that you receive an e-mail whenever the archive destination device is 90 percent full. In this manner, you can escalate a potential problem from an EM Database Control console message to an e-mail alert as the problem gets worse.

You can also use warning and critical alerts to distinguish between lower severity problems, such as statistics indicating temporary poor performance, and higher severity problems, such as ORA-0600 error messages in the database Alert log. You can achieve this by defining only warning thresholds for lower severity alerts and defining warning and critical alerts for higher severity problems.

Instead of specifying a set value, such as 50 percent and 75 percent for CPU utilization alert levels, you can also raise alerts when CPU utilization exceeds a baseline metric. Baseline metrics are gathered during a period of processing that represents normal database activity. Using these baselines, you can raise alerts when relative performance problems occur. For example, rather than raising an alert when the CPU utilization is 50 percent of the available CPU cycles, you can raise an alert when CPU utilization is 50 percent more than the baseline CPU utilization—which itself could be 85 percent of CPU cycles.

To gather baseline metrics, use the Options link on the Metric Baselines tab in the Manage Metrics screen, which is shown in Figure 9.43.

The output in Figure 9.43 shows that no metric baselines have yet been gathered. The AWR can store several baselines, any of which can be used as the basis for the alert system. To gather the first baseline metric, click Create to display the Create Metric Baseline screen, which is shown in Figure 9.44.

Using the Create Metric Baseline screen, you can minimally assign a name and date to the baseline statistics that will be gathered. Optionally, you can also assign a time of day, warning, and alert thresholds. In the example in Figure 9.44, warning alerts have been configured at 85 percent, and critical alerts have been configured at 95 percent.

If no value is supplied for Hour Of Day, baseline statistics are gathered for the entire 24-hour period for the date specified.

Click Go to capture the current database metrics and then display them at the bottom of the Create Metric Baseline screen, as shown in Figure 9.45.

Using this screen, you can deselect any metrics that you don't want to include in the baseline (all metrics are included by default) or modify the baseline values that were used. Once you tailored these metrics to your needs, you can store them by clicking OK at the top of the page, creating the baseline metric called Initial Server Stats, as shown in Figure 9.46.

The output in Figure 9.46 shows the Initial Server Stats baseline metrics. Clicking this link displays the metrics associated with this baseline. Figure 9.47 shows a sample of the baseline metrics that were established.

The first column shows the name of the metric being monitored, the second column displays the warning values, and the third column displays the critical values that were calculated for that threshold. For example, the baseline warning threshold for the metric Current Open Cursors Count is 784, and the critical threshold is 826.

How ADDM Computes Thresholds When baseline metrics are gathered, the AWR stores the high value and the low value for each metric. The technique that ADDM uses to compute a baseline threshold depends on the comparison operator that is used for specifying the threshold, and on the associated high and low value. If the metric comparison operator is a greater than (>), the warning threshold for the metric is computed as Metric High Value * (1 + Warning Percentage/100). For example, if the high value for the metric Current Open Cursors Count is 500, the 85 percent warning threshold from Figure 9.44 is 500 * (1 + 85/100) or 925, raising a warning alert whenever the Current Open Cursors Count exceeds 925. Likewise, the critical threshold is 500 * (1 + 95/100) or 975, raising a critical alert whenever the Current Open Cursors Count exceeds 975. If the metric comparison operator is a less than (<), the warning threshold for the metric is computed as Metric Low Value * (1 – Warning Percentage/100). For example, if the low value for the metric Large Pool Free % is 300MB, the 85 percent warning threshold is 300 * (1 – 85/100) or 45MB, raising a warning alert whenever the percentage of free space in the Large Pool falls below 45MB. Likewise, the critical threshold is 300 * (1 – 95/100) or 15MB, raising a critical alert whenever the free space in the Large Pool falls below 15MB.

Because more than one baseline can be stored in the AWR at one time, EM Database Control gives you a way to choose the baseline that you want to use for alert thresholds. Suppose you have the two baselines shown in Figure 9.48.

To use the metrics associated with the Overnight Processing baseline as the basis for ADDM alerts, simply select that option and click the Copy Thresholds From Metric Baseline button. EM Database Control gives you an opportunity to modify selected metric parameters if needed, before you update the threshold values and see the screen in Figure 9.49.