Building a SQL Tuning Set

Once the SQL trace files and mapping table are moved to the SQL Performance Analyzer system, you can build a SQL tuning set using the DBMS_SQLTUNE package.

To build a SQL tuning set:

Copy the SQL trace files to a directory on the SQL Performance Analyzer system.
Create a directory object for this directory.
Use the DBMS_SQLTUNE.SELECT_SQL_TRACE function to read the SQL statements from the SQL trace files.

For each SQL statement, only information for a single execution is collected. The execution frequency of each SQL statement is not captured. Therefore, when performing a comparison analysis for a production system running Oracle Database 10g Release 1 and older releases, you should ignore the workload-level statistics in the SQL Performance Analyzer report and only evaluate performance changes on an execution level.

The following example reads the contents of SQL trace files stored in the sql_trace_prod directory object and loads them into a SQL tuning set.
```
DECLARE
  cur sys_refcursor;
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET('my_sts_9i');
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(DBMS_SQLTUNE.SELECT_SQL_TRACE('sql_trace_prod', '%ora%')) P;
  DBMS_SQLTUNE.LOAD_SQLSET('my_sts_9i', cur);
  CLOSE cur;
END;
/
```

The syntax for the SELECT_SQL_TRACE function is as follows:

  DBMS_SQLTUNE.SELECT_SQL_TRACE ( 
    directory              IN VARCHAR2,
    file_name              IN VARCHAR2 := NULL,
    mapping_table_name     IN VARCHAR2 := NULL,
    mapping_table_owner    IN VARCHAR2 := NULL,
    select_mode            IN POSITIVE := SINGLE_EXECUTION,
    options                IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
    pattern_start          IN VARCHAR2 := NULL,
    parttern_end           IN VARCHAR2 := NULL,
    result_limit           IN POSITIVE := NULL)
  RETURN sys.sqlset PIPELINED;

Table: DBMS_SQLTUNE.SELECT_SQL_TRACE Function Parameters describes the available parameters for the SELECT_SQL_TRACE function.

DBMS_SQLTUNE.SELECT_SQL_TRACE Function Parameters

Parameter	Description
`directory`	Specifies the directory object pointing to the directory where the SQL trace files are stored.
`file_name`	Specifies all or part of the name of the SQL trace files to process. If unspecified, the current or most recent trace file in the specified directory will be used. % wildcards are supported for matching trace file names.
`mapping_table_name`	Specifies the name of the mapping table. If set to the default value of `NULL`, mappings from the current database will be used. Note that the mapping table name is not case-sensitive.
`mapping_table_owner`	Specifies the schema where the mapping table resides. If set to `NULL`, the current schema will be used.
`select_mode`	Specifies the mode for selecting SQL statements from the trace files. The default value is `SINGLE_EXECUTION`. In this mode, only statistics for a single execution per SQL statement will be loaded into the SQL tuning set. The statistics are not cumulative, as is the case with other SQL tuning set data source table functions.
`options`	Specifies the options for the operation. The default value is `LIMITED_COMMAND_TYPE`, only SQL types that are meaningful to SQL Performance Analyzer (such as `SELECT`, `INSERT`, `UPDATE`, and `DELETE`) are returned from the SQL trace files.
`pattern_start`	Specifies the opening delimiting pattern of the trace file sections to consider. This parameter is currently not used.
`pattern_end`	Specifies the closing delimiting pattern of the trace file sections to process. This parameter is currently not used.
`result_limit`	Specifies the top SQL from the (filtered) source. The default value is `MAXSB4`.

Building a SQL Tuning Set

Related Topics