Tuning Regressed SQL Statements Using APIs

After reviewing the SQL Performance Analyzer report, you should tune any regressed SQL statements that are identified after comparing the SQL performance. If there are large numbers of SQL statements that appear to have regressed, you should try to identify the root cause and make system-level changes to rectify the problem. In cases when only a few SQL statements have regressed, consider using the SQL Tuning Advisor to implement a point solution for them, or creating SQL plan baselines to instruct the optimizer to select the original execution plan in the future.

To tune regressed SQL statements using APIs:

Create a SQL tuning task for the SQL Performance Analyzer execution by using the CREATE_TUNING_TASK function in the DBMS_SQLTUNE package:
```
BEGIN
  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    spa_task_name => 'my_spa_task',
    spa_task_owner => 'immchan',
    spa_compare_exec => 'my_exec_compare');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(spa_task_name => 'my_spa_task');
END;
/
```
This example creates and executes a SQL tuning task to tune the SQL statements that regressed in the compare performance execution named my_exec_compare of the SQL Performance Analyzer task named my_spa_task. In this case, it is important to use this version of the CREATE_TUNING_TASK function call. Otherwise, SQL statements may be tuned in the environment from the production system where they were captured, which will not reflect the system change.

Note:

If you chose to execute the SQL workload remotely on a separate database, you should not use this version of the CREATE_TUNING_TASK function call to tune regressed SQL statements. Instead, you should tune any regressions identified by the SQL trials on the remote database, because the application schema is not on the database running SQL Performance Analyzer. Therefore, you need to run SQL Tuning Advisor on the database where the schema resides and where the change was made. For more information, see "Tuning Regressed SQL Statements From a Remote SQL Trial Using APIs".

Table 6-1 lists the SQL Performance Analyzer parameters that can be used with the DBMS_SQLTUNE.CREATE_TUNING_TASK function.

Table 6-1 CREATE_TUNING_TASK Function SQL Performance Analyzer Parameters

Parameter	Description
`SPA_TASK_NAME`	Name of the SQL Performance Analyzer task.
`SPA_TASK_OWNER`	Owner of the specified SQL Performance Analyzer task. If unspecified, this parameter will default to the current user.
`SPA_COMPARE_EXEC`	Execution name of the compare performance trial for the specified SQL Performance Analyzer task. If unspecified, this parameter defaults to the most recent execution of the `COMPARE PERFORMANCE` type for the given SQL Performance Analyzer task.

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.

Starting with Oracle Database 11g Release 2, SQL Tuning Advisor performs an alternative plan analysis when tuning a SQL statement. SQL Tuning Advisor reviews the execution history of the SQL statement, including any historical plans stored in the Automatic Workload Repository. If SQL Tuning Advisor finds alternate plans, it allows you to choose a specific plan and create a plan baseline to ensure that the desired execution plan is used for that SQL statement.