SQL Profiles

Submitted by dave on Wed, 02/04/2009 - 16:20

This is a collection of Information About SQL Profiles.
SQL Profiles are useful for controlling execution plans of some Oracle SQL statements. SQL Profiles contain text of a SQL statement and hints that are applied to that statement when it is see by the optimizer. This is helpful when you need to tune code that you do not have access to and cannot change. If you are at the mercy of your calling programs, SQL Profiles may be a tool to help you get some unruly statements to behave without having to involve vendors, developers, and the friction associated with getting the original code changed.

The power to lock in a plan comes with pros and cons. Honestly I try to only use them as a last resort. I have found that statements that can be whipped into submission with a SQL Profile are usually candidates for some intensive object statistics analysis first.

But I do use them from time to time and the information contained in this section of my site has some notes about my common interactions with them.

Note that users manipulating profiles must have ADMINISTER SQL TUNING SET privilege.

References:
Metalink Note 271196.1 "Automatic SQL Tuning - SQL Profiles"

Metalnk Note 457531.1 "How To Move SQL Profiles From One Database To Another Database"