You can compare two SQL tuning sets using the DBMS_SQLPA package. For example, while using Database Replay, you may have captured a SQL tuning set on the production system during workload capture, and another SQL tuning set on a test system during workload replay. You can then use SQL Performance Analyzer to compare these SQL tuning sets, without having to re-execute the SQL statements. This is useful in cases where you already have another utility to run your workload before and after making the system change, such as a custom script.
When comparing SQL tuning sets, SQL Performance Analyzer uses the runtime statistics captured in the SQL tuning sets to perform its comparison analysis, and reports on any new or missing SQL statements that are found in one SQL tuning set, but not in the other. Any changes in execution plans between the two SQL tuning sets are also reported. For each SQL statement in both SQL tuning sets, improvement and regression findings are reported for each SQL statement—calculated based on the average statistic value per execution—and for the entire workload—calculated based on the cumulative statistic value.
To compare SQL tuning sets using APIs:
Create a SQL Performance Analyzer task:
VAR aname varchar2(30); EXEC :aname := 'compare_s2s'; EXEC :aname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(task_name => :aname);
It is not necessary to associate a SQL tuning set to the task during creation.
Create the first SQL trial and convert the first SQL tuning set:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, -
execution_type => 'convert sqlset', -
execution_name => 'first trial', -
execution_params => DBMS_ADVISOR.ARGLIST(
'sqlset_name', 'my_first_sts', -
'sqlset_owner', 'APPS'));
Specify the name and owner of the SQL tuning set using the SQLSET_NAME and SQLSET_OWNER task parameters. The content of the SQL tuning set will not be duplicated by the SQL Performance Analyzer task. Instead, a reference to the SQL tuning set is recorded in association to the new SQL trial, which in this example is "first trial".
Create a second SQL trial and associate it to the second SQL tuning second to which you want to compare:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, -
execution_type => 'convert sqlset', -
execution_name => 'second trial', -
execution_params => DBMS_ADVISOR.ARGLIST(
'sqlset_name', 'my_second_sts', -
'sqlset_owner', 'APPS'));
Compare the performance data from the two SQL trials (or SQL tuning sets) by running a comparison analysis:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, -
execution_type => 'compare', -
execution_name => 'comparison', -
execution_params => DBMS_ADVISOR.ARGLIST(
'workload_impact_threshold', 0, -
'sql_impact_threshold', 0));
In this example, the workload and per-SQL impact threshold are set to 0% for comparison (the default value is 1%).
After the comparison analysis is complete, generate a SQL Performance Analyzer report using the DBMS_SQLPA.REPORT_ANALYSIS_TASK function.
For information about generating a SQL Performance Analyzer report using APIs, see "Analyzing SQL Performance Using APIs".
Once the report is generated, review it to identify any differences between the contents of the two SQL tuning sets. Example 6-8 shows the Analysis Information and Report Summary sections of a sample report generated by comparing two SQL tuning sets:
Example 6-8 Analysis Information and Report Summary
Analysis Information: ------------------------------------------------------------------------------------------------ Before Change Execution: After Change Execution: --------------------------------------------- --------------------------------------------- Execution Name : first trial Execution Name : second trial Execution Type : CONVERT SQLSET Execution Type : CONVERT SQLSET Status : COMPLETED Status : COMPLETED Started : … Last Updated : … Before Change Workload: After Change Workload: --------------------------------------------- --------------------------------------------- SQL Tuning Set Name : my_first_sts SQL Tuning Set Name : my_second_sts SQL Tuning Set Owner : APPS SQL Tuning Set Owner : APPS Total SQL Statement Count : 5 Total SQL Statement Count : 6 ------------------------------------------------------------------------------------------------ Report Summary ------------------------------------------------------------------------------------------------ Projected Workload Change Impact: ------------------------------------------- Overall Impact : 72.32% Improvement Impact : 47.72% Regression Impact : -.02% Missing-SQL Impact : 33.1% New-SQL Impact : -8.48% SQL Statement Count ------------------------------------------- SQL Category SQL Count Plan Change Count Overall 7 1 Common 4 1 Improved 3 1 Regressed 1 0 Different 3 0 Missing SQL 1 0 New SQL 2 0
As shown in Example 6-8, this report contains two additional categories that are not found in standard SQL Performance Analyzer reports; both categories are grouped under the heading Different:
Missing SQL
This category represents all SQL statements that are present in the first SQL tuning set, but are not found in the second SQL tuning set. In this example, only one SQL statement is missing. As shown in Example 6-9, this SQL statement has:
A sql_id value of gv7xb8tyd1v91
A performance impact on the workload of 33.1% based on the change
No performance impact on the SQL statement based on the change because its "Total Metric After" change value is missing
New SQL
This category represents all SQL statements that are present in the second SQL tuning set, but are not found in the first SQL tuning set. In this example, only two SQL statements are new in the second SQL tuning set. As shown in Example 6-9, these SQL statements have:
sql_id values of 4c8nrqxhtb2sf and 9utadgu5udmh4
A total performance impact on the workload of -8.48%
Missing "Total Metric Before" change values
Example 6-9 shows a table in the sample report that lists the missing and new SQL statements, as well as other top SQL statements as determined by their impact on the workload:
Example 6-9 Top 7 SQL Sorted by Absolute Value of Change Impact on the Workload
Top 7 SQL Sorted by Absolute Value of Change Impact on the Workload ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ | | | Impact on | Total Metric | Total Metric | Impact | Plan | | object_id | sql_id | Workload | Before | After | on SQL | Change | ------------------------------------------------------------------------------------------ | 4 | 7gj3w9ya4d9sj | 41.04% | 812791 | 36974 | 95% | y | | 7 | gv7xb8tyd1v91 | 33.1% | 625582 | | | n | | 2 | 4c8nrqxhtb2sf | -8.35% | | 157782 | | n | | 1 | 22u3tvrt0yr6g | 4.58% | 302190 | 215681 | 28.63% | n | | 6 | fgdd0fd56qmt0 | 2.1% | 146128 | 106369 | 27.21% | n | | 5 | 9utadgu5udmh4 | -.13% | | 2452 | | n | | 3 | 4dtv43awxnmv3 | -.02% | 3520 | 3890 | -47.35% | n | ------------------------------------------------------------------------------------------
Once you have identified a SQL statement of interest, you can generate a report for the SQL statement to perform more detailed investigation. For example, you may want to investigate the SQL statement with the sql_id value of 7gj3w9ya4d9sj and object_id value of 4 because it has the highest impact on the workload:
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => :aname, object_id => 4) rep FROM dual;
Example 6-10 shows a sample report generated for this SQL statement:
Example 6-10 Sample Report for SQL Statement
SQL Details: ----------------------------- Object ID : 4 SQL ID : 7gj3w9ya4d9sj SQL Text : /* my_csts_query1 */ select * FROM emp where empno=2 SQL Execution Statistics (average): --------------------------------------- --------------------------------------------------------- | | Impact on | Value | Value | Impact | | Stat Name | Workload | Before | After | on SQL | --------------------------------------------------------- | elapsed_time | 41.04% | .036945 | .001849 | 95% | | cpu_time | 13.74% | .004772 | .00185 | 61.24% | | buffer_gets | 9.59% | 8 | 2 | 69.01% | | cost | 11.76% | 1 | 1 | 10% | | reads | 4.08% | 0 | 0 | 63.33% | | writes | 0% | 0 | 0 | 0% | | rows | | 0 | 0 | | | executions | | 22 | 20 | | | plan_count | | 3 | 2 | | --------------------------------------------------------- Findings (2): ----------------------------- 1. The performance of this SQL has improved. 2. The structure of the SQL execution plan has changed. Plan Execution Statistics (average): --------------------------------------- ---------------------------------------------------------------------------------- | Statistic Name | Plans Before Change | Plans After Change | ---------------------------------------------------------------------------------- | plan hash value | 440231712 571903972 3634526668 | 571903972 3634526668 | | --------------- | --------- --------- ---------- | --------- ---------- | | schema name | APPS1 APPS2 APPS2 | APPS2 APPS2 | | executions | 7 5 10 | 10 10 | | cost | 2 1 2 | 1 2 | | elapsed_time | .108429 .000937 .00491 | .000503 .003195 | | cpu_time | .00957 .0012 .0032 | .0005 .0032 | | buffer_gets | 18 0 5 | 0 5 | | reads | 0 0 0 | 0 0 | | writes | 0 0 0 | 0 0 | | rows | 0 0 0 | 0 0 | ---------------------------------------------------------------------------------- Execution Plans Before Change: ----------------------------- Plan Hash Value : 440231712 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 87 | 2 | 00:00:01 | | 3 | PX BLOCK ITERATOR | | 1 | 87 | 2 | 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 | 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Plan Hash Value : 571903972 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 1 |00:00:01| | 2 | INDEX UNIQUE SCAN | MY_EMP_IDX | 1 | | 0 | | ---------------------------------------------------------------------------------- Plan Hash Value : 3634526668 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 | | | 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 | 00:00:01 | -------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Executions Plan After Change: ----------------------------- Plan Hash Value : 571903972 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 1 |00:00:01| | 2 | INDEX UNIQUE SCAN | MY_EMP_IDX | 1 | | 0 | | ---------------------------------------------------------------------------------- Plan Hash Value : 3634526668 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 | | | 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 | 00:00:01 | -------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement ----------------------------------------------------------------------------------
The SQL Execution Statistics section shows the average runtime statistics (per execution) of the SQL statement. The data in this table reveals that this SQL statement is present in both SQL tuning sets, but that it has only three execution plans in the first SQL tuning set and two execution plans in the second SQL tuning set. Furthermore, the SQL statement was executed 22 times in the first SQL tuning set, but only 20 times in the second SQL tuning set.
The Plan Execution Statistics section shows runtime statistics per execution plan (or plan hash value). The Plans Before Change column lists plans and their associated execution statistics for the first SQL tuning set; the Plans After Change columns lists these values for the second SQL tuning set. Execution plans structures for both SQL tuning sets are shown at the end of the report.
You can use these sections in the report to identify changes in execution plans between two SQL tuning sets. This is important because changes in execution plans may be a result of test changes that can have a direct impact to performance. When comparing two SQL tuning sets, SQL Performance Analyzer reports execution plan changes when a SQL statement has:
One plan in both SQL tuning sets, but the plan structure is different
More than one plan, and the number of plans in both SQL tuning sets are:
The same, but at least one plan in the second SQL tuning set is different from all plans in the first SQL tuning set
Different
After evaluating the SQL statement and plan changes, determine if further action is required. If the SQL statement has regressed, perform one of the following actions:
Tune the regressed SQL statement, as described in "Tuning Regressed SQL Statements Using APIs"
Create SQL plan baselines, as described in "Creating SQL Plan Baselines Using APIs"