You can create a SQL Performance Analyzer task in Database Management SQL Performance Watch to run SQL Performance Analyzer.

1. Go to the SQL Performance Watch home page for the database.
2. In the Tasks section, click Create.
3. In the Create SQL Performance Analyzer task panel:
   1. Specify the following general options:
      1. Name: Enter a name for the task.
      2. SQL tuning set: Click the Search icon () to select a SQL tuning set. If a SQL tuning set is not available, click Create to go to the SQL tuning sets section in Database Management Diagnostics & Management and create a SQL tuning set. Note that you can set the preferred credential even if you've not enabled Diagnostics & Management for the database.

         The SQL tuning set is the input source for a SQL Performance Analyzer task. The set of SQL statements on the production system that represents the SQL workload that you intend to analyze, must be captured and stored in a SQL tuning set. For information, see Capturing the SQL Workload in Oracle Database Testing Guide.

         You can create the SQL tuning set in Diagnostics & Management or Oracle Enterprise Manager before creating the SQL Performance Analyzer task or in Diagnostics & Management, while creating the SQL Performance Analyzer task. For information on how to create a SQL tuning set in:

         • Diagnostics & Management, see Create a SQL Tuning Set.
         • Oracle Enterprise Manager, see Creating a SQL Tuning Set in Oracle Database 2 Day + Performance Tuning Guide.
         Note
         
         You must ensure that the database user used to create the SQL tuning set is used to create the SQL Performance Analyzer task.
      3. Description: Optionally, enter a description for the task.
      4. Change type: Select one of the following change type options to determine its impact on SQL performance:
         • Parameter change: Enables you to test the performance effect on a SQL workload when you change the value of initialization parameters. If you select this option, click Add parameter to add the initialization parameters whose values you want to modify.
         • Optimizer statistics: Enables you to analyze the impact of optimizer statistic changes on the performance of a SQL workload. This option establishes that:
           • Pre-change SQL trial uses current statistics
           • Post-change SQL trial uses pending statistics
             Note
             
             If selecting the Optimizer statistics option, you must ensure that the post-change trial optimizer statistics have been collected and saved as pending.
         • Ignore optimizer hints: Enables you to analyze the impact of optimizer hints on the performance of a SQL workload. This option establishes that:
           • Pre-change SQL trial uses optimizer hints
           • Post-change SQL trial ignores optimizer hints
         • Upgrade: Enables you to analyze the impact of a database upgrade from one version to another.
         • Migration: Enables you to analyze the impact of data migration from source databases to target databases.
         • Custom: Enables you to customize your task according to your preferences.
   2. Select one of the following SQL Performance Analyzer task creation methods:
      • Basic: Enables you to quickly create a SQL Performance Analyzer task, and the pre-change and post-change SQL trials share the same configuration. If you select the Basic option, you must specify the following:
        1. Trial executions: Specify the execution method and time limit:
           1. Execution method: Select one of the following options to determine how the SQL trials are created and what content is generated:
              • Execute SQL: Generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements.
              • Generate plans: Invokes the optimizer to create execution plans only, without actually running the SQL statements.
              • Hybrid: Finds SQL statements with plan changes first, and then test-executes the SQL statements with plan changes. This is the default option.
           2. Per SQL time limit: Select one of the following options to specify the time limit for SQL execution during the trial.
              • 5 mins: Runs each SQL statement in the SQL tuning set for up to 5 minutes and gathers performance data.
              • Unlimited: Runs each SQL statement in the SQL tuning set to completion and gathers 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.
              • Custom: Enables you to specify the number of seconds, minutes, or hours.
        2. Comparison: Specify the details for the comparison analysis:
           1. Comparison metric: Select the metrics that you want to use for the comparison analysis.
           2. Validate SQL result sets: Select to direct the SQL Performance Analyzer to detect if the result-sets between the two trials being compared are different. If differences are seen in the result-sets of any SQL statement in the two trials being compared, the SQL Performance Analyzer comparison report will indicate this for every such SQL statement. This is On by default.
        3. Schedule: Select Immediately to start the task now or Later to schedule the task at a later time.
      • Advanced: Enables you to customize the pre-change and post-change SQL trials and comparison configuration. If you select the Advanced option, you must specify the following:
        1. Pre-change trial: Specify details to collect the pre-change SQL performance data:
           1. Trial name: Enter a name for the pre-change SQL trial.
           2. Description: Optionally, enter a description for the pre-change SQL trial.
           3. Execution method: Select one of the following options to determine how the SQL trials are created and what content is generated:
              • Execute SQL: Generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements.
              • Generate plans: Invokes the optimizer to create execution plans only without actually running the SQL statements.
              • Hybrid: Finds SQL statements with plan changes first, and then test-executes the SQL statements with plan changes. This is the default option.
           4. Execution options: Review and make changes to the execution options, if required:
              1. Per SQL time limit: Select one of the following options to specify the time limit for SQL execution during the trial.
                 • 5 mins: Runs each SQL statement in the SQL tuning set for up to 5 minutes and gathers performance data.
                 • Unlimited: Runs each SQL statement in the SQL tuning set to completion and gathers 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.
                 • Custom: Enables you to specify the number of seconds, minutes, or hours.
              2. Use SQL capture compile environment: Select to indicate that the compilation environment should be captured with the SQL statements. This is Off by default.
              3. Maximum number of rows to fetch: Select one of the following options to specify the number of rows to be fetched:
                 • All rows: Fetches all the rows for the SQL statement.
                 • Average: Calculates the number of result rows as the ratio of total rows processed and total executions for each SQL statement in the SQL tuning set.
                 • Automatic: Determines the number of result rows using the value of optimizer_mode parameter of the optimizer environment captured in the SQL tuning set. If the value of optimizer_mode is ALL_ROWS, then all result rows will be fetched. If its value is FIRST_ROWS_n, then n result rows will be fetched.
              4. Disable multiple executions of SQL: Select to execute each SQL statement in the SQL tuning set only once. This is On by default.
              5. Execute full DML: Select to execute DML statement fully, including acquiring row locks and modifying rows. This is On by default.
              6. + Add custom option: Click to add a custom execution option.
        2. Post-change trial: Specify details to collect the post-change SQL performance data:
           1. Trial name: Enter a name for the post-change SQL trial.
           2. Description: Optionally, enter a description for the post-change SQL trial.
           3. Execution method: Select one of the following options to determine how the SQL trials are created and what content is generated:
              • Execute SQL: Generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements.
              • Generate plans: Invokes the optimizer to create execution plans only without actually running the SQL statements.
              • Hybrid: Finds SQL statements with plan changes first, and then test-executes the SQL statements with plan changes. This is the default option.
           4. Use same options as pre-change trial: Select to use the same options specified for the pre-change SQL trial. This is the default option.
           5. Use trial specific options: Select to specify different execution options for the post-change SQL trial.
        3. Comparison: Specify the details for the comparison analysis:
           1. Comparison metric: Select the metrics that you want to use for the comparison analysis. By default, SQL Performance Analyzer uses Elapsed Time as a metric for comparison. Alternatively, you can select another metric for comparison in this field.
           2. Workload impact threshold (%): Enter a value between 0 and 100 to indicate the threshold of a SQL statement change impact on a workload. Statements having workload change impact below the absolute value of this threshold will be considered as unchanged, that is, the performance of those statements will be considered neither improved nor regressed.
           3. SQL impact threshold (%): Enter a value between 0 and 100 to indicate the threshold of a change impact on a SQL statement. Statements having SQL change impact below the absolute value of this threshold will be considered as unchanged, that is, the performance of those statements will be considered neither improved nor regressed.
           4. Compare plan lines: Select one of the following plan line comparison options:
              • Always: Performs a line by line comparison of plans in all scenarios.
              • Automatic: Performs a line-by-line comparison of execution plans only if the computation of the plan hash value for the first SQL trial has changed or the second SQL trial is unavailable.
              • None: Performs a line-by-line comparison of execution plans only if the plan hash value is unknown. This is the default value.
           5. Metric delta threshold (%): Enter a value between 0 and 100 to indicate the threshold of the difference between the SQL performance metric before and after the change.
           6. Validate SQL result sets: Select to direct the SQL Performance Analyzer to detect if the result-sets between the two trials being compared are different. If differences are seen in the result-sets of any SQL statement between the two trials being compared, the SQL Performance Analyzer comparison report will indicate this for every such SQL statement. This is On by default.
           7. + Add custom option: Click to add a custom execution option.
        4. Schedule: Select Immediately to start the task now or Later to schedule the task at a later time.
   3. Click Submit.