Tuning Regressed SQL Statements From a Remote SQL Trial Using APIs

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.

See Also: