Performance Tuning: Oracle SPM Vs. Sql Profiles

The following is the real nice work from

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

This was very good post and awesome write up which help my blog readers too. End to end on sqlprofiles and spm, happy reading, thanks to Maxym.

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’s attach to their target SQLs.

There is no much subtle difference 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 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 have 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.