Analyzing SQL Performance Using APIs

After the post-change SQL performance data is built, you can compare the pre-change version of performance data to the post-change version. Run a comparison analysis using the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK procedure or function.

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

See Also: