A very good article explaining the differences between SQL Profile and SPM Baseline :
http://intermediatesql.com/oracle/what-is-the-difference-between-sql-profile-and-spm-baseline/
Here is the summarized table outlined the defferences:
|
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, 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 |
留言列表
{{ article.title }}