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:

1. Copy the SQL trace files to a directory on the SQL Performance Analyzer system.

2. Create a directory object for this directory.

3. 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 8-1 describes the available parameters for the SELECT_SQL_TRACE function.