About Resolving Performance Degradation Over Time

Performance degradation of the database occurs when your database was performing optimally in the past, such as 6 months ago, but has gradually degraded to a point where it becomes noticeable to the users. The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time.

While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports with a total of four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods. The two time periods selected for the AWR Compare Periods report can be of different durations. The report normalizes the statistics by the amount of time spent on the database for each time period and presents statistical data ordered by the largest difference between the periods.

For example, a batch workload that historically completed in the maintenance window between 10:00 p.m. and midnight is currently showing poor performance and completing at 2 a.m. You can generate an AWR Compare Periods report from 10:00 p.m. to midnight on a day when performance was good and from 10:00 a.m. to 2 a.m. on a day when performance was poor. The comparison of these reports should identify configuration settings, workload profile, and statistics that were different in these two time periods. Based on the differences identified, you can diagnose the cause of the performance degradation.

This chapter contains the following sections:

9.1 Managing Baselines

Baselines are an effective way to diagnose performance problems. AWR supports the capture of baseline data by enabling you to specify and preserve a pair or a range of snapshots as a baseline. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

A moving window baseline corresponds to all AWR data that exists within the AWR retention period. Oracle Database automatically maintains a system-defined moving window baseline. The default size of the window is the current AWR retention period, which by default is 8 days.

9.1.1 Creating a Baseline

Before creating a baseline, carefully consider the time period you choose as a baseline because it should represent the database operating at an optimal level. In the future, you can compare these baselines with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.

9.1.1.1 Creating a Single Baseline

A single baseline is captured at a single, fixed time interval. For example, a single baseline may be captured on February 5, 2012 from 5:00 p.m. to 8:00 p.m.

You can choose future start and end times to create a baseline that captures future database activity. A baseline template is a specification that enables Oracle Database to automatically generate a baseline for a future time period.

To create a single baseline:

Access the Database Home page.-> Performance -> AWR ->AWR Administration

The AWR Baselines page appears with a list of existing baselines displayed.

->CREATE 

 -> Single

 -> Continue

The Create Baseline: Single Baseline page appears.

1. In the Baseline Name field, enter a name for the baseline.

 Under Baseline Interval, select whether to use a snapshot range or a time range for the baseline. Do one of the following:

1. Snapshot Range
2. Period Start Time
3. Period End Time
4. Change Chart Time Period
5. Chart Start Date
6. Chart End Date
7. Go

 9.1.1.2 Creating a Repeating Baseline

A repeating baseline is a baseline that repeats during a time interval over a specific period. 

To create a repeating baseline:

Click Create -> Repeating -> Continue ->Baseline Name Prefix -> Daily -> Weekly 

Under Interval of Baseline Creation, complete the following steps:

Start Time -> End Time -> Finish

9.1.2 Deleting a Baseline

To conserve storage space, you may want to periodically delete unused baselines stored in the database.

To delete a baseline:

Access the AWR Baselines page -> Delete -> Purge the underlying data associated with the baseline -> Do not purge the underlying data associated with the baseline -> Yes

9.1.3 Computing Threshold Statistics for Baselines

Computing threshold statistics for baselines enables you to graphically display the computed statistics in the charts on the Performance page.

To compute threshold statistics for baselines:

1. Access the AWR Baselines page
2. Select the baseline for which you want to compute statistics
3. Select a baseline that does not already have computed statistics. 
4. From the Actions list, select Schedule Statistics Computation, and then click Go.
5. The Compute Threshold Statistics page appears.

This example computes statistics for the baseline BASELINE_TUE_1120

1. In the Name field, enter a name for the task.

   Alternatively, you can choose to use the system-generated name.

2. In the Description field, enter a description for the task.

   Alternatively, you can choose to use the system-generated description.

3. Under Start, do one of the following:

   • Select Immediately to run the task immediately after it has been submitted.

   • Select Later to run the task at a later time as specified using the Date and Time fields.

   This computation is resource-intensive, so you may want to schedule it to run during off-peak hours.

4. Click Submit.

   The AWR Baselines page appears. A message informs you that statistics computation has been scheduled for the selected baseline.

 

 9.1.4 Setting Metric Thresholds for Baselines

a metric is the rate of change in a cumulative statistic. Alerts notify you when particular metric thresholds are crossed. When the metric thresholds are crossed, the system is in an undesirable state. You can edit the threshold settings for baseline metrics.

9.1.4.1 Setting Metric Thresholds for the Default Moving Baseline

To set metric thresholds for the default moving baseline:

1. Access the Database Home page.
2. Performance 
3. Adaptive Thresholds
4. Quick Configuration
5. Workload Profile(Primarily OLTP (pure transaction processing 24 hours a day),Primarily Data Warehousing (query and load intensive),Alternating (OLTP during the daytime and batch during the nighttime))
6. Primarily OLTP
7. Continue

9.1.4.2 Setting Metric Thresholds for Selected Baselines

This section explains how to select a baseline and edit its thresholds. You can configure the type of threshold, for example, whether it is based on significance levels, percentage of maximum values, or fixed values. You can also configure the threshold levels that determine when the database generates critical alerts and warnings.

To set a metric threshold for a selected moving baseline:

Access the Baseline Metric Thresholds page -> Basic Metrics -> Category/Name -> Number of Transactions (per second) 

9.2 Running the AWR Compare Periods Reports

9.2.1 Comparing a Baseline to Another Baseline or Pair of Snapshots

When performance degradation occurs over time, you can run the AWR Compare Periods report to compare the degraded performance, captured as a new baseline or a pair of snapshots, to an existing baseline. You must have a baseline that represents the system operating at an optimal level. If an existing baseline is unavailable, then compare database performance between two periods of time using two arbitrary pairs of snapshots.

 To compare a baseline to another baseline:

Access the Database Home page -> Performance  -> AWR -> AWR Administration

9.2.2 Comparing Current System Performance to a Baseline Period

 You may have noticed a performance change on a production system and would like to know why, or you may have implemented a change to a production system and want to know the effect of the change, such as increased concurrency waits.

The Compare Period ADDM compares the performance of the database server in two time periods, and returns a report describing the performance changes and the root origin of the changes. 

From the Performance menu, select AWR, then Compare Period ADDM.

rom the Run Compare Period ADDM page, specify the desired comparison and base periods:

• Comparison Period
• Base Period

Click Run to display the Database Compare Period Report.

9.2.3 Comparing Two Pairs of Snapshots

If an existing baseline is unavailable, then you can compare database performance by using two arbitrary pairs of snapshots. Use one pair taken when the database is performing optimally, and another pair when the database is performing poorly. At least four snapshots must be available.

To compare performance using two pairs of snapshots:

Access the Database Home page.

From the Performance menu, select AWR, then Compare Periods Reports.

1. In First Period, select By Snapshot.

2. Select the starting point for the first snapshot period to be included in the report, and then click Select.

3. Select the ending point for the first snapshot period to be included in the report, and then click Select.

4. Click Generate Report.

5. To save the report as an HTML file, click Save to File.

9.3 Using the AWR Compare Periods Reports

After an AWR Compare Periods report is generated for the time periods you want to compare, you can use it to analyze performance degradation. 

The AWR Compare Periods report is divided into the following sections:

9.3.1 Summary of the AWR Compare Periods Report

The report summary is at the beginning of the AWR Compare Periods report, and summarizes information about the snapshot sets and loads used in the report. 

9.3.1.1 Snapshot Sets

The Snapshot Sets section displays information about the snapshot sets used for this report, such as instance, host, and snapshot information.

9.3.1.2 Host Configuration Comparison

The Host Configuration Comparison section compares the host configurations used in the two snapshot sets. For example, the report compares physical memory and number of CPUs. Differences in the configurations are quantified as percentages in the %Diff column.

9.3.1.3 Cache Sizes

The Cache Sizes section compares the database configurations used in the two snapshot sets. For example, the report compares the SGA and log buffer size. Differences in the configurations are quantified as percentages in the %Diff column.

9.3.1.4 Load Profile

The Load Profile section compares the loads used in the two snapshot sets. Differences in the loads are quantified as percentages in the %Diff column.

9.3.1.5 Top Timed Events

The Top Timed Events section is one of the most useful sections in the report. This section displays the five timed events or operations that consumed the highest percentage of total DB time in each of the snapshot sets.

9.3.2 Details of the AWR Compare Periods Report

The Report Details section follows the summary of the AWR Compare Periods report, and provides statistics about the snapshot sets and loads used in the report. For example, the section includes statistics for time model statistics, wait events, SQL execution time, and instance activity.

9.3.3 Supplemental Information in the AWR Compare Periods Report

The supplemental information is at the end of the AWR Compare Periods report, and provides additional information about initialization parameters and SQL statements. The init.ora Parameters section lists all the initialization parameter values for the first snapshot set. The Complete List of SQL Text section lists each statement by SQL ID and shows the text of the SQL statement.