- 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 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.
EXEC dbms_sqltune.pack_stgtab_sqlprof(staging_table_name => 'profile_stg');
And for SPM Baselines:
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 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.
|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)|
|They affect||Individual SQL||Individual SQL|
|What they do||Adjust Optimizer cardinality estimations||Direct 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 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 SQL Profile||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 SPM baseline|
|Can they be created automatically ?||YES, by AutoTask analyzing Top SQLs||YES, 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 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 AWR repository ?||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 for SQL Tuning AutoTask||MAYBE, SPM baseline is activated if it is the first baseline captured (for the SQL)|
|Can they be deactivated globally ?||NO||YES, Set optimizer_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 with MVIEW ?||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 generic ENTERPRISE Edition ?||NO, you need to also license DIAGNOSTICS and TUNING packs||YES|