Intermediate SQL Color Coded SQL, UNIX and Database Essays

What is the difference between SQL Profile and SPM Baseline ?

SQL Profiles

and SPM Baselines

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 …

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:

And for SPM Baselines:

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

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.

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

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.

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!