12 Analyzing SQL Performance Impact

System changes, such as upgrading a database or adding an index, may cause changes to execution plans of SQL statements, resulting in a significant impact on SQL performance. In some cases, the system changes may cause SQL statements to regress, resulting in performance degradation. In other cases, the system changes may improve SQL performance. Being able to accurately forecast the potential impact of system changes on SQL performance enables you to tune the system beforehand in cases where the SQL statements regress, or to validate and measure the performance gain in cases where the performance of the SQL statements improves.

SQL Performance Analyzer enables you to forecast the impact of system changes on a SQL workload by:

• Measuring the performance before and after the change

• Generating a report that describes the change in performance

• Identifying the SQL statements that regressed or improved

• Providing tuning recommendations for each SQL statement that regressed

• Enabling you to implement the tuning recommendations when appropriate

This chapter contains the following sections:

• SQL Performance Analyzer Usage

• SQL Performance Analyzer Methodology

• Running SQL Performance Analyzer

• Reviewing the SQL Performance Analyzer Report

See Also:

• Oracle Database Real Application Testing User's Guide for more information about SQL Performance Analyzer

SQL Performance Analyzer Usage

You can use SQL Performance Analyzer to analyze the SQL performance impact of any type of system changes. Examples of system changes include the following:

• Database upgrade

  When performing a database upgrade, you may not be able to predict how the system will perform after the upgrade, or if an existing functionality may be adversely affected. For example, a database upgrade installs a new version of the optimizer, which has an effect on SQL performance. SQL Performance Analyzer enables you to compare the SQL performance between two versions of Oracle Database. In this way, you can identify and tune SQL statements that may potentially regress after the database upgrade without affecting your production system.

• Changes to the operating system, hardware, and database configuration

  Changes to the operating systems (such as installing a new operating system), hardware (such as adding more CPU or memory), or database configuration (such as moving from a single instance database environment to Oracle Real Application Clusters) may have a significant effect on SQL performance. SQL Performance Analyzer enables you to determine the improvement or deterioration to SQL performance when making these changes.

• Schema change

  Changing a schema, such as altering indexes or creating new ones, almost inevitably affects SQL performance. SQL Performance Analyzer enables you to determine the effect on SQL performance when making a schema change.

• Database initialization parameter change

  Changing the value of a database parameter may produce unexpected results. For example, you may enable a specific initialization parameter to improve performance, but this change may produce unexpected results because the system constraints may have changed. SQL Performance Analyzer enables you to determine the effect on SQL performance when changing a database initialization parameter.

• SQL tuning

  Accepting recommendations from an advisor (such as ADDM, SQL Tuning Advisor, or SQL Access Advisor) may require you to tune problematic SQL statements. For example, SQL Tuning Advisor may recommend that you accept a SQL profile for a particular SQL statement. SQL Performance Analyzer enables you to measure the performance improvement that may be gained by tuning SQL statements as recommended by the advisors, and determine whether to accept these recommendations.

SQL Performance Analyzer Methodology

You can run SQL Performance Analyzer on a test system that closely resembles the production system, or on the production system itself. Performing a SQL Performance Analyzer analysis is resource-intensive, so performing the analysis on the production system may cause significant performance degradation.

Any global changes made on the system to test the performance effect may also affect other users of the system. For smaller changes, such as adding or dropping an index, the effect on other users may be acceptable. However, for systemwide changes, such as a database upgrade, using a production system is not recommended and should be considered only if a test system is unavailable. If a separate test system is available, then running SQL Performance Analyzer on the test system enables you to test the effects of the changes without affecting the production system.

To ensure that SQL Performance Analyzer can accurately analyze the SQL performance impact, the test system should be as similar to the production system as possible. For these reasons, running SQL Performance Analyzer on a test system is recommended and is the methodology described here. If you choose to run SQL Performance Analyzer on the production system, then substitute the production system for the test system where applicable.

Analyzing the SQL performance effect of system changes using SQL Performance Analyzer is an iterative process that involves the following steps:

1. Capture the SQL workload that you want to analyze on the production system, as described in "Capturing and Transporting a SQL Workload".

2. Transport the SQL workload from the production system to the test system, as described in "Capturing and Transporting a SQL Workload".

3. Create a SQL Performance Analyzer task on the test system using the SQL workload as its input source, as described in "Following a Guided Workflow with SQL Performance Analyzer".

4. Set up the environment on the test system to match the production system as closely as possible, as described in "Establishing the Initial Environment".

5. Build the pre-change performance data by executing the SQL workload on the system before the change, as described in "Collecting SQL Performance Data Before the Change".

6. Perform the system change on the test system, as described in "Making the System Change".

7. Build the post-change performance data by executing the SQL workload on the system after the change, as described in "Collecting SQL Performance Data After the Change".

8. Compare and analyze the pre-change and post-change versions of performance data, as described in "Comparing SQL Performance Before and After the Change".

9. Generate and review a report to identify the SQL statements in the SQL workload that have improved, remain unchanged, or regressed after the system change.

10. Tune any regressed SQL statements that are identified, as described in Chapter 10, "Tuning SQL Statements".

11. Ensure that the performance of the tuned SQL statements is acceptable by repeating Step 5 through Step 10 until your performance goals are met.

This section contains the following topics:

• Capturing and Transporting a SQL Workload

• Setting Up the Database Environment on the Test System

• Executing a SQL Workload

Capturing and Transporting a SQL Workload

Before running SQL Performance Analyzer, capture a set of SQL statements on the production system that represents the SQL workload that you intend to analyze and transport to the test system. The captured SQL statements should include the following information:

• SQL text

• Execution environment

  • SQL binds, which are bind values needed to execute a SQL statement and generate accurate execution statistics

  • Parsing schema under which a SQL statement can be compiled

  • Compilation environment, including initialization parameters under which a SQL statement is executed

• Number of times a SQL statement was executed

You can store captured SQL statements in SQL Tuning Sets and use them as an input source for SQL Performance Analyzer. Capturing a SQL workload using a SQL Tuning Set enables you to do the following:

• Store the SQL text and any necessary auxiliary information in a single, persistent database object

• Populate, update, delete, and select captured SQL statements in the SQL Tuning Set

• Load and merge content from various data sources, such as the Automatic Workload Repository (AWR) or the cursor cache

• Export the SQL Tuning Set from the system where the SQL workload is captured and import it into another system

• Reuse the SQL workload as an input source for other advisors, such as SQL Tuning Advisor and SQL Access Advisor

After you have captured the SQL workload into a SQL Tuning Set on the production system, you must transport it to the test system.

See Also:

• "Creating a SQL Tuning Set"

• "Transporting SQL Tuning Sets"

Setting Up the Database Environment on the Test System

There are many ways to create a test database. For example, you can use the DUPLICATE command of Recovery Manager (RMAN), Oracle Data Pump, or transportable tablespaces. Oracle recommends using RMAN because it can create the test database from pre-existing backups or from the active production datafiles. The production and test databases can reside on the same host or on different hosts.

You should configure the test database environment to match the database environment of the production system as closely as possible. In this way, SQL Performance Analyzer can more accurately forecast the effect of the system change on SQL performance.

See Also:

• Oracle Database Backup and Recovery User's Guide for information about duplicating a database with RMAN

Executing a SQL Workload

After the SQL workload is captured and transported to the test system, and the initial database environment is properly configured, execute the SQL workload to build the pre-change performance data before making the system change. Executing a SQL workload runs each of the SQL statements contained in the workload to completion. During execution, SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload. After the pre-change performance data is built, you can perform the system change.

After performing the system change, execute the SQL workload again to build the post-change performance data. SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload a second time, resulting in a new set of performance data that can be used to compare to the pre-change version of the performance data.

Depending on its size, executing a SQL workload can be resource-intensive and cause a significant performance impact. When executing a SQL workload, you can choose to generate execution plans only, without collecting execution statistics. This technique shortens the time to run the execution and lessens the effect on system resources, but the results of the comparison analysis may not be as accurate. If you are running SQL Performance Analyzer on the production system, then consider executing the SQL workload using a private session to avoid affecting the rest of the system.

Running SQL Performance Analyzer

SQL Performance Analyzer enables you to analyze the effects of environmental changes on execution of SQL statements in SQL Tuning Sets. As explained in "Managing SQL Tuning Sets", a SQL Tuning Set is a database object that includes one or more SQL statements along with their execution statistics and execution context. In addition to the performance analysis, SQL Performance Analyzer can invoke SQL Advisor and provide tuning recommendations.

SQL Performance Analyzer guides you through the SQL workload comparison by means of the following workflows:

• Parameter Change

  Use this workflow to determine how a database initialization parameter change will affect SQL performance.

• Guided Workflow

  Use this workflow to compare SQL performance for all types of system changes other than a database initialization parameter change.

In each of the preceding workflows, you must create a SQL Performance Analyzer task. A task is a container for the results of SQL trials. A SQL trial captures the execution performance of a SQL Tuning Set under specific environmental conditions.

To run SQL Performance Analyzer:

1. On the Database Home page, click Advisor Central.

   The Advisor Central page appears.

2. Click SQL Performance Analyzer.

   SQL Performance Analyzer page appears. A list of existing SQL Performance Analyzer tasks are displayed.

   
   Description of the illustration spa.gif
   
   

3. Do one of the following:

   • Proceed to "Testing an Initialization Parameter Change with SQL Performance Analyzer".

   • Proceed to "Following a Guided Workflow with SQL Performance Analyzer".

See Also:

• Oracle Database Real Application Testing User's Guide for information about running SQL Performance Analyzer using APIs

Testing an Initialization Parameter Change with SQL Performance Analyzer

The Parameter Change workflow enables you to test the performance effect on a SQL Tuning Set when you vary a single environment initialization parameter between two values. For example, you can compare SQL performance when the sort area size is increased from 1 MB to 2 MB.

After you select a SQL Tuning Set and a comparison metric, SQL Performance Analyzer creates a task and performs a trial with the initialization parameter set to the original value. The Analyzer then performs a second trial with the parameter set to the new value. The system-generated SQL trial Comparison report evaluates the regression.

To test an initialization parameter change:

1. On the SQL Performance Analyzer page, click Parameter Change.

   The Parameter Change page appears.

   
   Description of the illustration spa_param_change.gif
   
   

2. In the Task Name field, enter the name of the task.

   For example, enter SORT_TIME.

3. Select the SQL Tuning Set. Do one of the following:

   • In SQL Tuning Set, enter the name the SQL Tuning Set that contains the SQL workload to be analyzed.

   • Click the search icon to search for a SQL Tuning Set, and then select the set.

     The tuning set now appears in the SQL Tuning Set field.

4. In the Description field, optionally enter a description of the task.

   For example enter the following text: Double the value of sort_area_size.

5. In the Creation Method list, determine how the SQL trial is created and what contents are generated by performing one of the following actions:

   • Select Execute SQLs.

     The SQL trial generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements.

   • Select Generate Plans.

     The SQL trial invokes the optimizer to create execution plans only without actually running the SQL statements.

6. In the Per-SQL Time Limit list, determine the time limit for SQL execution during the trial by performing one of the following actions:

   • Select Unlimited.

     The execution will run each SQL statement in the SQL tuning set to completion and gather performance data. Collecting execution statistics provides greater accuracy in the performance analysis but takes a longer time.

   • Select Customize and enter the specified number of seconds, minutes, or hours.

7. In the Parameter Change section, complete the following steps:

   1. In the Parameter Name field, enter the name of the initialization parameter whose value you want to modify, or click the Search icon to review the current parameter settings.

      For example, enter sort_area_size.

   2. In the Base Value field, enter the current value of the initialization parameter.

      For example, enter 1048576.

   3. In the Changed Value field, enter the new value of the initialization parameter.

      For example, enter 2097152.

8. In the Comparison Metric list, select the comparison metric to use for the analysis:

   • If you selected Generate Plans in Step 5, then select Optimizer Cost.

   • If you selected Execute SQLs in Step 5, then select one of the following options:

     • Elapsed Time

     • CPU Time

     • Buffer Gets

     • Disk Reads

     • Direct Writes

     • Optimizer Cost

   To perform the comparison analysis by using more than one comparison metric, perform separate comparison analyses by repeating this procedure using different metrics.

9. In the Schedule section:

   1. In the Time Zone list, select your time zone code.

   2. Select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

10. Click Submit.

    A confirmation message appears.

    In the SQL Performance Analyzer Tasks section, the status of this task is displayed. To refresh the status icon, click Refresh. After the task completes, the Last Run Status icon changes to a check mark.

    
    Description of the illustration spa_spa_tasks.gif
    
    

11. In the SQL Performance Analyzer Tasks section, select the task and click the link in the Name column.

    The SQL Performance Analyzer Task page appears.

    
    Description of the illustration spa_task.gif
    
    

    This page contains the following sections:

    • SQL Tuning Set

      This section summarizes information about the SQL tuning set, including its name, owner, description, and the number of SQL statements it contains.

    • SQL Trials

      This section includes a table that lists the SQL trials used in the SQL Performance Analyzer task.

    • SQL Trial Comparisons

      This section contains a table that lists the results of the workload comparisons

12. Click the icon in the Comparison Report column.

    The SQL Performance Analyzer Task Result page appears.

13. Review the results of the performance analysis, as described in "Reviewing the SQL Performance Analyzer Report".

Following a Guided Workflow with SQL Performance Analyzer

You can use the guided workflow to compare the performance of SQL statements before and after a variety of system changes that can impact the performance of the SQL workload.

Tip:

Before you can create a SQL Performance Analyzer task, capture the SQL workload to be used in the performance analysis into a SQL Tuning Set on the production system. Afterward, transport the workload to the test system where the performance analysis will be performed, as described in "Capturing and Transporting a SQL Workload".

To initiate a guided workflow:

1. On the SQL Performance Analyzer page, click Guided Workflow.

   The Guided Workflow page appears.

   This page lists the required steps in the SQL Performance Analyzer task in sequential order. Each step must be completed in the order displayed before you can begin the next step.

   
   Description of the illustration spa_guided_workflow.gif
   
   

2. Proceed to the next step, as described in "Creating a SQL Performance Analyzer Task Based on a SQL Tuning Set".

Creating a SQL Performance Analyzer Task Based on a SQL Tuning Set

To run SQL Performance Analyzer, you must create a SQL Performance Analyzer task. The task requires you to select the SQL Tuning Set containing the workload to be used in the performance analysis.

The SQL Tuning Set remains constant in the SQL Performance Analyzer task and is executed in isolation during each SQL trial. Thus, performance differences between trials are caused by environmental differences.

Tip:

Before you can create a SQL Performance Analyzer task based on a SQL Tuning Set, capture the SQL workload for the performance analysis in a SQL Tuning Set on the production system. Transport the set to the test system, as described in "Capturing and Transporting a SQL Workload".

To create a task based on a SQL Tuning Set:

1. In the Guided Workflow page, click the Execute icon for the Create SQL Performance Analyzer Task based on SQL Tuning Set step.

   The Create SQL Performance Analyzer Task page appears.

   
   Description of the illustration spa_create_task.gif
   
   

2. In the Name field, enter the name of the task.

3. In the Description field, optionally enter a description of the task.

4. In the SQL Tuning Set section, do one of the following:

   • In Name, enter the name the SQL Tuning Set that contains the SQL workload to be analyzed.

   • Click the search icon to search for a SQL Tuning Set, and then select the set.

     The tuning set now appears in the Name field.

5. Click Create.

   The Guided Workflow page appears.

   The Status icon of this step has changed to a check mark and the Execute icon for the next step is now enabled.

6. Proceed to the next step, as described in "Establishing the Initial Environment".

Establishing the Initial Environment

After selecting a SQL Tuning Set as the input source, establish the initial environment on the test system. This step is not included in the Guided Workflow page because you must perform it manually. For more information about setting up the database environment, see "Setting Up the Database Environment on the Test System".

Tip:

Before you establish the initial environment, select a SQL Tuning Set, as described in "Creating a SQL Performance Analyzer Task Based on a SQL Tuning Set".

To establish the initial environment:

1. On the test system, manually make any necessary environmental changes affecting SQL optimization and performance.

   These changes could include changing initialization parameters, gathering or setting optimizer statistics, and creating indexes.

2. Proceed to the next step, as described in "Collecting SQL Performance Data Before the Change".

Collecting SQL Performance Data Before the Change

After you have properly configured the initial environment on the test system, build the pre-change version of performance data by executing the SQL workload before performing the system change. For more information about executing a workload, see "Executing a SQL Workload".

Tip:

Before computing the pre-change version of performance data, establish the initial environment, as described in "Establishing the Initial Environment".

To collect SQL performance data before the change:

1. On the Guided Workflow page, click the Execute icon for the Replay SQL Tuning Set in Initial Environment step.

   The Create SQL trial page appears. A summary of the selected SQL Tuning Set containing the SQL workload is displayed.

   
   Description of the illustration spa_create_trial.gif
   
   

2. In the SQL Trial Name field, enter the name of the SQL trial.

3. In the SQL Trial Description field, enter a description of the SQL trial.

4. In the Creation Method list, determine how the SQL trial is created and what contents are generated by performing one of the following actions:

   • Select Execute SQLs Locally.

     The SQL trial generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements locally on the test system.

   • Select Execute SQLs Remotely.

     The SQL trial generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements remotely on another test system over a public database link.

   • Select Generate Plans Locally.

     The SQL trial invokes the optimizer to create execution plans locally on the test system without actually running the SQL statements.

   • Select Generate Plans Remotely.

     The SQL trial invokes the optimizer to create execution plans remotely on another test system over a public database link without actually running the SQL statements.

   • Select Build From SQL Tuning Set.

     The SQL trial copies the execution plans and statistics from the SQL tuning set directly into the trial.

5. In the Per-SQL Time Limit list, determine the time limit for SQL execution during the trial by performing one of the following actions:

   • Select Unlimited.

     The execution will run each SQL statement in the SQL tuning set to completion and gather performance data. Collecting execution statistics provides greater accuracy in the performance analysis but takes a longer time.

   • Select Customize and enter the specified number of seconds, minutes, or hours.

6. Ensure that the database environment on the test system matches the production environment as closely as possible, and select Trial environment established.

7. In the Schedule section, do one of the following:

   1. Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

   2. Select Immediately to start the task now, or Later to schedule the task to start at a time specified with the Date and Time fields.

8. Click OK.

   The Guided Workflow page appears when the execution begins.

   The status icon of this step changes to a clock while the execution is in progress. To refresh the status icon, click Refresh. Depending on the options selected and the size of the SQL workload, the execution may take a long time to complete. After the execution is completed, the Status icon will change to a check mark and the Execute icon for the next step is enabled.

9. Proceed to the next step, as described in "Making the System Change".

Making the System Change

After computing the pre-change SQL performance data, perform the system change on the test system. This step is not included in the Guided Workflow page because you must perform it manually. Depending on the type of change, it may be necessary to reconfigure the environment on the test system to match the new environment for which you want to perform SQL performance analysis, as described in "Setting Up the Database Environment on the Test System".

SQL Performance Analyzer can analyze the SQL performance impact of any type of system change. For example, you may want to test an application upgrade that involves changes such as database table redesign, adding or removing indexes, and so on. For examples of different types of system changes that can be analyzed by SQL Performance Analyzer, see "SQL Performance Analyzer Usage".

Tip:

Before making the system change, build the pre-change version of performance data, as described in "Collecting SQL Performance Data Before the Change".

To make the system change:

1. Make the necessary changes to the test system.

2. Proceed to the next step, as described in "Collecting SQL Performance Data After the Change".

Collecting SQL Performance Data After the Change

After you have made the system change, build the post-change version of performance data by executing the SQL workload again. For more information about executing a workload, see "Executing a SQL Workload".

Tip:

Before you can build the post-change version of performance data, make the system change, as described in "Making the System Change".

To collect SQL performance after the change:

1. On the Guided Workflow page, click the Execute icon for the Replay SQL Tuning Set in Changed Environment step.

   The Create SQL trial page appears.

2. In the SQL Trial Name field, enter the name of the execution.

3. In the SQL Trial Description field, enter a description of the execution.

4. In the Creation Method list, determine how the SQL trial is created and what contents are generated by performing one of the following actions:

   • Select Execute SQLs Locally.

     The SQL trial generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements locally on the test system.

   • Select Execute SQLs Remotely.

     The SQL trial generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements remotely on another test system over a public database link.

   • Select Generate Plans Locally.

     The SQL trial invokes the optimizer to create execution plans locally on the test system without actually running the SQL statements.

   • Select Generate Plans Remotely.

     The SQL trial invokes the optimizer to create execution plans remotely on another test system over a public database link without actually running the SQL statements.

   For each of these creation methods, the application schema and data should already exist on the local or remote test system.

5. In the Per-SQL Time Limit list, determine the time limit for SQL execution during the trial by performing one of the following actions:

   • Select Unlimited.

     The execution will run each SQL statement in the SQL tuning set to completion and gather performance data. Collecting execution statistics provides greater accuracy in the performance analysis but takes a longer time.

   • Select Customize and enter the specified number of seconds, minutes, or hours.

6. Ensure that the database environment on the test system is set up to match the production environment as closely as possible, and select Trial environment established.

7. In the Schedule section, complete the following steps:

   1. Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

   2. Select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

8. Click OK.

   The Guided Workflow page appears when the execution begins.

   The status icon of this step changes to an arrow icon while the execution is in progress. To refresh the status icon, click Refresh. Depending on the options selected and the size of the SQL workload, the execution may take a long time to complete. After the execution is completed, the Status icon will change to a check mark and the Execute icon for the next step is enabled.

9. Proceed to the next step, as described in "Comparing SQL Performance Before and After the Change".

Comparing SQL Performance Before and After the Change

After the post-change SQL performance data is built, compare the pre-change version of performance data to the post-change version by running a comparison analysis.

Tip:

Before you can compare the pre-change version of performance data with the post-change version, build the post-change version of performance data, as described in "Collecting SQL Performance Data After the Change".

To analyze SQL performance before and after the change:

1. On the Guided Workflow page, click the Execute icon for Compare Step 2 and Step 3.

   The Run SQL Trial Comparison page appears.

   
   Description of the illustration spa_run_trial_compare.gif
   
   

   In this example, the SQL_TRIAL_1207494888380 and SQL_TRIAL_1207499034916 trials are selected for comparison.

2. To compare trials other than those listed by default, select the desired trials in the Trial 1 Name and Trial 2 Name lists.

   Note that you cannot compare a statistical trial with a trial that tests the explain plan only.

3. In the Comparison Metric list, select the comparison metric to use for the comparison analysis.

   The types of comparison metrics you can use include:

   • Elapsed Time

   • CPU Time

   • Buffer Gets

   • Disk Reads

   • Direct Writes

   • Optimizer Cost

   To perform the comparison analysis by using more than one comparison metric, perform separate comparison analyses by repeating this procedure with different metrics.

4. In the Schedule section, complete the following steps:

   1. Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

   2. Select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

5. Click Submit.

   The Guided Workflow page appears when the comparison analysis begins.

   The status icon of this step changes to an arrow icon while the comparison analysis is in progress. To refresh the status icon, click Refresh. Depending on the amount of performance data collected from the pre-change and post-change executions, the comparison analysis may take a long time to complete. After the comparison analysis is completed, the Status icon changes to a check mark.

6. Click the Execute icon for View Trial Comparison Result.

   The SQL Performance Analyzer Task Result page appears.

7. Review the results of the analysis, as described in "Reviewing the SQL Performance Analyzer Report".

Reviewing the SQL Performance Analyzer Report

When a SQL Performance Analyzer task is completed, the resulting data is generated into a report. This section shows a sample of a SQL Performance Analyzer report. This sample report uses the elapsed time comparison metric to compare the pre-change and post-change executions of a SQL workload.

Description of the illustration spa_task_result.gif

To review the SQL Performance Analyzer report:

1. Review the general information about the performance analysis, as described in "Reviewing the SQL Performance Analyzer Report: General Information".

2. Review general statistics, as described in "Reviewing the SQL Performance Analyzer Report: Global Statistics".

3. Optionally, review the detailed statistics, as described in "Reviewing the SQL Performance Analyzer Report: Global Statistics Details".

Reviewing the SQL Performance Analyzer Report: General Information

The General Information section contains basic information and metadata about the workload comparison performed by SQL Performance Analyzer.

Description of the illustration spa_task_result_info.gif

To review general information:

1. On the SQL Performance Analyzer Task Result page, review the information at the top of the page.

   This summary at the top of the page includes the following information:

   • The name, owner, and description of the SQL Performance Analyzer task

   • The name and owner of the SQL Tuning Set

   • The total number of SQL statements in the tuning set and the number of failing statements

   • The names of the SQL trials and the comparison metric used

2. Optionally, click the link next to SQL Tuning Set Name.

   The SQL Tuning Set page appears.

   This page contains information the SQL ID, SQL text, and related information about every SQL statement in the set.

3. Click the link next to SQL Statements With Errors if errors were found.

   The SQL Performance Analyzer Task Result page appears.

   The Errors table reports all errors that occurred while executing a given SQL workload. An error may be reported at the SQL Tuning Set level if it is common to all statements executions in the SQL Tuning Set, or at the execution level if it is specific to a SQL statement or execution plan.

4. Review general statistics, as described in "Reviewing the SQL Performance Analyzer Report: Global Statistics".

Reviewing the SQL Performance Analyzer Report: Global Statistics

The Global Statistics section reports statistics that describe the overall performance of the entire SQL workload. This section is a very important part of the SQL Performance Analyzer analysis because it reports on the impact of the system change on the overall performance of the SQL workload. Use the information in this section to understand the tendency of the workload performance, and determine how the workload performance will be affected by the system change.

To review the global statistics:

1. Review the chart in the Projected Workload Execute Elapsed Time subsection.

   The chart shows the two replay executions on the x-axis and the execute elapsed time (in seconds) on the y-axis.

   
   Description of the illustration spa_task_result_elapsed.gif
   
   

   The most important statistic is the overall impact, which is given as a percentage. The overall impact is the difference between the improvement impact and the regression impact. You can click the link for any impact statistic to obtain more details, as described in "Reviewing the SQL Performance Analyzer Report: Global Statistics Details".

   In this example, the improvement impact is 44%, while the regression impact is -6%, so the overall impact of the system change is an improvement of approximately 37%.

2. Review the chart in the SQL Statement Count subsection.

   The x-axis of the chart shows the number of SQL statements that are improved, regressed, and unchanged after the system change. The y-axis shows the number of SQL statements. The chart also indicates whether the explain plan was changed or unchanged for the SQL statements.

   
   Description of the illustration spa_task_result_sqlcount.gif
   
   

   This chart enables you to quickly weigh the relative performance of the SQL statements. You can click any bar in the chart to obtain more details, as described in "Reviewing the SQL Performance Analyzer Report: Global Statistics Details".

   In this example, all SQL statements were unchanged after the system change.

Reviewing the SQL Performance Analyzer Report: Global Statistics Details

You can use the SQL Performance Analyzer Report to obtain detailed statistics for the SQL workload comparison. The details chart enables you to drill down into the performance of SQL statements that appears in the Result Summary section of the report. Use the information in this section to investigate why the performance of a particular SQL statement regressed.

To review the global statistics details:

1. Click the bar in any chart on the SQL Performance Analyzer Task Result page, or click the impact percentages in the Projected Workload Execute Elapsed Time subsection.

   A table including the detailed statistics appears. Depending on the table, the following columns are included:

   • SQL ID

     This column indicates the ID of the SQL statement.

   • Net Impact on Workload (%)

     This column indicates the impact of the system change relative to the performance of the SQL workload.

   • Elapsed Time

     This column indicates the total time (in seconds) of the SQL statement execution.

   • Net Impact on SQL (%)

     This column indicates the local impact of the change on the performance of a particular SQL statement.

   • % of Workload

     This column indicates the percentage of the total workload consumed by this SQL statement.

   • Plan Changed

     This column indicates whether the SQL execution plan changed.

2. Click SQL ID for any SQL statement in the table.

   The SQL Details page appears.

   You can use this page to access the text of the SQL statement and obtain low-level details such as CPU time, buffer gets, and optimizer cost.