Manage SQL Tuning Sets
You can manage SQL tuning sets and perform tasks such as creating a SQL tuning set and loading SQL statements into SQL tuning sets in Database Management.
A SQL tuning set is a database object that serves as a mechanism to collect, maintain, and access SQL workload data for SQL performance monitoring and tuning. It enables you to group SQL statements and related metadata in a single database object, which you can use as an input to tuning tools such as SQL Tuning Advisor. 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, and 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).
To manage SQL tuning sets in Database Management, go to the Managed database details page and click SQL tuning sets on the left pane under Resources. The list of existing SQL tuning sets for the Managed Database are displayed along with information such as the SQL tuning set's name, status, owner, and the number of statements in the SQL tuning set.
You can perform the following SQL tuning set-related tasks:
- Create a new SQL tuning set and load SQL statements from multiple sources.
- Load SQL statements into an existing SQL tuning set.
- Monitor SQL tuning set details.
- Save SQL statements from a SQL tuning set into another new or existing SQL tuning set.
- Delete a SQL tuning set or statements within a SQL tuning set.
In addition, when creating a SQL tuning set or monitoring SQL tuning set
details, you can specify a SQL predicate to filter the SQL statements. The SQL predicate
is based on the attributes of the SQLSET_ROW
. For information, see
SQLSET_ROW Object Type in Oracle Database PL/SQL Packages and Types Reference.
Privileges Required to Manage SQL Tuning Sets
You must have one of the following privileges:
ADMINISTER SQL TUNING SET
: Allows you to manage the SQL tuning sets that you own.ADMINISTER ANY SQL TUNING SET
: Allows you to manage any SQL tuning set.
Create a SQL Tuning Set
You can create a new SQL tuning set for a Managed Database and load SQL statements from multiple sources.
Once a SQL tuning set is created, you can select it and perform the following tasks:
- Click Load SQLs to load SQL statements into the SQL tuning set. Note that the load methods and steps involved in loading SQL statements into the SQL tuning set are the same as detailed in the SQL tuning set creation procedure.
- Click the Actions icon () and click Delete to delete the SQL tuning set.
Monitor SQL Tuning Set Details
You can monitor SQL tuning set details and the SQL statements in the SQL tuning set.
In the SQL tuning sets section on the Managed database details page, click the name of the SQL tuning set to go to the SQL tuning set details page. The SQL tuning set details page provides SQL tuning set information such as its owner and the total number of SQL statements in the SQL tuning set. Click Delete to delete the SQL tuning set.
In the SQL statements section of the SQL tuning set details page, you can view a maximum of 2000 SQL statements in the SQL tuning set and perform the following tasks:
- Monitor the details of each SQL statement, such as the SQL ID, SQL text, and plan hash value.
- Click Show filter and use the following options to filter the
list of SQL statements in the SQL tuning set:
- Filter query: Enter the SQL predicate to
filter the SQL statements. The SQL predicate is based on the attributes of
the
SQLSET_ROW
. For information, see SQLSET_ROW Object Type in Oracle Database PL/SQL Packages and Types Reference. - Sort results by: Select up to three metrics to sort the SQL statements.
- Result percentage (%): Enter a number to denote the percentage of matching results. The results of the filter query will include the specified percentage of SQL statements with the highest sorting among all matching SQL statements. Note that sorting is based on the metrics specified in the Sort results by drop-down list, and at least one metric must be selected to use this option.
- Result limit: Enter a number to denote the hard limit
of matching results. The results of the filter query will be limited to the
specified number of SQL statements. If any metric is selected in the Sort
results by drop-down list, sorting of results will be applied before
selecting the top SQL statements. A value of
0
indicates that all matching SQL statements should be included in the results. - Recursive SQL: Select to include recursive SQL statements in the search results.
- Filter query: Enter the SQL predicate to
filter the SQL statements. The SQL predicate is based on the attributes of
the
- Click Load SQLs to load SQL statements into the SQL tuning set. For information on the load methods available to collect and load SQL statements into a SQL tuning set, see Create a SQL Tuning Set.
- Click Save as to save the SQL statements in a new
or existing SQL tuning set. You can either save all the SQL statements in the SQL
tuning set or only those that match the defined filtering criteria.
In the Save SQL tuning set panel:
- Provide the details of the SQL tuning set in which you want to
save the SQL statements.
- To save the SQL statements in an existing SQL tuning set:
- Save method: Select Into existing SQL tuning set.
- Name: Select the SQL tuning set in which you want to save the SQL statements.
- Show advanced options: Specify details to select and save SQL statements in another SQL tuning set. For information on the options displayed in this section, see Create a SQL Tuning Set.
- To save the SQL statements in a new SQL tuning set:
- Save method: Select Into new SQL tuning set.
- Name: Enter a name for the SQL tuning set.
- Description: Optionally, enter a description for the SQL tuning set.
- To save the SQL statements in an existing SQL tuning set:
- Select one of the available options in the Credential type drop-down list in the Credentials section to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
- Click Save.
- Provide the details of the SQL tuning set in which you want to
save the SQL statements.
- Click Delete SQLs to delete all SQL statements or those that match the filtering criteria, from the SQL tuning set.