You want to run SQL Tuning Advisor statements that are not captured by ADDM, AWR, and are
not in the library cache.
What is the prerequisite?
A.
Enable SQL plan management
B.
Create a SQL plan baseline for each query
C.
Create a SQL Tuning Set (STS) containing the SQL statements
D.
Gather statistics for objects used in the application
Explanation:
You can use an STS as input to SQL Tuning Advisor, which performs automatic
tuning of the SQL statements based on other user-specified input parameters.
Note:
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. You can
load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared
SQL area, or customized 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).
Reference: Oracle Database Performance Tuning Guide, Managing SQL Tuning Sets