Category: Oracle Tuning

SQL plan baselines are a new feature introduced in Version 11g.

SQL execution plans determine how a query is executed and the path that is taken to retrieve the data. Plans can be evolved or improved over executions and should only change when a better plan can be found.

When a query is hard parsed, the cost based optimizer creates an execution plan. In the absence of any other control, this plan would be used. However, circumstances can lead to different plans being generated with some being much more efficient than others.

The purpose of SQL plan baselines is to make sure that a less optimal execution plan is never used and that changes in the database such as statistics being gathered, upgrades or patches will not lead to slower plans being adopted.

In fact, when SQL plan baselines are in use, a new plan will not become ‘Accepted’ and become available for use untill it has been demonstrated to be more efficient than the old plan.

Here are a couple of good articles on SQL plan baselines and SQL plan management

Difference between SQL plan Baselines and SQL profiles.

SQL plan management

SQL plan baselines are stored in the SYSAUX tablespace and by default can use up to 10% of that tablespace. That figure can be manually set between 1% and 50% using the command – DBMS_SPM.CONFIGURE(‘space_budget_percent’,30); – to set it to use 30% of SYSAUX.

If the space limit is exceeded a message is generated into the alert log.

The length of time that unused plans are kept can be set by setting the retention period. This setting can be between 5 and 523 weeks.

Set it with the DBMS_SPM package: DBMS_SPM.CONFUGURE(‘plan_retention_weeks’,105)

The current settings for ‘space budget’ and ‘retention time’ can be can be seen in the DBA_SQL_MANAGEMENT_CONFIG view:

select * from DBA_SQL_MANAGEMENT_CONFIG;

So, the basic premise is that changes in the execution plan that are an improvement should be accepted but changes for the worse should be rejected.

It is important that untested plans are not used until proven to be an improvement. This is because it is preferable to use an accepted plan that is suboptimal rather than risk a dramatic drop in performance caused by a poor new plan.

Only plans that are set as ‘ACCEPTED’ within the plan baseline can be used.

The first time a piece of SQL is ran, if it has no prior plan in place, it will automatically have the first generated execution plan used and it will become ‘ACCEPTED’ in the baseline.

Additionally, if a change occurs that appears to invalidate all of the ‘ACCEPTED’ plans, the plan with the lowest cost will be used and set as ‘ACCEPTED’ in the same way as when no plans are included.

Subsequently, if a new plan is generated and appears to be an improvement it will be saved but cannot be used until it is accepted.

An automatic task needs to be created and ran that tests all of the candidate execution plans to verify which is the fastest and that one will be set as ‘ACCEPTED’ and will be used as the current baseline. Note that subsequently, there may be more than one ‘ACCEPTED’ plan, so being ‘ACCEPTED’ does not mean that a plan will be used.

To set the database to automatically capture plans you must change the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (it is FALSE by default).

For SQL Plan Baselines to be used, you also need to set the parameter: OPTIMIZER_USE_PLAN_BASELINES = true/false (default true)

You can also load plans manually if you want to force a particular plan into a baseline. This can be done from a number of sources, SQL Tuning Sets, AWR snapshots and the Cursor Cache. Manually added plans are set as ACCEPTED immediately.

To load a plan from a SQL set:

First load into SQL set with CREATE_SQLSET and LOAD_SQLSET then

load the plan from the SQL set:

DBMS_SPM.LOAD_PLANS_FROM_SQLSET

For example:

DECLARE

l_plans_loaded PLS_INTEGER;

BEGIN

l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(

sqlset_name => ‘my_sqlset’);

END; /

Follow a similar procedure to load from the cursor cache:

DECLARE

l_plans_loaded PLS_INTEGER;

BEGIN

l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(

sql_id => ‘1fkh93md0802n’);

END; /

Evolving SQL Plan Baselines

As mentioned earlier, only ‘ACCEPTED’ plans can be uesd and apart from the first one, new plans are not accepted when they are created even if they appear to be more efficient.

There will be no change the the current accepted plans unless a new one has been tested and found to be an improvement. The process of testing plans is called ‘Evolving the Plan Baseline’.

There is a procedure to evolve baselines that must be ran. The normal method is to create a job which calls the procedure and have that job ran during off peak hours.

The procedure is: DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

Fixed Plans

It is possible to set a plan as fixed (by setting the fixed attribute to yes). This essentially stops the baseline from evolving. A fixed plan takes precedence over a non fixed plan and no new plans are automatically added where a fixed plan exists, although you can manually add them, but for them to be used, you must manually alter them to FIXED

Displaying SQL Plan Baselines.

You can view SQL Plan Baselines with the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE procedure

SELECT *

FROM table( dbms_xplan.display_sql_plan_baseline(

sql_handle = > ‘SYS_SQL_109c10fabeeec639’,

format = > ‘basic’)

);

A nice discussion with clear examples can be seen here