SQL Plan Management feature

Outline
Well, outlines are more about fixing the plan for a query that optimizer will use. So if there is change in environment,
the plan would still be the same.
There are certain situations where you want the plan to change with change in lets say data. What if your data gets skewed over time.

Profile
Profile is another feature introduced in 10g and can be implemented at SQL level.
Profiles are better than outlines in that they are not fixing a plan through out the life of SQL.
With change in the environment the plans can change even if the profile is used.

Profile is more of a advisors. They give advice to optimizer while executing the query. They provide optimizer with all the precise estimates. Profiles are more abount correcting optimizer to use correct plan when the underlying data is skewed and changed drastically.

The goal is to create best execution plan for the SQL by giving the very precise data to the optimizer. Its the optimizer who will decide what should be the explain plan based on the information/hints it has received from profile.

Baseline

What is SQL Plan Management?

SPM is a new feature introduced in Oracle database 11g which stores the baseline (Plans + Hints) inside database.
SPM allows an Oracle DBA to capture and preserve the most efficient execution plans for any SQL statement,
thus limiting the impact of refreshed optimizer statistics, changes to existing applications, and even upgraded database versions

SPM is basically used for plan stability

even after change in the environment, your queries will use same plan as it was before. More plans could exists in the new environment,
but optimizer is not allowed to use those plans without you confirming the plan change.

SQL Plan baselines works differently. It enforces the plan to be used for a SQL. Baseline does not provide and estimates or it does not help optimizer in anyway. It just tells optimizer to ignore everything and use the plan that we are giving you.
Optimizer just followes the plan provided by baselines. So here baseline is the driving mechanism for getting the correct plan.

SQL baselines are combination of outlines and profiles. It gives the stability of plan similar to outlines and it also allows capturing better plans in case the environment changes.

Not only that, SQL Baselines give the complete control to the DBA on

Whether to capture the new and better plans?
Whether to use the new plans automaticallly without DBA’s intervention.

So DBAs have complete control of the environment now. Far better than profiles and outlines.

Before we check how to use SQL Plan baselines, lets consider the significance of 2 important baseline parameters

optimizer_capture_sql_plan_baselines – Default “FALSE”
1) Using DBMS_SPM.LOAD_PLANS_FROM_SQLSET
2) Using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

optimizer_use_sql_plan_baselines – Default “TRUE”

http://avdeo.com/2011/06/02/oracle-sql-plan-management-part-1/
http://avdeo.com/2011/06/07/oracle-sql-plan-management-%E2%80%93-part-2/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s