Automatic SQL Tuning in Oracle Database 11g

g automates the SQL Tuning process by identifying problematic SQLg:

Oracle Database 11

statements, running SQL Tuning Advisor on them, and implementing the resulting SQL profile

recommendations to tune the statement without requiring user intervention. Automatic SQL Tuning

uses the AUTOTASK framework through a new task called “Automatic SQL Tuning” that runs

every night by default. Here is a brief description of the automated SQL tuning process in Oracle

Database 11

Step 1: Based on the AWR Top SQL identification (SQLs that were top in four different time

periods: the past week, any day in the past week, any hour in the past week, or single response time),

Automatic SQL Tuning targets for automatic tuning.

Steps 2 and 3: While the Automatic SQL Tuning task is executing during the maintenance window,

the previously identified SQL statements are automatically tuned by invoking SQL Tuning Advisor.

As a result, SQL profiles will be created for them if needed. However, before making any decision,

the new profile is carefully tested.

Step 4: At any point in time, you can request a report about these automatic tuning activities.

You then have the option of checking the tuned SQL statements to validate or remove the automatic

SQL profiles that were generated.