What is the difference between SQL Profile and SPM Baseline ?
- SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement
and SPM Baselines
-----
- 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 hintsthat “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 adog 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.pack_stgtab_sqlprof(staging_table_name => 'profile_stg');
And for SPM Baselines:
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", 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.
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 SQLProfiles 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, SPMBaselines 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 INFO | SQL PROFILES | SPM BASELINES |
|---|---|---|
| What they are | Stored collections of Hints (plus some technical information for the optimizer) | Stored collections of Hints (plus some technical information for the optimizer) |
| Available from | 10g | 11g |
| They affect | Individual SQL | Individual SQL |
| What they do | Adjust Optimizer cardinality estimations | Direct SQL to follow specific execution plan |
| Motto (as far asSQL Plans are concerned) | Be the Best you can be ! | Only the Worthy may Pass ! |
| Managed by PL/SQL package | dbms_sqltune | dbms_spm |
| LOADING | SQL PROFILES | SPM 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 SQLProfile | Take 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 it | YES, any SQL execution plan can be made into SPMbaseline |
| Can they be created automatically ? | YES, by AutoTask analyzing Top SQLs | YES, ifoptimizer_capture_sql_plan_baselines=TRUE |
| Can they be created manually for individual SQL? | YES, bydbms_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 Sets | YES, if optimizer_capture_sql_plan_baselines=TRUE |
| Can they be “group loaded” from SQLs in the shared pool ? | YES, through SQL Tuning Sets | YES, directly |
| Can they be “group loaded” from SQLs in AWRrepository ? | YES, through SQL Tuning Sets | YES, through SQL Tuning Sets |
| Are they “activated” upon creation ? | NO, SQL Profiles need to be explicitly accepted | MAYBE, 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 forSQL Tuning AutoTask | MAYBE, SPM baseline is activated if it is the first baseline captured (for the SQL) |
| Can they be deactivated globally ? | NO | YES, Setoptimizer_use_sql_plan_baselines=FALSE |
| Can they be deactivated locally ? | YES, set sqltune_category | NO |
| Can they be transferred to another database ? | YES | YES |
| BEHAVIOR | SQL PROFILES | SPM BASELINES |
| Can they “fire” for the object in different schema ? | YES | YES |
| Can they “fire” when object has a different structure ? | YES | YES |
| Can they “fire” when table is replaced withMVIEW ? | YES | NO |
| Can they “fire” when some objects (i.e. indexes) used in the original plan are missing for the new object ? | YES | NO |
| LICENSING | SQL PROFILES | SPM BASELINES |
| Available in Standard Edition ? | NO | NO |
| Available in genericENTERPRISE Edition ? | NO, you need to also licenseDIAGNOSTICS and TUNING packs | YES |
