SQL Tuning Sets

A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user.

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 cursor compilation environment

• 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)

A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. SQL Tuning Sets are transportable across databases and can be exported from one system to another, allowing for the transfer of SQL workloads between databases for remote performance diagnostics and tuning. To transport SQL Tuning Sets, use the DBMS_SQLTUNE package procedures.

(Performance Tuning Guide - Ch12.9)

Choosing an Access Path

To choose an access path, the optimizer first determines which access paths are available by examining the conditions in the statement's WHERE clause and its FROM clause. The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan, using the statistics for the index, columns, and tables accessible to the statement. Finally, the optimizer chooses the execution plan with the lowest estimated cost.

When choosing an access path, the optimizer is influenced by the following:

• Optimizer hints: The optimizer's choice among available access paths can be overridden with hints. (Using hints requires changing the code)

• Statistics: For example, if a table has not been analyzed since it was created, and the table statistics show that it is small, then the optimizer uses a full table scan. The LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table reflect the statistics used by the optimizer.

• SQL outlines, SQL baseline plans, and SQL profiles: If these exist they will override the optimizer choices. For more information see the SQL Performance Management lesson.

(Oracle Database 11g: Performance Tuning Ch10.17)