SQL Profiles in Oracle
Oracle Databases have an interesting concept called SQL profiles. A SQL profile is a set of information for the optimizer, which is specific to a SQL statement. Conceptually, a SQL profile is to a SQL statement what statistics are to a table or an index. The database can use this information to improve execution plans.
- The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, etc.
- The supplemental statistics in the SQL profile.
In short, SQL profiles just guide the optimizer to a better plan.
What is the main use case for SQL profiles?
- DML statements (SELECT, INSERT with a SELECT clause, UPDATE, and DELETE)
- CREATE TABLE statements (only with the AS SELECT clause)
- MERGE statements (the update or insert operations)
Advantages of SQL Profiles:
- Unlike hints and stored outlines, profiles do not tie the optimizer to a specific plan or subplan. Profiles fix incorrect estimates while giving the optimizer the flexibility to pick the best plan in different situations.
- Unlike hints, no changes to application source code are necessary when using profiles.
- The use of SQL profiles by the database is transparent to the user.
Enhancements to SQL profiles in Oracle 11g:
- Automatic SQL Tuning: The database can automatically tune SQL statements by identifying problematic SQL statements and implementing recommendations using SQL Tuning Advisor during system maintenance windows. If the performance improvement improves at least threefold, then the database accepts the SQL profile, but only if the ACCEPT_SQL_PROFILES task parameter is set to TRUE. Otherwise, the automatic SQL tuning reports merely report the recommendation to create a SQL profile.
- Test execution: If a SQL profile is recommended, the database tests the new SQL profile by executing the SQL statement both with and without the SQL profile.
- Parallel query profile recommendation: Starting with Oracle Database 11g Release 2, SQL Tuning Advisor may recommend accepting a profile that uses the Automatic Degree of Parallelism(Auto DOP) feature. A parallel query profile is only recommended when the original plan is serial and when parallel execution can significantly reduce the elapsed time for a long-running query.
How do SQL profiles co-exist with user hints?
Recommended best practices for SQL profiles
- Transport SQL profiles from test to production rather than creating new SQL profiles in productionTest SQL profiles on production in a private category (by default all profiles are in DEFAULT category and when you want to test them, you should change the profile. This technique enables you to test a profile in a restricted environment before making it available to other sessions.Set FORCE_MATCHING parameter to ‘yes’ when creating SQL profiles. This causes SQL Profiles to target all SQL statements which have the same text after normalizing all literal values to bind variables.