Configuring the Automatic SQL Tuning Advisor

The following are some configuration tasks that you might want to perform for the Automatic SQL Tuning Advisor:

  • Enable automatic implementation of SQL profile recommendations.

    Automatic implementation is disabled by default.

  • Change the maximum number of SQL profiles implemented during one run of the SQL Tuning Advisor

    When automatic implementation of SQL profile recommendations is enabled, 20 SQL profiles are implemented during a run of the SQL Tuning Advisor, by default.

  • Change the maximum number of SQL profiles that can be implemented overall.

    When automatic implementation of SQL profile recommendations is enabled, a total of 10000 SQL profiles can be implemented by SQL Tuning Advisor, by default.

To configure the Automatic SQL Tuning Advisor:

  1. Log into EM Express as a user who has the EM_EXPRESS_ALL role.

    See "Granting Access to EM Express for Nonadministrative Users" for more information about the EM_EXPRESS_ALL role.

  2. In EM Express, from the Performance menu, select SQL Tuning Advisor.

    The SQL Tuning Advisor page appears, with the Automatic tab showing.

    The SQL tuning task that appears on the Automatic tab is the SYS_AUTO_SQL_TUNING_TASK. This tuning task is created daily by the Automatic SQL Tuning Advisor. The task includes any high-load SQL queries for which the Automatic SQL Tuning Advisor has generated tuning recommendations.

    Description of the illustration auto_sqlt_advisor.gif follows
    Description of the illustration auto_sqlt_advisor.gif
  3. Click the Configuration button.

    The SQL Tuning Settings dialog box appears.

    Description of the illustration sql_tuning_settings_dibox.gif follows
    Description of the illustration sql_tuning_settings_dibox.gif
  4. (Optional) To disable the Automatic SQL Tuning Advisor, remove the check mark for the the Automatic SQL Tuning option.

    Note:

    If you disable the Automatic SQL Tuning option, none of the other options in the SQL Tuning Settings dialog box have any effect.

  5. (Optional) In the Time Limit per Statement (seconds) field, enter the maximum time that SQL Tuning Advisor should take to tune any single SQL statement (in seconds).
  6. (Optional) In the Automatic Implementation of SQL Profiles field, enter a check mark to enable the automatic implementation of SQL profiles, or remove the check mark to disable the automatic implementation of SQL profiles.

    Note:

    If you disable the Automatic Implementation of SQL Profiles option, the settings for the Maximum SQL Profiles Implemented per Execution and Maximum SQL Profiles Implemented (Overall) options have no effect.

  7. (Optional) In the Maximum SQL Profiles Implemented per Execution field, enter the total number of SQL profiles that can be implemented during a single daily run of the SQL Tuning Advisor. The default value is 20.
  8. (Optional) In the Maximum SQL Profiles Implemented (Overall) field, enter the total number of SQL profiles that can be implemented overall. The default value is 10000.
  9. Click OK.

    A confirmation page appears.

See Also:

"About the Automatic SQL Tuning Advisor" for more information about SQL profiles