A SQL Tuning Set --- STS --- is a set of sql statements together with their processing context.
The input sources of a STS are
1. Individual SQL statements grouped together by the DBA
2. The cursor cache
3. AWR statistical data ( snapshots , baselines )
4. A combination of cursor cache and some individual statements added
5. A user defined workload (Statements inserted in a workload table)
The idea is that we package some SQL toghether with execution statistics. We have something changed in the processing context ( change parameters ) have the STS executed again and have a comparison done. Is the new processing context better, faster ?
The package dbms_sqltune can be used to create, update, delete SQL tuning Sets. Alternatively the Database Control offers us a grafical interface.
STEP 1 : Consider the performance TAB of the Database Control
STEP 2 : On the performance tab click on SQL Tuning Sets
STEP 3 : Create a new SQL Tuning Set
STEP 4 : Select the data source : Cursor Cache, AWR snapshots, AWR Baseline or a User Defined Workload
STEP 5 : optionally apply filters at will
STEP 6 : Create a SQL Tuning Set as a scheduled task and have it executed
STEP 7 : Throw an eye on the DBMS_SQLTUNE syntax
STEP 8 : Note that once the SQL Tuning Set has been created it can be used as an input source for the SQL Access Advisor or the SQL Tuning Advisor. We select here the option SQL Performance Analyzer
STEP 9 : drill down and add optionally other SQL statements, remove optionally some SQL statements
STEP 10 : View the sql statement in detail
STEP 11 : Once the SQL Performance Analyzer chosen there is the option to choose between Parameter Change or Guided Workflow
STEP 12 : Select the parameter you want to change
STEP 13 : Take a look to the comparison reports after execution. you can choose several comparisons ( buffer gets, elapsed time, disk reads, ... )