Managing SQL Tuning Sets

A SQL Tuning Set is a database object that includes one or more SQL statements and their execution statistics and context. You can use the set as an input for advisors such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer. You can load SQL statements into a SQL Tuning Set from different SQL sources, such as AWR, the cursor cache, or high-load SQL statements that you identify.

A SQL Tuning Set includes the following:

• A set of SQL statements

• Associated execution context such as:

  • User schema

  • Application module name and action

  • List of bind values

  • Cursor compilation environment

• Associated basic execution statistics such as:

  • Elapsed time and CPU time

  • Buffer gets

  • Disk reads

  • Rows processed

  • Cursor fetches

  • Number of executions and number of complete executions

  • Optimizer cost

  • Command type

• Associated execution plans and row source statistics for each SQL statement (optional)

SQL statements can be filtered using the application module name and action, or any of the execution statistics. In addition, SQL statements can be ranked based on any combination of execution statistics.

SQL Tuning Sets are transportable, enabling SQL workloads to be transferred between databases for remote performance diagnostics and tuning. When high-load SQL statements are identified on a production system, it may not be desirable to perform investigation and tuning activities directly on this system. This feature enables you to transport the high-load SQL statements to a test system, where they can be safely analyzed and tuned. For information about transporting SQL Tuning Sets, see Oracle Database Performance Tuning Guide.

Using Enterprise Manager, you can manage SQL Tuning Sets by doing the following:

• Creating a SQL Tuning Set

• Dropping a SQL Tuning Set

• Transporting SQL Tuning Sets