About SQL Profiles and SQL Plan Baselines

A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The SQL profile contains supplemental statistics for the entire statement, not individual plans. The profile does not itself determine a specific plan. A SQL profile contains, among other statistics, a set of cardinality adjustments.

What exactly is this ‘supplemental statistics‘ in the SQL profile ?
You can get a detailed information of what it is in the blog : What Are SQL Profiles and why do we need them

A SQL profile is a set of auxiliary information specific to a SQL statement. A SQL profile contains corrections for suboptimal optimizer estimates discovered during Automatic SQL Tuning.
This information can improve optimizer estimates for cardinality, which is the number of rows that is estimated to be or actually is returned by an operation in an execution plan, and selectivity. These improved estimates lead the optimizer to select better plans.

SQL profiles are also implemented using hints, but these hints do not specify any specific plan. Rather, the hints correct miscalculations in the optimizer estimates that lead to suboptimal plans. For example, a hint may correct the cardinality estimate of a table.

Because a profile does not constrain the optimizer to any one plan, a SQL profile is more flexible than a SQL plan baseline.

Differences Between SQL Plan Baselines and SQL Profiles
Both SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans. Both profiles and baselines are internally implemented using hints

SQL plan baselines are proactive, whereas SQL profiles are reactive.

SQL plan baselines prevent the optimizer from using suboptimal plans in the future.

SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.

A SQL plan baseline is a set of accepted plans. Each plan is implemented using a set of outline hints that fully specify a particular plan.

Additional Caveats:

• Stored Outlines:  Stored outlines will same an existing execution plan and force it to be used.  Stored outlines can force a plan for a single SQL, or for an entire database.

• SQL Hints:  Oracle hints are optimizer directives that can be used to force Oracle to always use the same execution plan for a SQL statement.

• SQL Profiles:  SQL profiles can be used to force a part of an execution plan while allowing other areas to retain the ability to change as the optimizer statistics change.

Reference :
Database Performance Tuning Guide 12.1
Automatic SQL Tuning in Oracle Database 11g Release 1
Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)
What Are SQL Profiles and why do we need them

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s