3 Creating an Analysis Task

3.1.1 Using the Parameter Change Workflow

The parameter change workflow enables you to test the performance effect on a SQL workload when you change the value of a single environment initialization parameter. For example, you can compare SQL performance by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter set to 10.2.0.4 and 11.2.0.1.

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. SQL Performance Analyzer then performs a second trial with the parameter set to the changed value by issuing an ALTER SESSION statement. The impact of the change is thus contained locally to the testing session. Any regression or change in performance is reported in a system-generated SQL Performance Analyzer report.

To use the SQL Performance Analyzer parameter change workflow:

1. On the SQL Performance Analyzer page, under SQL Performance Analyzer Workflows, 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.

3. In the SQL Tuning Set field, enter the name of the SQL tuning set that contains the SQL workload to be analyzed.

   Alternatively, click the search icon to search for a SQL tuning set using the Search and Select: SQL Tuning Set window.

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

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

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 5 minutes.

     The execution will run each SQL statement in the SQL tuning set up to 5 minutes and gather performance data.

   • 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. Using this setting is not recommended because the task may be stalled by one SQL statement for a prolonged time period.

   • 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 select an initialization parameter using the Search and Select: Initialization Parameters window.

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

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

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

     • User I/O Time

     • Buffer Gets

     • Physical I/O

     • Optimizer Cost

     • I/O Interconnect Bytes

   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.

    The SQL Performance Analyzer page 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 Status field changes to Completed.

    
    Description of the illustration spa_tasks_param_change.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_param_change.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 SQL trial 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 Using Oracle Enterprise Manager".

14. In cases when regression are identified, click the icon in the SQL Tune Report column to view a SQL tuning report.

3.1.2 Using the Optimizer Statistics Workflow

The optimizer statistics workflow enables you to analyze the effects of optimizer statistics changes on the performance of a SQL workload.

SQL Performance Analyzer tests the effect of new optimizer statistics by enabling pending optimizer statistics in the testing session. The first SQL trial measures the baseline SQL tuning set performance; the second SQL trial uses the pending optimizer statistics. You can then run a comparison report for the two SQL trials.

To use the optimizer statistics workflow:

1. On the SQL Performance Analyzer page, under SQL Performance Analyzer Workflows, click Optimizer Statistics.

   The Optimizer Statistics page appears.

   
   Description of the illustration spa_optimizer_stats.gif
   
   

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

3. In the SQL Tuning Set field, enter the name of the SQL tuning set that contains the SQL workload to be analyzed.

   Alternatively, click the search icon to search for a SQL tuning set using the Search and Select: SQL Tuning Set window.

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

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

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 5 minutes.

     The execution will run each SQL statement in the SQL tuning set up to 5 minutes and gather performance data.

   • 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. Using this setting is not recommended because the task may be stalled by one SQL statement for a prolonged time period.

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

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

   • Elapsed Time

   • CPU Time

   • User I/O Time

   • Buffer Gets

   • Physical I/O

   • Optimizer Cost

   • I/O Interconnect Bytes

   Optimizer Cost is the only comparison metric available if you chose to generate execution plans only in the SQL trials.

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

8. Ensure that pending optimizer statistics are collected, and select Pending optimizer statistics collected.

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.

    The SQL Performance Analyzer page 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 Status field changes to Completed.

    
    Description of the illustration spa_tasks_optimizer.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_optimizer.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 SQL trial 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 Using Oracle Enterprise Manager".

    Any regressions found in performance can be fixed using SQL plan baselines and the SQL Tuning Advisor. If the pending optimizer statistics produce satisfactory performance, you can publish for use.

3.1.3 Using the Exadata Simulation Workflow

The Exadata simulation workflow enables you to simulate the effects of an Exadata Storage Server installation on the performance of a SQL workload.

Oracle Exadata provides extremely large I/O bandwidth coupled with a capability to offload SQL processing from the database to storage. This allows Oracle Database to significantly reduce the volume of data sent through the I/O interconnect, while at the same time offloading CPU resources to the Exadata storage cells.

SQL Performance Analyzer can analyze the effectiveness of Exadata SQL offload processing by simulating an Exadata Storage Server installation and measuring the reduction in I/O interconnect usage for the SQL workload.

Running the Exadata simulation does not require any hardware or configuration changes to your system. After you select a SQL tuning set, SQL Performance Analyzer creates a task and performs an initial trial with the Exadata Storage Server simulation disabled. SQL Performance Analyzer then performs a second trial with the Exadata Storage Server simulation enabled. SQL Performance Analyzer then compares the two trials using the I/O Interconnect Bytes comparison metric and generates a SQL Performance Analyzer report, which estimates the amount of data that would not need to be sent from the Exadata storage cells to the database if Oracle Exadata is being used. In both SQL trials, the SQL statements are executed to completion and I/O interconnect bytes measurements are taken as the actual and simulated Exadata values for the first and second trials, respectively. The measured change in I/O interconnect bytes provides a good estimate of how much filtering can be performed in the Exadata storage cells and, in turn, the amount of CPU that normally would be used to process this data, but now can be offloaded from the database.

To use the SQL Performance Analyzer Exadata simulation workflow:

1. On the SQL Performance Analyzer page, under SQL Performance Analyzer Workflows, click Exadata Simulation.

   The Exadata Simulation page appears.

   
   Description of the illustration spa_exadata_sim.gif
   
   

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

3. In the SQL Tuning Set field, enter the name of the SQL tuning set that contains the SQL workload to be analyzed.

   Alternatively, click the search icon to search for a SQL tuning set using the Search and Select: SQL Tuning Set window.

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

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

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 5 minutes.

     The execution will run each SQL statement in the SQL tuning set up to 5 minutes and gather performance data.

   • 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. Using this setting is not recommended because the task may be stalled by one SQL statement for a prolonged time period.

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

6. 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.

7. Click Submit.

   The SQL Performance Analyzer page 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 Status field changes to Completed.

   
   Description of the illustration spa_tasks_exadata.gif
   
   

8. 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_exadata_sim.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 SQL trial comparisons

9. Click the icon in the Comparison Report column.

   The SQL Performance Analyzer Task Result page appears.

10. Review the results of the performance analysis, as described in "Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager".

    Any SQL performance improvement with the Exadata simulation between the first and second trials is captured in the report. In general, you can expect a greater impact if the SQL workload contains queries that scan a large number of rows or a small subset of table columns. Conversely, a SQL workload that queries indexed tables or tables with fewer rows will result in a lesser impact from the Exadata simulation.

3.1.4 Using the Guided Workflow

The guided workflow enables you to test the performance effect of any types of system changes on a SQL workload, as listed in "SQL Performance Analyzer".

To use the SQL Performance Analyzer task guided workflow:

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

   The Guided Workflow page appears.

   The guided workflow enables you to test the performance effect on a SQL workload when you perform any type of system changes, as described in "SQL Performance Analyzer".

   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 the next step can begin.

   
   Description of the illustration spa_guided_workflow.gif
   
   

2. On the Guided Workflow page, click the Execute icon for the Step 1: Create SQL Performance Analyzer Task based on SQL Tuning Set.

   The Create SQL Performance Analyzer Task page appears.

   
   Description of the illustration spa_create_task.gif
   
   

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

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

5. Under SQL Tuning Set, in the Name field, enter the name the SQL tuning set that contains the SQL workload to be analyzed.

   Alternatively, click the search icon to select a SQL tuning set from the Search and Select: SQL Tuning Set window.

6. 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.

7. Once the analysis task is created, you can build the pre-change performance data by executing the SQL statements stored in the SQL tuning set, as described in Chapter 4, "Creating a Pre-Change SQL Trial".

3.2.1 Running the Exadata Simulation Using APIs

This section describes how to run the Oracle Exadata simulation using APIs. For information about how SQL Performance Analyzer simulates the effects of an Exadata Storage Server installation on the performance of a SQL workload, see "Using the Exadata Simulation Workflow".

To enable Exadata simulation for an analysis task, call the SET_ANALYSIS_TASK_PARAMETER procedure before creating the post-change SQL trial, as shown in the following example:

EXEC DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name => 'my_spa_task', -
       parameter => 'CELL_SIMULATION_ENABLED', - 
       value => 'TRUE');

This will enable Exadata simulation when you create the post-change SQL trial, which can then be compared to the pre-change SQL trial that was created with Exadata simulation disabled.

Alternatively, you can run the Exadata simulation using the tcellsim.sql script:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/tcellsim.sql
   

2. Enter the name and owner of the SQL tuning set to use:

   Enter value for sts_name: MY_STS
   Enter value for sts_owner: IMMCHAN
   

   The script then runs the following four steps automatically:

   • Creates a SQL Performance Analyzer task

   • Test executes SQL statements with Exadata simulation disabled

   • Test executes SQL statements with Exadata simulation enabled

   • Compares performance and generates analysis report