Monday 4 May 2015

Difference between SPM and SQL Profiles



Difference between SPM and SQL Profiles :-

Below are some valid points regarding the differences of the above :-

SPM baselines :

# Enforce specific plans and Guarantee plan stability
# Contain multiple plans
# Can record new plans for potential improvement
# Only matches a whole SQL statement in its entirety
# Only accepted plans are used

A SQL Profile is generated by SQL Tuning Advisor and:

# Does not enforce a specific plan
# Does not guarantee plan stability
# Does not contains a plan at all, whereas SPM can contain multiple plans
# Is a collection of directives (generated by STA based on empirical information) which can be applied across many SQL statements to influence the optimizer to hopefully produce a better plan.
# Because no plans are recorded, if there is a change that results in a new, better, plan, then that plan would be lost if the change was backed out.
# Can be configured to match part of multiple SQL statement (ie can apply to SQL strings which contain all the same characters)
# If there is already a SQL plan baseline in place when a SQL Profile is created, the new plan is added to the baseline

No comments:

Post a Comment