Translate into your own language

Friday, October 27, 2017

What is the order of precedence for Baselines, SQL profiles, Outlines, Hints etc?

The first recommendation is to "keep it simple". In other words,try not to mix up different methods if possible.

If you do have to use different methods, then:

Adding a hint changes the SQL and so you will have a new cursor that won't match up with an existing baseline. Once you add a baseline to a query containing a hint then that baseline can override whatever that hint is intended to do.

If you have a stored outline and a baseline then the Stored outline will take precedence. If you have Outlines and you want to use SPM Baselines, then migrate the outlines into the baseline. When they are migrated they are no longer considered an outline any more so they won't take precedence over the baseline. If you have migrated the stored outline into a baseline then it is just another plan in that baseline.

If a SQL Plan Baseline exists, then a plan generated by a SQL profile will be added to that baseline. Within a baseline, the plan generated by the SQL profile has the same status options as other baseline plans (ENABLED, ACCEPTED, FIXED, REPRODUCED etc.).


If SQL Profile plan is generated and added to a baseline that already contains a different accepted plan, the plan from the profile will not be one of the accepted plans in the baseline. The SQL Profile will be added as an unaccepted plan to the list of accepted and unaccepted candidates for the baseline in the plan history. 

The DBA then will have a choice between which different plans to enable and accept. If the profile plan was the first plan added to the baseline, then it would be automatically accepted as with any other initial plan.

No comments:

Post a Comment