Managing SQL Profiles

A SQL profile is a set of auxiliary information that is built during automatic tuning of a SQL statement. A SQL profile is to a SQL statement what statistics are to a table.

When running a SQL Tuning Advisor task with a limited scope, the optimizer makes estimates about cardinality, selectivity, and cost that are sometimes significantly off, resulting in poor execution plans. To address this problem, consider running a SQL Tuning Advisor task with a comprehensive scope to collect additional information using sampling and partial execution techniques into a SQL profile. The database can use the profile to verify and, if necessary, adjust optimizer estimates.

During SQL profiling, the optimizer uses the execution history of the SQL statement to create appropriate settings for optimizer parameters. After SQL profiling completes, the optimizer uses the information in the SQL profile and regular database statistics to generate execution plans. The additional information enables the database to produce well-tuned plans for corresponding SQL statements.

After running a SQL Tuning Advisor task with a comprehensive scope, a SQL profile may be recommended. If you accept the recommendation, then the database creates the SQL profile and enables it for the SQL statement.

In some cases, you may want to disable a SQL profile. For example, you may want to test the performance of a SQL statement without using a SQL profile to determine if the SQL profile is actually beneficial. If the SQL statement is performing poorly after the SQL profile is disabled, then you should enable it again to avoid performance degradation. If the SQL statement is performing optimally after you have disabled the SQL profile, then you may want to remove the SQL profile from your database.

To enable, disable, or delete a SQL profile: 

1. On the Performance page, click Top Activity.

   The Top Activity page appears.

2. Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.

   The SQL Details page appears.

3. Click the Plan Control tab.

   A list of SQL profiles is displayed under SQL Profiles and Outlines.

4. Select the SQL profile you want to manage. Do one of the following:

   • To enable a SQL profile that is disabled, click Disable/Enable.

   • To disable a SQL profile that is enabled, click Disable/Enable.

   • To remove a SQL profile, click Delete.

   A confirmation page appears.

5. Click Yes to continue, or No to cancel the action.