Create a SQL Performance Analyzer Task

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

A SQL Performance Analyzer analysis comprises of at least two SQL trials and a comparison. The SQL trials capture the execution performance of a SQL tuning set under specific environmental conditions such as a database upgrade, and the analysis provides a comparison based on the impact of the environmental differences between the two trials.

Before you create a SQL Performance Analyzer task, you must:

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

    You can create a SQL tuning set in Database Management Diagnostics & Management or in Oracle Enterprise Manager. For information on how to create a SQL tuning set in:

    Note

    You must ensure that the database user used to create the SQL tuning set is used to create the SQL Performance Analyzer task.
  • Ensure that you've created database links if establishing a connection to a remote database. For information on how to create a database link, see Create Database Link in Oracle Database SQL Language Reference.
  • Perform the following steps, if you plan to use a test system separate from your production system.
    1. Set up the test system to match the production environment as closely as possible.
    2. Transport the SQL tuning set to the test system.

To create a SQL Performance Analyzer task:

  1. Go to the SQL 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 (Search) to select a SQL tuning set.
      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.
After one or more SQL Performance Analyzer tasks are created on the SQL Watch home page for the database, you can monitor the task-related information in the following tiles, for the time period selected in the View data drop-down list.
  • Latest comparison report: Displays the task status and the latest comparison for a change type between the pre-change and post-change SQL trials specified in the task. You can click the comparison report (change percentage) link to view the comparison report. For information on the comparison report, see View SQL Performance Analyzer Comparison Reports.
  • Tasks by status: Displays the SQL Performance Analyzer tasks categorized by status in a donut chart. Click the All tasks link to reset the data displayed in the chart.
  • Tasks by change type: Displays the SQL Performance Analyzer tasks categorized by change type in a horizontal bar chart. Click the All tasks link to reset the data displayed in the chart.

The SQL Performance Analyzer tasks are listed in the Tasks section on the SQL Watch home page for the database. If you have a number of tasks, you can use the Change type and Status drop-down lists or the Search by task or description field to filter the tasks. To view additional information such as the description of the task, select an option in the Columns drop-down list.

In the Tasks section, you can:

  • Click the name of the task to view SQL Performance Analyzer task details, executions, and comparison, and perform tasks such as creating trials and comparisons. For information, see Monitor a SQL Performance Analyzer Task.
  • Click the Actions icon (Actions) for the task and perform the following tasks:
    • View: Click to view SQL Performance Analyzer task details.
    • Show latest report: Click to view the latest comparison report generated for the task.
    • Delete: Click to delete the task.
    • Complete: Click to complete the creation of a SQL Performance Analyzer task. The creation of certain SQL Performance Analyzer tasks requires two dbms_scheduler jobs as manual steps must be performed. Once the first job is completed, the task is flagged and once you've performed the required manual steps, click Complete to trigger the execution of the second job, and complete the task.