Subscriber Count

    464

Subscribe to Posts by Email

Pages

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.

 

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 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, 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 fire 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

 

2 comments to Performance Tuning: Oracle SPM Vs. Sql Profiles

  • seshu

    Hi Geek DBA,

    Could you please post some articles on SQl performance analyzer and SQL tuning advisor

    Regards,
    Seshu

    • Hello Kumar,

      I would for sure, before to that,

      SPA is basically a comparision tool for sql statements performance, Its analyzes a group of sql belongs to a tuning set or from cursor cache or from AWR and then compare them.

      SQL Tuning set is collection of sql statements, assigned to a tuning task and get recommendations. not the comparision.

      If you have two sets of sql tuning sets , you can create a task with SPA and compare those tuning sets and get recommendations.

      I am writing the same will post in a day or two.

      -Thanks Geek DBA

      On Wed, Mar 13, 2013 at 3:23 PM, Geek DBAGeek DBA

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>