Intermediate SQL Color Coded SQL, UNIX and Database Essays

3Oct/1011

What is the difference between SQL Profile and SPM Baseline ?

SQL Profiles

Note
-----
   - SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

and SPM Baselines

Note
-----
   - SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement

are both relatively new features of ORACLE Optimizer with Profiles first appearing in version 10 and SPM Baselines in version 11.

Both SQL Profiles and SPM Baselines are designed to deal with the same problem: Optimizer may sometimes produce a very inefficient execution plan, and they are both doing it by essentially abandoning the idea that “all SQLs are created equal”. Instead, another idea is put forward: “Some SQLs are special and deserve individual treatment”.

The way they are implemented, both SQL Profiles and SPM Baselines are:

  • External objects that contain additional “magic” information for the optimizer
  • Attached to individual SQLs and influence them only
  • Built by actually running the SQL and using feedback from runtime engine

What is the “magic” contents that profiles and baselines use to influence SQL execution plans ? It turns out to be nothing more than hints (what else ?). In other words, both SQL Profiles and SPM Baselines are collections of stored hints that “attach” to their target SQLs.

The bottom line so far is that both SPM Baselines and SQL Profiles show no visible differences … both seem to be alike in every respect and you know what they say: if Baseline wags like a dog Profile and barks like a dog Profile, then it must be a dog Profile :-)

So, has ORACLE mislead us into thinking that the two are different (perhaps to collect more license $$) ? Let’s dig dipper and find out …

Is there any difference in contents ?

As we have already established, both Profiles and Baselines are nothing more than stored collections of hints. But what exactly those hints are ?

They are fairly easy to see when we use the “dump them for export to another database” technique.

For Profiles:

EXEC dbms_sqltune.create_stgtab_sqlprof('profile_stg');
EXEC dbms_sqltune.pack_stgtab_sqlprof(staging_table_name => 'profile_stg');

And for SPM Baselines:

var n NUMBER
EXEC dbms_spm.create_stgtab_baseline('baseline_stg');
EXEC :n := dbms_spm.pack_stgtab_baseline('baseline_stg');

Let’s look at the Profile staging table first. The hint contents of a typical profile will look like this:

OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "T"@"SEL$1", "T_N_IDX", SCALE_ROWS=2.156967362e-06)
OPT_ESTIMATE(@"SEL$1", TABLE, "T"@"SEL$1", SCALE_ROWS=2.156967362e-06)

OPT_ESTIMATE here is a good old CARDINALITY hint in disguise (albeit somewhat more useful). Its mechanics are simple: “default” cardinality estimation for, say table T that is coming out of the optimizer is multiplied by SCALE_ROWS coefficient to get to the “real” cardinality.

Let’s now look at hint contents of SPM baselines.

FULL(@"SEL$1" "T"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."N"))

See the difference ? These are the more familiar “directional” hints that are driving ORACLE to choose specific operations during this SQL execution.

In other words, based on hint contents, there are some major differences in SQL influence “mechanics” between SQL Profiles and SPM Baselines:

  • SQL Profiles (“Soft power”) are mostly ADVISORS that work with the Optimizer making its estimations more precise
  • SPM Baselines (“Hard Power”) on the other hand are ENFORCERS. They completely ignore the Optimizer and simply tell the runtime engine what to do

Is there any difference in purpose ?

Now that we’ve seen the actual contents of both SQL Profiles and SPM Baselines, let’s talk about their purpose.

SQL Profiles were designed to correct Optimizer behavior when underlying data does not fit anymore into its statistical (and simplistic) world view. Their goal is to create the absolute best execution plan for the SQL by giving the very precise data to the optimizer. In other words, SQL Profiles are all about “ON THE SPOT” SQL PERFORMANCE.

SPM Baselines, on the other hand are different. They were designed to prevent execution plans from changing uncontrollably and their goal is to only allow execution plans that were proven to be efficient. In other words, SPM Baselines are all about LONG TERM STABILITY.

The bottom line: What’s the same and what’s different ?

We’ve seen a few major differences between SQL Profiles and SPM Baselines, but there are others, of course: how profiles and baselines are created, how they are managed, how they behave under various circumstances etc …

After some (fairly extensive) documentation reading, feature comparison and field testing I’ve summarized the similarities and differences in the table below.

Enjoy!

Basic InfoSQL ProfilesSPM Baselines
What they areStored collections of Hints (plus some technical information for the optimizer)Stored collections of Hints (plus some technical information for the optimizer)
Available from10g11g
They affectIndividual SQLIndividual SQL
What they doAdjust Optimizer cardinality estimationsDirect SQL to follow specific execution plan
Motto (as far as SQL Plans are concerned)Be the Best you can be !Only the Worthy may Pass !
Managed by PL/SQL packagedbms_sqltunedbms_spm
LoadingSQL ProfilesSPM Baselines
How are they created ?Run SQL Tuning task (dbms_sqltune.execute_tuning_task) to analyze existing SQL and IF cardinality is skewed, store it as SQL ProfileTake existing execution plan from SQL that already ran and store it as SPM baseline
Can their creation be forced ?YES, but this is not fully supported. I.e. look how folks from www.oraxperts.com did itYES, any SQL execution plan can be made into SPM baseline
Can they be created automatically ?YES, by AutoTask analyzing Top SQLsYES, if optimizer_capture_sql_plan_baselines=TRUE
Can they be created manually for individual SQL ?YES, by dbms_sqltune.execute_tuning_task()YES, but SQL needs to already have run: dbms_spm.load_plans_from_cursor_cache(sql_id => …)
Can they be captured for the ongoing workload ?YES, through SQL Tuning SetsYES, if optimizer_capture_sql_plan_baselines=TRUE
Can they be “group loaded” from SQLs in the shared pool ?YES, through SQL Tuning SetsYES, directly
Can they be “group loaded” from SQLs in AWR repository ?YES, through SQL Tuning SetsYES, through SQL Tuning Sets
Are they “activated” upon creation ?NO, SQL Profiles need to be explicitly acceptedMAYBE, Baseline is activated if it is the first baseline captured (for the SQL) OR if loaded from cursor cache, AWR etc
Can they be activated automatically ?YES, if accept_sql_profiles is set for SQL Tuning AutoTaskMAYBE, SPM baseline is activated if it is the first baseline captured (for the SQL)
Can they be deactivated globally ?NOYES, Set optimizer_use_sql_plan_baselines=FALSE
Can they be deactivated locally ?YES, set sqltune_categoryNO
Can they be transferred to another database ?YESYES
BehaviorSQL ProfilesSPM Baselines
Can they “fire” for the object in different schema ?YESYES
Can they “fire” when object has a different structure ?YESYES
Can they “fire” when table is replaced with MVIEW ?YESNO
Can they “fire” when some objects (i.e. indexes) used in the original plan are missing for the new object ?YESNO
LicensingSQL ProfilesSPM Baselines
Available in Standard Edition ?NONO
Available in generic ENTERPRISE Edition ?NO, you need to also license DIAGNOSTICS and TUNING packsYES
Comments (11) Trackbacks (2)
  1. Very good post!

  2. Although it hasn’t worked for me you can plans into a Tuning set and then load them into a baseline. That’s how you can get your plans from AWR into a baseline.

  3. Robert, thanks! You are right – I missed this option for profiles. I adjusted the document.

  4. Great job on this. I’ve been looking for a consise answer to the differences, and you pinned it.

  5. 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.

  6. Kerry, thanks – very good points.

    I was always wondering why dbms_xplan would show: SQL profile … used for this statement even though hints in that SQL profile were misspelled or wrong altogether and none made it to the plan.

    Speaking of baselines – the only way I saw it disregarded is when underlying objects (“indexes”) were missing – have you ever seen any other reason for this ?

    And yes, I completely agree on the “force matching” – it is an awesome feature for profiles … just discovered it recently (you learn more as you go along :-) )

  7. Hi Maxym,

    Just stumbled across this post again and noticed you had asked me a question. Sorry I didn’t notice before now.

    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/

  8. Thanks, Kerry.

    That makes perfect sense now :-)

  9. Reading this post again. An excellent article, thanks for taking the time and effort to write it.

  10. Just wish to say your article is as amazing. The clearness in your submit is simply spectacular and that
    i could assume you’re an expert on this subject.
    Well together with your permission allow me to grasp your RSS feed
    to keep up to date with drawing close post. Thanks a million and
    please keep up the gratifying work.

  11. Clarifying the base concept between two topics. Well explained in a simple manner. :)


Leave a comment