Oracle Cloud Infrastructure Documentation

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.

  1. In the SQL tuning sets section on the Managed database details page, click Create.
  2. On the SQL tuning set information page of the Create SQL tuning set panel:
    1. Provide the following information to create an empty SQL tuning set:
      1. Name: Enter a name for the SQL tuning set.
      2. Owner: Enter the database user name of the owner of the SQL tuning set.
        Note

        The owner's user name is auto-populated if preferred credentials are set in Database Management. For example, if the Advanced diagnostics preferred credential is set, the associated database user will be the owner of the SQL tuning set. For information on preferred credentials, see Set Preferred Credentials.
      3. Description: Optionally, enter a description for the SQL tuning set.
    2. Click Next.
  3. On the Load SQLs page of the Create SQL tuning set panel:
    1. Select one of the following load methods to collect and load SQL statements into the SQL tuning set:
      • Incremental cursor cache: Select this option and specify the following details to select and load active SQL statements from the cursor cache into the SQL tuning set incrementally over a specified period of time.
        1. Capture time (sec): Enter the time period during which active SQL statements will be captured from the shared SQL area.
        2. Repeat interval (sec): Enter the duration of the interval between capturing active SQL statements.
        3. Capture option: Select a capture option to load SQL statements into the SQL tuning set:
          • Insert: Select to add new SQL statements only.
          • Update: Select to update existing SQL statements and ignore any new statements.
          • Merge: Select to add new SQL statements and update existing SQL statements.
        4. Capture mode: Select a capture mode for the Update and Merge capture options:
          • Replace old statistics: Replaces old statistics when the number of executions is greater than the number stored in the SQL tuning set.
          • Accumulate statistics: Adds new values to current values for a SQL statement that is already stored. Note that this mode detects a statement that has aged out, so the final value for statistics is the sum of the statistics of all the cursors under which the SQL statement existed.
        5. Recursive SQL: Select to include recursive SQL statements in the SQL tuning set.
        6. Filter: Enter the SQL predicate to filter the SQL statements that you want to load into the SQL tuning set. If a filter is not specified, only CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and MERGE statements are captured. The SQL predicate is based on the attributes of the SQLSET_ROW.
      • Current cursor cache: Select this option and specify the following details to select and load SQL statements from the cursor cache into the SQL tuning set.
        1. Sort results by: Select up to three metrics to specify the sorting order of the results. This is useful in case the results have more SQL statements than the limits being set.
        2. 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.
        3. 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.
        4. Load option: Select an option to load SQL statements into the SQL tuning set:
          • Insert: Select to add new SQL statements only.
          • Update: Select to update existing SQL statements and ignore any new statements.
          • Merge: Select to add new SQL statements and update existing SQL statements.
        5. Advanced options: Expand this section to use advanced options to control how SQL statements are loaded:
          • Attribute list: Select an option to specify which SQL statement attributes should be included in the SQL tuning set:
            • Basic: Includes all attributes (such as binds and statistics) except SQL plans. This also includes the execution context.
            • Typical: Includes Basic attributes along with SQL plans. This does not include row source statistics and an object reference list.
            • All: Includes all attributes along with the execution context.
          • Commit rows: Enter a number to specify whether SQL statements are committed incrementally or at the end of loading. If 0 is specified in this field, a commit is performed only once at the end of loading. Otherwise, a commit is performed each time the specified number of statements are loaded.
          • Update option: Select an update option to specify how to update existing SQL statements in the SQL tuning set. This selection is considered only for the Update and Merge load options:
            • Replace old statistics: Updates the SQL statement using the new statistics, bind list, object list, and so on. This is the default option.
            • Accumulate statistics: Combines statistics when possible or replaces the existing values with the provided values. The SQL statement statistics that can be accumulated are: elapsed_time, buffer_gets, direct_writes, disk_reads, rows_processed, fetches, executions, end_of_fetch_count, stat_period, and active_stat_period.
          • Update attributes: Select an option to specify which SQL statement attributes should be updated in the SQL tuning set. This selection is considered only for the Update and Merge load options:
            • Basic: Includes statistics and binds only.
            • Typical: Includes Basic attributes along with SQL plans. This does not include row source statistics and an object reference list.
            • All: Includes all attributes along with the execution context.
          • Recursive SQL: Select to include recursive SQL statements in the SQL tuning set.
          • Ignore null attributes: Select to ignore attributes when the new value is NULL.
        6. Filter: Enter the SQL predicate to filter the SQL statements that you want to load into the SQL tuning set. If a filter is not specified, only CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and MERGE statements are captured. The SQL predicate is based on the attributes of the SQLSET_ROW.
      • AWR snapshots: Select this option and specify the following details to select and load SQL statements from AWR snapshots.
        1. Starting snapshot: Enter the ID of the starting snapshot in the range (non-inclusive).
        2. Ending snapshot: Enter the ID of the ending snapshot in the range (inclusive).
        3. Sort results by: Select up to three metrics to specify the sorting order of the results. This is useful in case the results have more SQL statements than the limits being set.
        4. 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.
        5. 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.
        6. Load option: Select an option to load SQL statements into the SQL tuning set:
          • Insert: Select to add new SQL statements only.
          • Update: Select to update existing SQL statements and ignore any new statements.
          • Merge: Select to add new SQL statements and update existing SQL statements.
        7. Advanced options: Expand this section to use advanced options to control how SQL statements are loaded:
          • Attribute list: Select an option to specify which SQL statement attributes should be included in the SQL tuning set:
            • Basic: Includes all attributes (such as binds and statistics) except SQL plans. This also includes the execution context.
            • Typical: Includes Basic attributes along with SQL plans. This does not include row source statistics and an object reference list.
            • All: Includes all attributes along with the execution context.
          • Commit rows: Enter a number to specify whether SQL statements are committed incrementally or at the end of loading. If 0 is specified in this field, a commit is performed only once at the end of loading. Otherwise, a commit is performed each time the specified number of statements are loaded.
          • Update option: Select an update option to specify how to update existing SQL statements in the SQL tuning set. This selection is considered only for the Update and Merge load options:
            • Replace old statistics: Updates the SQL statement using the new statistics, bind list, object list, and so on. This is the default option.
            • Accumulate statistics: Combines statistics when possible or replaces the existing values with the provided values. The SQL statement statistics that can be accumulated are: elapsed_time, buffer_gets, direct_writes, disk_reads, rows_processed, fetches, executions, end_of_fetch_count, stat_period, and active_stat_period.
          • Update attributes: Select an option to specify which SQL statement attributes should be updated in the SQL tuning set. This selection is considered only for the Update and Merge load options:
            • Basic: Includes statistics and binds only.
            • Typical: Includes Basic attributes along with SQL plans. This does not include row source statistics and an object reference list.
            • All: Includes all attributes along with the execution context.
          • Recursive SQL: Select to include recursive SQL statements in the SQL tuning set.
          • Ignore null attributes: Select to ignore attributes when the new value is NULL.
        8. Filter: Enter the SQL predicate to filter the SQL statements that you want to load into the SQL tuning set. If a filter is not specified, only CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and MERGE statements are captured. The SQL predicate is based on the attributes of the SQLSET_ROW.
      • Skip loading: Select this option to create an empty SQL tuning set. You can load SQL statements into the empty SQL tuning set at a later time.
    2. 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.
  4. Optionally, click Show SQL to view the SQL statement that will be executed.
  5. Click Create.
The newly created SQL tuning set is displayed in the SQL tuning sets sections. Initially, its status is Running while the SQL statements are being loaded and once it's successfully created, the status changes to Completed.

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 (Actions) 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.
  • 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:

    1. 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:
        1. Save method: Select Into existing SQL tuning set.
        2. Name: Select the SQL tuning set in which you want to save the SQL statements.
        3. 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:
        1. Save method: Select Into new SQL tuning set.
        2. Name: Enter a name for the SQL tuning set.
        3. Description: Optionally, enter a description for the SQL tuning set.
    2. 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.
    3. Click Save.
  • Click Delete SQLs to delete all SQL statements or those that match the filtering criteria, from the SQL tuning set.