Thinking Out Loud

September 30, 2015

SQL Profile and Plan Baseline Notes

Filed under: 11g,12c,performance — mdinh @ 1:09 pm

Optimizer with Oracle Database 12c
Oracle White Paper – June 2013
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf

What is the difference between SQL Profiles and SQL Plan Baselines?

The difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can’t.

By Maria Colgan-Oracle on May 08, 2012
https://blogs.oracle.com/optimizer/entry/what_is_the_different_between

Plan Stability using Sql Profiles and SQL Plan Management
Amit Bansal / 20 December, 2011
http://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/

what-is-the-difference-between-sql-profile-and-spm-baseline
3 OCT/10
http://intermediatesql.com/oracle/what-is-the-difference-between-sql-profile-and-spm-baseline/

Comments from Kerry Osborne September 9th, 2011 – 18:31
You didn’t mention the two biggest differences (in my opinion).

1.
Baselines know what plan they are trying recreate and SQL Profiles do not.
SQL Profiles will blindly apply any hints it has and what you get is what you get.
Baselines will apply the hints and if the optimizer gets the plan it was expecting, it uses the plan.
If it doesn’t come up with the expected plan, the hints are thrown away and the optimizer tries again (possibly with the hints from another accepted Baseline).

2.
Profiles have a “force matching” capability that allows them to be applied to multiple statements that differ only in the values of literals.
Think of it as a just in time cursor sharing feature. Baselines do not have this ability to act on multiple statements.

Comments from Kerry Osborne January 25th, 2012 – 16:38

I have seen Baselines be disregarded, even without such extreme conditions as a specified index having been removed.

The reason for this is that Baselines attempt to apply enough hints to limit the choices the optimizer has to a single plan,
but there are situations where the set of hints is not sufficient to actually force the desired plan.

What I mean is that the hints will eliminate virtually all possibility but there still may be a few that are valid and so it’s possible to get a different plan.

In fact, I have even seen situations where the act of creating a Baseline causes the plan to change.

This is clearly not intended behavior but it can happen.

I think I blogged about that once. At any rate, in these cases where the optimizer arrives at a different plan than was expected,
the optimizer uses a plan generated completely without the hints (or possible picks another accepted Baseline and applies it’s hints).

This behavior is very different from SQL Profiles which blindly apply the hints.

I should also note that SQL Profiles can also be created manually with the same set of hints that would be used in a Baseline and there is a MOS note regarding how to do this.

I have blogged about that as well here:
http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/

Updated Oct 19, 2015

Strategies for Minimising SQL Execution Plan Instability
https://orastory.wordpress.com/2015/05/01/strategies-for-minimising-sql-execution-plan-instability

To baseline or not to baseline?
https://mikepargeter.wordpress.com/2015/07/21/to-baseline-or-not-to-baseline/

What you need to know about SQL Plan Management and Auto Capture
https://blogs.oracle.com/optimizer/entry/what_you_need_to_know

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: