Header

Friday, September 15, 2017

SQL Profiles Overview

What is a SQL profile?

A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The profile is stored in the data dictionary. The optimizer uses this information during optimization to determine the most optimal plan. In other words, a SQL profile is to a SQL statement what object-level statistics are to a table or index. SQL profiles are generated when we invoke SQL Tuning Advisor.

How SQL Profile works ?

When profiling a SQL statement, SQL Tuning Advisor uses a specific set of bind values as input. The advisor compares the optimizer estimate with values obtained by executing fragments of the statement on a data sample. When significant variances are found, SQL Tuning Advisory bundles corrective actions together in a SQL profile, and then recommends its acceptance. The corrected statistics in a SQL profile can improve optimizer cardinality estimates, which in turn leads the optimizer to select better plans.

A SQL profile contains, among other statistics, a set of cardinality adjustments. The cardinality measure is based on sampling the WHERE clause rather than on statistical projection. A profile uses parts of the query to determine whether the estimated cardinalities are close to the actual cardinalities and, if a mismatch exists, uses the corrected cardinalities. For example, if a SQL profile exists for SELECT * FROM emp WHERE id>50 AND SAL>10, then the profile stores the actual number of rows returned.

SQL profile Key Notes

When choosing plans, the optimizer has the following sources of information:

The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on

The supplemental statistics in the SQL profile

The SQL profile contains supplemental statistics for the entire statement, not individual plans. The profile does not itself determine a specific plan.

Over time, profile content can become outdated. In this case, performance of the SQL statement may degrade. The statement may appear as high-load or top SQL. In this case, the Automatic SQL Tuning task again captures the statement as high-load SQL. You can implement a new SQL profile for the statement.

Internally, a SQL profile is implemented using hints that address different types of problems. These hints do not specify any particular plan. Rather, the hints correct errors in the optimizer estimation algorithm that lead to sub-optimal plans.

What is the difference between SQL profiles, hints, stored outlines, and SQL plan baselines?

Unlike hints and stored outlines, SQL profiles do not tie the optimizer to a specific plan . SQL 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 SQL profiles. The use of SQL profiles by the database is transparent to the user

The goal of SQL plan baselines is to preserve the performance of corresponding SQL statements, regardless of changes in the database. SQL Profiles, on the other hand, were supposed to guide the Optimizer to a better plan. They do not guarantee the same plan each time the statement is parsed.

SQL Profiles and SQL Plan Baselines

You can use SQL profiles with or without SQL plan management. No strict relationship exists between the SQL profile and the plan baseline. If a statement has multiple plans in a SQL plan baseline, then a SQL profile is useful because it enables the optimizer to choose the lowest-cost plan in the baseline.

Listing SQL Profiles

select * from DBA_SQL_PROFILES;

How to implement SQL Profile ?As a rule of thumb, implement a SQL profile recommended by SQL Tuning Advisor. If the database recommends both an index and a SQL profile, then either use both or use the SQL profile only. If you create an index, then the optimizer may need the profile to pick the new index.Some important parameters are as follows:-profile_typeSet this parameter to REGULAR_PROFILE for a SQL profile without a change to parallel execution, or PX_PROFLE for a SQL profile with a change to parallel execution.force_matchThis parameter controls statement matching. An accepted SQL profile is associated with the SQL statement through a SQL signature that is generated using a hash function. This hash function changes the SQL statement to upper case and removes all extra whitespace before generating the signature. Thus, the same SQL profile works for all SQL statements in which the only difference is case and white spaces.By setting force_match to true, the SQL profile additionally targets all SQL statements that have the same text after the literal values in the WHERE clause have been replaced by bind variables. This setting may be useful for applications that use only literal values because it enables SQL with text differing only in its literal values to share a SQL profile. If both literal values and bind variables are in the SQL text, or if force_match is set to false (default), then the literal values in the WHERE clause are not replaced by bind variables.This tutorial assumes the following:

SQL Tuning Advisor task STA_SAL_TASK includes a recommendation to create a SQL profile.