10 Monitoring and Tuning the Database

Alerts

Alerts help you monitor your database. Most alerts notify you of when particular metric thresholds are crossed. For each alert, you can set critical and warning threshold values. These threshold values are meant to be boundary values that when crossed indicate that the system is in an undesirable state. For example, when a tablespace becomes 97 percent full this can be considered undesirable, and Oracle will generate a critical alert.

Other alerts correspond to database events such as Snapshot Too Old or Resumable Session suspended. These types of alerts indicate that the event has occurred.

In addition to notification, you can set alerts to perform some action such as running a script. For instance, scripts that shrink tablespace objects can be useful for a Tablespace Usage warning alert.

By default, Oracle enables the following alerts:

• Table Space Usage (warning at 85 percent full, critical at 97 percent full)

• Snapshot Too Old

• Recovery Area Low on Free Space

• Resumable Session Suspended

You can modify these alerts or enable others by setting their metrics.

For more information, see "Managing Alerts".

Performance Self-Diagnostics: Automatic Database Diagnostics Monitor

Oracle Database includes a self-diagnostic engine called the Automatic Database Diagnostic Monitor (ADDM). ADDM makes it possible for the Oracle Database to diagnose its own performance and determine how any identified problems can be resolved.To facilitate automatic performance diagnosis using ADDM, Oracle Database periodically collects snapshots of the database state and workload. The default collection interval is one hour. Snapshots provide a statistical summary of the state of the system at any point in time. These snapshots are stored in the Automatic Workload Repository (AWR), residing in the SYSAUX tablespace. The snapshots are stored in this repository for a set time (a week by default) before they are purged to make room for new snapshots.ADDM analyses data captured in AWR to determine the major problems in the system and in many cases recommends solutions and quantifies expected benefits.

Generally, the performance problems ADDM can flag include the following:

• Resource bottlenecks, such as when your database is using large amounts of CPU time or memory, for example as a result of high load SQL statements

• Poor connection management, such as when your application is making too many logons to the database

• Lock contention in a multiuser environment, when a lock to update data causes other sessions to wait, slowing down the database

Monitoring General Database State and Workload

The Enterprise Manager home page enables you to monitor the health of your database. It provides a central place for general database state information and is updated periodically. This page reports information that is helpful for monitoring database state and workload. See Figure 10-1, "Enterprise Manager Home Page".

Figure 10-1 Enterprise Manager Home Page

Description of "Figure 10-1 Enterprise Manager Home Page"

The General section provides a quick view of the database, such as whether the database is Up or Down, the time the database was last started, instance name, and host name.

The Host CPU section shows the percentage of CPU time used in the overall system. This chart breaks down CPU percentage into time used by the database and time used by other processes. If your database is taking up most of the CPU time, you can explore the cause further by looking at the Active Sessions summary. This summary tells you what the database processes are doing, such as which ones are using CPU, or waiting on I/O. You can drill down for more information by clicking a link, such as CPU.

If other processes are taking up most of your CPU time, this indicates that some other application running on the database machine may be causing the performance problems. To investigate this further, click the Host link under the General section. This link takes you to the machine overview page where you can see some general information about the machine, such as what operating system it is running, how long the machine has been up, and any potential problems.

Click the Performance property page to see a summary of CPU utilization, average active sessions, instance disk I/O, and instance throughput. Additional Monitoring Links enable you to drill down to Top Activity and other data. The type of actions you can take to improve host performance depends on your system, but can include eliminating unnecessary processes, adding memory, or adding CPUs.

On the Home page, the Diagnostic Summary summarizes the latest ADDM performance findings. This section also summarizes any critical or warning alerts listed in the Alerts section.

The Alerts table provides information about any alerts that have been issued along with the severity rating of each. An alert is a notification that a metric threshold has been crossed. For example, an alert can be triggered when a tablespace is running out of space.

When an alert is triggered, the name of the metric causing it is displayed in the Name column. The severity icon (Warning or Critical) is displayed, along with time of alert, and alert value. You can click the message to learn more about the cause. For more information, see "Alerts" .

The Performance Analysis section provides a quick summary of the latest ADDM findings, highlighting the issues that are causing the most significant performance impact. This analysis can identify problems such as SQL statements that are consuming significant database time. See Figure 10-2, "Performance Analysis on Home Page".

Figure 10-2 Performance Analysis on Home Page

Description of "Figure 10-2 Performance Analysis on Home Page"

For more information, see "Performance Self-Diagnostics: Automatic Database Diagnostics Monitor".

Managing Alerts

The following sections describe how to manage alerts.

Viewing Metrics and Thresholds

Metrics are a set of statistics for certain system attributes as defined by Oracle. They are computed and stored by the Automatic Workload Repository, and are displayed on the All Metrics page, which is viewable by clicking All Metrics under Related Links on the Database Home page.

Figure 10-3 shows a portion of the All Metrics page, displaying some of the metrics that AWR computes. When you click a specific metric link, a detail page appears, with more information about the metric. Online Help for this page gives you a description of the metric.

Figure 10-3 All Metrics Page

Description of "Figure 10-3 All Metrics Page"

For each of these metrics, you are able to define warning and critical threshold values, and whenever the threshold is crossed, Oracle issues an alert.

Alerts are displayed on the Database Home page under the Alerts heading (or Related Alerts for non-database alerts such as a component of Oracle Net). Figure 10-4 shows five alerts including two Tablespace Full warnings.

Figure 10-4 Alerts Section of Enterprise Manager Home Page

Description of "Figure 10-4 Alerts Section of Enterprise Manager Home Page"

Setting thresholds is discussed in "Setting Metric Thresholds". Actions you might take to respond to alerts are discussed in "Responding to Alerts".

When the condition that triggered the alert is resolved and the metric's value is no longer outside the boundary, Oracle clears the alert. Metrics are important for measuring the health of the database and serve as input for self-tuning and recommendations made by Oracle advisors.

Host Name=mydb.us.mycompany.com
Metric=Response Time per Call
Timestamp=08-NOV-2003 10:10:01 (GMT -7:00)
Severity=Critical
Message=Response time per call has exceeded the threshold. See the
lattest ADDM analysis.
Rule Name=
Rule Owner=SYSMAN

Viewing Performance Analysis

ADDM runs automatically every hour to coincide with the snapshots taken by Automatic Workload Repository (AWR). Its output consists of a description of each problem it has identified, and a recommended action.

Findings are displayed in two places on the home page:

• Under the Performance Analysis section on the Database Home page, as shown in Figure 10-6.

  You can drill down by clicking the finding. The Performance Findings Details page appears, describing the findings and recommended actions.

• The Diagnostic Summary next to Performance Findings shows the number of findings, if any. Click this link to go to the ADDM page.

Figure 10-6 Performance Analysis

Description of "Figure 10-6 Performance Analysis"

Responding to Performance Findings

To respond to a performance finding, click the finding and follow the recommended actions, if any. A recommendation can include running an advisor.

For example, Figure 10-6 shows a performance finding of SQL statements consuming significant database time were found, with an impact of 100 percent and recommendation of SQL Tuning.

Clicking the finding link takes you to the Performance Finding Details page. Here the recommended action is to run the SQL Advisor, which you can do by clicking Run Advisor Now. The advisor runs and gives a recommendation in the form of precise actions for tuning the SQL statements for better performance.

Modifying Default ADDM Behavior

ADDM behavior and analysis is based on the Automatic Workload Repository (AWR), which collects system performance statistics and stores the data in the database. After default installation, the AWR captures data every hour in the form of a snapshot and purges data over seven days old. You can modify both the snapshot frequency and the data retention period as desired.

You can view and alter these settings on the Workload Repository page:

• The retention period for snapshots. This is initially set to 7 days. AWR automatically purges any snapshot older than the retention period, with the exception preserved snapshots that are retained for ever. A preserved snapshot set is typically created to define a reference period for performance analysis.

• The interval for snapshots. The default and recommended value is one hour.

To navigate to this page, from the Database Administration page, under Statistics Management, select Automatic Workload Repository.

To change either of these settings, click Edit on the Workload Repository page. The Edit Settings page appears. Enter a new Snapshot retention period or new System Snapshot Interval. Click OK.

Running ADDM Manually

By default Oracle runs ADDM every hour. Performance findings, if any, from the last snapshot are listed on the Oracle Enterprise Manager Home page. This is described in "Performance Self-Diagnostics: Automatic Database Diagnostics Monitor" .

You can also run ADDM manually. Reasons for doing so include running it as a recommended action associated with an alert, running it in the middle of a snapshot period, or running it across multiple snapshots.

From the Home page, under Related links you can navigate to the ADDM page by clicking Advisor Central, then ADDM. The Run ADDM page appears.

Figure 10-7 is a screen shot of Run ADDM page.

Increased session activity is displayed as peaks in the graph.

To analyze a period across multiple snapshots:

1. Select Run ADDM to analyses past instance performance.

2. Select Period Start Time. Choose a starting snapshot under the graph.

3. By default, the end time is the last snapshot. To specify a different end time, select Period End Time. Choose an ending snapshot under the graph.

4. Click OK to start the analysis. The Automatic Database Diagnostic Monitor page appears detailing its findings and recommendations.

Figure 10-7 Run ADDM Page

Description of "Figure 10-7 Run ADDM Page"

About Performance Advisors

This section deals primarily with the advisors that can improve performance. These advisors include the SQL Tuning, SQL Access, and Memory Advisors. Table 10-1, "Performance Advisors" describes these advisors.

Other advisors such as the Undo and Segment Advisors are listed in Table 10-2, "Other Advisors".

For example, the shared pool memory advisor graphically displays the impact on performance of changing the size of this component of the SGA.

You can run an advisor when faced with the following situations:

• You want to resolve a problem in a specific area, for example, to determine why a given SQL statement is consuming 50 percent of CPU time and what to do to reduce its resource consumption.You can use the SQL Tuning Advisor.

• During application development, you want to tune new schema and its associated SQL workload for optimal performance. You can use the SQL Access Advisor.

• You are planning to add memory to your system. You can use the Memory Advisor to determine the database performance impact of increasing your SGA or PGA.

Table 10-1 describes the performance advisors that Oracle provides. These advisors are described in this chapter.

Table 10-2, "Other Advisors" describes other advisors Oracle provides. These are described elsewhere in this book.

You can run an advisor from the Advisor Central home page, accessible through a link on the Database Home page. You can invoke advisors in other ways, often through recommendations from ADDM or alerts.

Using the SQL Tuning Advisor

Use the SQL Tuning Advisor for tuning SQL statements. Typically, you can run this advisor in response to an ADDM performance finding that recommends its use.

Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, as well as on a SQL workload.

To run the SQL Tuning Advisor do the following:

1. On the Home Page, under Related Links, click Advisor Central, then click SQL Tuning Advisor. The SQL Tuning Advisor Links page appears.

2. The advisor can be run on one of the following sources:

   • Top Activity—The most resource intensive SQL statements executed during the last hour. Use this option to tune SQL statements that might have caused recent performance problems.

   • Period SQL— A set of SQL statements over any 24 hour window. Use this option for pro-active tuning of SQL statements.

   • SQL Tuning Sets (STS)—A set of SQL statements you provide. An STS can be created from SQL statements captured by AWR snapshots or from any SQL workload.

   • Snapshots—A set of SQL statements from an AWR snapshot.

   • Preserved Snapshot Sets—A set of SQL statements from a preserved snapshot set.

3. For example, select Top Activity.

   The Top Activity page appears. This page has a graph showing the active sessions over the last hour.You can select an five minute interval to analyze by clicking the bar under the time line. The Top SQL and Top Sessions tables show the activity within the selected period. You can select one or more SQL statements to analyze.

4. Click Schedule SQL Tuning Advisor.

   The Schedule Advisor page appears showing the SQL statements in the interval. Give your task a name and description, select the scope for the analysis (Comprehensive or Limited), and select a start time for the task. Click OK.

5. The SQL Tuning Result page appears. To view recommendations, select the SQL statement and click View Recommendations. The recommendation can include one or more of the following:

   • Accept the SQL profile, which contains additional SQL statistics specific to the statement that enables the query optimizer to generate a significantly better execution plan at runtime.

   • Gather optimizer statistics on objects with outdated or no statistics

   • Advice on how to rewrite a query for better performance.

   • Create an index to offer alternate, faster access paths to the query optimizer.

Using the SQL Access Advisor

The SQL Access Advisor helps define appropriate access structures such as indexes and materialized views to optimize SQL queries. The advisor takes a SQL workload as an input and recommends which indexes, materialized views, or logs to create, drop, or retain for faster performance. You can select your workload from different sources including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment.

The recommendations that this advisor makes include possible indexes, materialized views, or materialized view logs that can improve your query performance for the given workload.

Note that both SQL Tuning and Access advisor provide index creation recommendations. The SQL Tuning advisor recommends creation of indexes only when it anticipates exceptional performance gains for the SQL statement being tuned. However, creation of new indexes may adversely impact the performance of DML (inserts, updates, and deletes) operations. The SQL Tuning advisor does not take this into account while generating new index recommendations.

The SQL Access Advisor on the other hand considers the impact of new indexes on the complete workload. As such, if an index improves performance of one SQL statement but adversely impacts the rest of the workload, the new index won't be recommended by the SQL Access advisor. For this reason, the SQL Tuning advisor always recommends validating its new index recommendation by running the SQL Access Advisor.

To run the SQL Access advisor:

1. Navigate to the Advisor Central page, and click SQL Access Advisor. This begins a wizard which prompts you for your initial options. Select Use Default Options. Click Continue.

2. The Workload Source page appears. Select your workload source as Current and Recent SQL Activity to analyze the most recent and ongoing activity. You can also select from other sources. The best workload is one that fully represents the statements that access all the underlying tables. Click Next.

3. The Recommendations Options page appears. Select if you want the advisor to recommend indexes, materialized views, or both. You can also choose to perform an evaluation only, which returns information on the objects being accessed by the workload, but does not recommend any new structures.

   You can choose to run the advisor in limited or comprehensive mode. Limited mode runs faster by concentrating on highest cost statements. You can ignore the Advanced Options for now. Click Next.

4. The Schedule page appears. Ensure your task has a name and description. Under Start, select Immediately. Click Next.

5. The Review page appears. Review your options and click Submit.

6. The Advisor Central page appears. A confirmation message appears, indicating that your task has been created successfully. Click Refresh to view the status of your task.

7. When your SQL Access Advisor task has completed, select View Result. The Result for Task page appears.

   The Summary page shows you the potential for improvement under the headings Workload I/O Cost and Query Execution Time Improvement.

   The Recommendations page shows the recommendations, if any, for improving performance. A recommendation might consist of a SQL script with one or more CREATE INDEX statements, which you can run by clicking Schedule Implementation.

Using the Memory Advisor

Adequate physical memory has a significant impact on the performance of your Oracle Database. The SGA and PGA target initialization parameters determine the amount of physical memory available to the database. When you use the DBCA to create your database, the initial value of these parameters is configured according to your workload type and the total amount of memory available on your machine.

With Oracle's automatic memory management capabilities, Oracle automatically adjusts the memory distribution among the various SGA and PGA sub-components for optimal performance. These adjustments are made within the boundaries of your total SGA and PGA target values.

ADDM periodically evaluates the performance of your database to determine performance bottlenecks. If ADDM finds that the current amount of available memory is inadequate and adversely affecting performance, it can recommend that you increase your SGA or PGA target value. You can set new values for the SGA and PGA using the Memory Advisor.

Additionally, you can use the Memory Advisor to perform what-if analysis on:

• The database performance benefits of adding physical memory to your database.

• The database performance impact of reducing the physical memory available to your database.

Optionally, you can use the Memory Advisor to set a new value for the SGA and PGA targets, based on what-if analysis.

Determining Performance Impact for Altering your SGA or PGA

You can use the Memory Advisor to do what-if analysis for adding or removing physical memory to your database. This advisor graphically analyses the database performance impact of altering your SGA or PGA targets.

To gain advice on configuring the total size of your SGA, on the Memory Parameters: SGA Page, click Advice next to Total SGA Size.

The SGA Advice graph appears. Refer to Figure 10-8, "SGA Size Advice".

Improvement in DB Time (%) is plotted against Total SGA size. A higher number for Improvement in DB Time is better for performance. In this example, the graph tells us that an SGA size larger than 1000MB will not improve performance by much. Thus 1000MB is the recommended optimal SGA size.

Figure 10-8 SGA Size Advice

Description of "Figure 10-8 SGA Size Advice"

Similarly, to run the PGA advisor, navigate to the PGA property page. Next to Aggregate PGA Target, click Advice. The PGA Target Advice Page appears, plotting Cache hit percentage against PGA Target memory size.

Cache hit percentage is the percentage of read requests serviced by memory, as opposed to those requests serviced by reading from disk, which is slower. A higher hit percentage indicates better cache performance. The optimal zone for cache hit percentage is between 75 and 100 percent. However, it is not safe to conclude that your database is having performance problems simply because your cache hit percentage is not within the optimal zone. When the amount of currently available PGA memory is not adequate for optimal performance, ADDM will automatically recommend adjusting this value with a performance finding.