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.
A SQL profile contains corrections for poor optimizer estimates discovered by the SQL Tuning advisor. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.
The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing 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?
Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements that are good candidates for SQL tuning. Also, it is possible to invoke SQL Tuning Advisor manually for on-demand tuning of one or more SQL statements. To tune multiple statements, one must create a SQL Tuning Set (STS). A SQL tuning set is a database object that stores SQL statements along with their execution context.
SQL Tuning Advisor can recommend a SQL profile the following types of statements:
- 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)
If a profile is recommended it will be in a form of a finding that allows for the profile to be implemented. Once implemented, the database creates the profile and stores it persistently in the data dictionary. If a user issues a statement for which a profile has been built, then the queryoptimizer uses both the environment and the SQL profile to build a well-tuned plan.
SQL Profiles were introduced in Oracle Database 10g and were supposed to guide the Optimizer to a better plan. They do not guarantee the same plan each time the statement is parsed.
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?
SQL profiles, when enabled, remove any user hints from the SQL statement. They can also inadvertently change optimizer configuration; for example, they will bring OPTIMIZER_FEATURES_ENABLE to the newest possible value, unless an older value is shown to be better through test-execution of the SQL statement.
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.
