1. Performance Tuning Guide
  2. SQL Tuning Set (STS)

SQL Tuning Set (STS)

An SQL Tuning Set (STS) is a database object that you can use as input to tuning tools.

An STS includes:

  • A set of SQL statements
  • Associated execution context, such as user schema, application module name and action, list of bind values, and the environment for SQL compilation of the cursor
  • Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
  • Associated execution plans and row source statistics for each SQL statement (optional)

You can create SQL Tuning Sets by filtering or ranking SQL statements from several sources:

  • The shared SQL area using the SELECT_CURSOR_CACHE Function
  • Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Function
  • Other SQL Tuning Sets using the SELECT_SQLSET Function
  • SQL Performance Analyzer task comparison results using the SELECT_SQLPA_TASK Function
  • SQL Trace files using the SELECT_SQL_TRACE Function
  • A user-defined workload

For creating SPM SQL Baseline, one of the common methods is using to create the STS with following steps using Enterprise Manager or DBMS_SQLTUNE:

  1. Create the SQL Set: DBMS_SQLTUNE.CREATE_SQLSET
  2. Use the DBMS_SQLTUNE. SELECT_CURSOR_CACHE function poll the shared SQL area
  3. Load the SQL Set: DBMS_SQLTUNE.LOAD_SQLSET

    Code sample for DBMS_SQLTUNE.