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. The Advisor can analyze any Oracle RDBMS version 10.2.0.4 or later monitored by Cloud Control. The following procedure explains how to initiate a report from the Compare Period ADDM.

1. From the Performance menu, select AWR, then Compare Period ADDM.
2. From the Run Compare Period ADDM page, specify the desired comparison and base periods:

   • Comparison Period — Generally represents an improperly functioning time period. However, you could also use the advisor to understand why performance has improved now when compared with an earlier time period.

   • Base Period — Represents a known (baseline or reference) period in which the database is functioning properly. You should select a base period in which the performance was acceptable, and the workload was as similar or identical as possible.

3. Click Run to display the Database Compare Period Report.
4. Examine the sections of the report to understand the performance change between the two periods and the cause of the change:

   • Overview

     This portion of the report shows SQL commonality, which is the comparability between the base and comparison periods based on the average resource consumption of the SQL statements common to both periods.

     A commonality value of 100% means that the workload "signature" in both time periods is identical. A value of 0% means that the two time periods have no items in common for the specific workload dimension.

     Commonality is based on the type of input (that is, which SQL is executing) as well as the load of the executing SQL statements. Consequently, SQL statements running in only one time period, but not consuming significant time, do not affect commonality. Therefore, two workloads could have a commonality of 100% even if some SQL statements are running only in one of the two periods, provided that these SQL statements do not consume significant resources.

   • Configuration

     The information displayed shows base period and comparison period values for various parameters categorized by instance, host, and database.

   • Findings

     The findings can show performance improvements and identify the major performance differences caused by system changes. For negative outcomes, if you understand and remove the cause, the negative outcome can be eliminated.

     The values shown for the Base Period and Comparison Period represent performance with regard to database time.

     The Change Impact value represents a measurement of the scale of a change in performance from one time period to another. It is applicable to issues or items measured by the total database time they consumed in each time period. The absolute values are sorted in descending order.

     If the value is positive, an improvement has occurred, and if the value is negative, a regression has occurred. For instance, a change impact of -200% means that period 2 is three times as slow as period 1.

     You can run performance tuning tools, such as ADDM and the SQL Tuning Advisor, to fix issues in the comparison period to improve general system performance.

   • Resources

     The information shown here provides a summary of the division of database time for both time periods, and shows the resource usage for CPU, memory, I/O, and interconnect (Oracle RAC only).

5. Based on your observations, decide how to proceed to resolve performance regressions, then implement your action plan.