To analyze SQL performance using Enterprise Manager:

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_1241213421833 and SQL_TRIAL_1241213881923 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:

   • 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 generated 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.

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

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 and the Execute icon for the next step is enabled.

6. Once SQL Performance Analyzer has analyzed the pre-change and post-change performance data, generate a SQL Performance Analyzer report that you can use for further analysis.

   On the Guided Workflow page, click the Execute icon for View Trial Comparison Report.

   The SQL Performance Analyzer Task Report page appears. Review the report, as described in "Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager".

To generate and review the SQL Performance Analyzer report:

1. From the Performance menu, select SQL, then SQL Performance Analyzer.

   If the Database Login page appears, then log in as a user with administrator privileges.

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

2. Under SQL Performance Analyzer Tasks, select the task for which you want to view a SQL Performance Analyzer report and click View Latest Report.

   The SQL Performance Analyzer Task Report page appears.

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

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

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

6. To generate an active report, click Save to generate and save the report, or Mail to generate and mail the report as an HTML attachment.

   Active reports include information about the top SQL statements from each category (such as improved, regressed, and changed plans) with pre-change and post-change statistics, explain plans, and task summary.

   For more information, see "About SQL Performance Analyzer Active Reports".

To compare the pre-change and post-change SQL performance data:

1. Call the EXECUTE_ANALYSIS_TASK procedure or function using the following parameters:

   • Set the task_name parameter to the name of the SQL Performance Analyzer task.

   • Set the execution_type parameter to COMPARE PERFORMANCE. This setting will analyze and compare two versions of SQL performance data.

   • Specify a name to identify the execution using the execution_name parameter. If not specified, it will be generated by SQL Performance Analyzer and returned by the function.

   • Specify two versions of SQL performance data using the execution_params parameters. The execution_params parameters are specified as (name, value) pairs for the specified execution. Set the execution parameters that are related to comparing and analyzing SQL performance data as follows:

     • Set the execution_name1 parameter to the name of the first execution (before the system change was made). This value should correspond to the value of the execution_name parameter specified in "Creating a Pre-Change SQL Trial Using APIs".

     • Set the execution_name2 parameter to the name of the second execution (after the system change was made). This value should correspond to the value of the execution_name parameter specified in "Creating a Post-Change SQL Trial Using APIs" when you executed the SQL workload after the system change. If the caller does not specify the executions, then by default SQL Performance Analyzer will always compare the last two task executions.

     • Set the comparison_metric parameter to specify an expression of execution statistics to use in the performance impact analysis. Possible values include the following metrics or any combination of them: elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, optimizer_cost, and io_interconnect_bytes.

     For other possible parameters that you can set for comparison, see the description of the DBMS_SQLPA package in Oracle Database PL/SQL Packages and Types Reference.

   The following example illustrates a function call:

   EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', - 
          execution_type => 'COMPARE PERFORMANCE', -
          execution_name => 'my_exec_compare', -
          execution_params => dbms_advisor.arglist(-
                                'comparison_metric', 'buffer_gets'));
   

2. Call the REPORT_ANALYSIS_TASK function using the following parameters:

   • Set the task_name parameter to the name of the SQL Performance Analyzer task.

   • Set the execution_name parameter to the name of the execution to use. This value should match the execution_name parameter of the execution for which you want to generate a report.

     To generate a report to display the results of:

     • Execution plans generated for the SQL workload, set this value to match the execution_name parameter of the desired EXPLAIN PLAN execution.

     • Execution plans and execution statistics generated for the SQL workload, set this parameter to match the value of the execution_name parameter used in the desired TEST EXECUTE execution.

     • A comparison analysis, set this value to match the execution_name parameter of the desired ANALYZE PERFORMANCE execution.

     If unspecified, SQL Performance Analyzer generates a report for the last execution.

   • Set the type parameter to specify the type of report to generate. Possible values include TEXT (default), HTML, XML, and ACTIVE.

     Active reports provides in-depth reporting using an interactive user interface that enables you to perform detailed analysis even when disconnected from the database or Oracle Enterprise Manager. It is recommended that you use active reports instead of HTML or text reports when possible.

     For information about active reports, see "About SQL Performance Analyzer Active Reports".

   • Set the level parameter to specify the format of the recommendations. Possible values include TYPICAL (default), ALL, BASIC, CHANGED, CHANGED_PLANS, ERRORS, IMPROVED, REGRESSED, TIMEOUT, UNCHANGED, UNCHANGED_PLANS, and UNSUPPORTED.

   • Set the section parameter to specify a particular section to generate in the report. Possible values include SUMMARY (default) and ALL.

   • Set the top_sql parameter to specify the number of SQL statements in a SQL tuning set to generate in the report. By default, the report shows the top 100 SQL statements impacted by the system change.

   To generate an active report, run the following script:

   set trimspool on
   set trim on
   set pages 0
   set linesize 1000
   set long 1000000
   set longchunksize 1000000
   spool spa_active.html
   SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'my_spa_task',
             type => 'active', section => 'all') FROM dual;
   spool off
   

   The following example illustrates a portion of a SQL script that you could use to create and display a comparison summary report in text format:

   VAR rep   CLOB;
   EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
                   'text', 'typical', 'summary');
   SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
   PRINT :rep
   

3. Review the SQL Performance Analyzer report, as described in "Reviewing the SQL Performance Analyzer Report in Command-Line".

If you chose to execute the SQL workload remotely on a separate database, then you should tune any regressions identified by the SQL trials on the remote database, instead of the system where the SQL Performance Analyzer task resides.

To tune regressed SQL statements from a remote SQL trial using APIs:

1. On the system running SQL Performance Analyzer, create a subset of the regressed SQL statements as a SQL tuning set:

   DECLARE
     sqlset_cur  DBMS_SQLTUNE.SQLSET_CURSOR;
   BEGIN
     DBMS_SQLTUNE.CREATE_SQLSET('SUB_STS1', 'test purpose');
    
     OPEN sqlset_cur FOR
       SELECT value(p)
       FROM table(
         DBMS_SQLTUNE.SELECT_SQLPA_TASK(
           task_name  => 'SPA_TASK1',
           execution_name => 'COMP',
           level_filter => 'REGRESSED')) p;
      
     DBMS_SQLTUNE.LOAD_SQLSET('SUB_STS1', sqlset_cur);
    
     CLOSE sqlset_cur;
   END;
   /
   

   Other than 'REGRESSED', you can use other filters to select SQL statements for the SQL tuning set, such as 'CHANGED', 'ERRORS', or 'CHANGED_PLANS'. For more information, see Oracle Database PL/SQL Packages and Types Reference.

2. Create a staging table to where the SQL tuning set will be exported:

   BEGIN
     DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
       table_name  => 'STG_TAB1',
       schema_name => 'JOHNDOE',
       tablespace_name => 'TBS_1',
       db_version => DBMS_SQLTUNE.STS_STGTAB_11_1_VERSION);
   END;
   /
   

   Use the db_version parameter to specify the appropriate database version to where the SQL tuning set will be exported and tuned. In this example, the staging table will be created with a format so that it can be exported to a system running Oracle Database 11g Release 1, where it will later be tuned using SQL Tuning Advisor. For other database versions, see Oracle Database PL/SQL Packages and Types Reference for that release.

3. Export the SQL tuning set into the staging table:

   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
       sqlset_name => 'SUB_STS1', 
       sqlset_owner => 'JOHNDOE', 
       staging_table_name => 'STG_TAB1', 
       staging_schema_owner => 'JOHNDOE', 
       db_version => DBMS_SQLTUNE.STS_STGTAB_11_1_VERSION);
   END;
   /
   

4. Move the staging table to the remote database (where the SQL workload was executed) using the mechanism of choice (such as Oracle Data Pump or database link).

5. On the remote database, import the SQL tuning set from the staging table:

   BEGIN
     DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
       sqlset_name => 'SUB_STS1', 
       staging_table_name => 'STG_TAB1', 
       replace => TRUE);
   END;
   /
   

6. Tune the regressed SQL statements in the SQL tuning set by running SQL Tuning Advisor:

   BEGIN
     sts_name  := 'SUB_STS1';
     sts_owner := 'JOHNDOE';
     tune_task_name := 'TUNE_TASK1';
     tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name  => sts_name, 
                                              sqlset_owner => sts_owner, 
                                              task_name    => tune_task_name);
     EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(:tname, 
                                                 'APPLY_CAPTURED_COMPILENV', 
                                                 'FALSE');
     exec_name := DBMS_SQLTUNE.EXECUTE_TUNING_TASK(tname);
   END;
   /
   

   Note:

   The APPLY_CAPTURED_COMPILENV parameter used in this example is only supported by Oracle Database 11g Release 1 and newer releases. If you are testing a database upgrade from an earlier version of Oracle Database, SQL Tuning Advisor will use the environment variables stored in the SQL tuning set instead.

After tuning the regressed SQL statements, you should test these changes using SQL Performance Analyzer. Run a new SQL trial on the test system, followed by a second comparison (between this new SQL trial and the first SQL trial) to validate your results. Once SQL Performance Analyzer shows that performance has stabilized, implement the fixes from this step to your production system.

Creating SQL plan baselines for regressed SQL statements with plan changes is another option to running the SQL Tuning Advisor. Doing so instructs the optimizer to use the original execution plans for these SQL statements in the future.

To create SQL plan baselines for the original plans:

1. Create a subset of a SQL tuning set of only the regressed SQL statements.

2. Create SQL plan baselines for this subset of SQL statements by loading their plans using the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package, as shown in the following example:

   DECLARE
     my_plans PLS_INTEGER;
   BEGIN
     my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
       sqlset_name => 'regressed_sql');
   END;
   /
   

You can query the following views to monitor SQL Performance Analyzer and view its analysis results:

• The DBA_ADVISOR_TASKS and USER_ADVISOR_TASKS views display descriptive information about the SQL Performance Analyzer task that was created.

• The DBA_ADVISOR_EXECUTIONS and USER_ADVISOR_EXECUTIONS views display information about task executions. SQL Performance Analyzer creates at least three executions to analyze the SQL performance impact caused by a database change on a SQL workload. The first execution collects a pre-change version of the performance data. The second execution collects a post-change version of the performance data. The third execution performs the comparison analysis.

• The DBA_ADVISOR_FINDINGS and USER_ADVISOR_FINDINGS views display the SQL Performance Analyzer findings. SQL Performance Analyzer generates the following types of findings:

  • Problems, such as performance regression

  • Symptoms, such as when the structure of an execution plan has changed

  • Errors, such as nonexistence of an object or view

  • Informative messages, such as when the structure of an execution plan in the pre-change version is different than the one stored in the SQL tuning set

• The DBA_ADVISOR_SQLPLANS and USER_ADVISOR_SQLPLANS views display a list of all execution plans.

• The DBA_ADVISOR_SQLSTATS and USER_ADVISOR_SQLSTATS views display a list of all SQL compilations and execution statistics.

• The V$ADVISOR_PROGRESS view displays the operation progress of SQL Performance Analyzer. Use this view to monitor how many SQL statements have completed or are awaiting execution in a SQL trial. The SOFAR column indicates the number of SQL statements processed so far, and the TOTAL WORK column shows the total number of SQL statements to be processed by the task execution.

You must have the SELECT_CATALOG_ROLE role to access the DBA views.