Step by step - What is SQL plan baseline, how to create and use it

Step by step - What is SQL plan baseline, how to create and use it

In Oracle Database 11g, using the new SQL plan management feature, we can now examine how execution plans change over time, have the database verify new plans by executing them before using them, and gradually evolve better plans in a controlled manner.

SQL Plan Management

When SQL plan management is enabled, the optimizer stores generated execution plans in a special repository, the SQL management base. All stored plans for a specific SQL statement are said to be part of a plan history for that SQL statement.

Some of the plans in the history can be marked as “accepted.”When the SQL statement is reparsed, the optimizer considers only the accepted plans in the history. This set of accepted plans for that SQL statement is called a SQL plan baseline , or baseline for short.

The optimizer still tries to generate a better plan, however. If the optimizer does generate a new plan, it adds it to the plan history but does not consider it while reparsing the SQL, unless the new plan is better than all the accepted plans in the baseline. Therefore, with SQL plan management enabled, SQL statements will never suddenly have a less efficient plan that results in worse performance.

With SQL plan management, we can examine all the available plans in the plan history for a SQL statement, compare them to see their relative efficiency, promote a specific plan to accepted status, and even make a plan the permanent (fixed) one.

Capture

The capture function of SQL plan management captures the various optimizer plans used by SQL statements. By default, capture is disabled—that is, SQL plan management does not capture the history for the SQL statements being parsed or reparsed.

First, we enable the baseline capture in the session:

alter session

set optimizer_capture_sql_plan_baselines = true;

Now all the SQL statements executed in this session will be captured, along with their optimization plans, in the SQL management base. Every time the plan changes for a SQL statement, it is stored in the plan history.

Using Baselines

With the SQL plan baselines captured, we now enable the optimizer to use them:

alter session set

optimizer_use_sql_plan_baselines = true;

With baseline use enabled, when the optimizer reparses a SQL statement, it examines the plans stored in the baseline for that SQL statement and chooses the best among them. This is where the most important benefit of baselines comes in. The optimizer also still reparses the SQL statements—the presence of a baseline does not prevent that—and if the newly generated plan is not found in the plan history of the SQL, it will be added, but not as “accepted.”So, if the newly generated plan is worse, the performance of the SQL will not be affected, because the plan is not used. However, in some cases, we may decide that the new plan is better, based on your knowledge of the data distribution or the application logic. For instance, suppose the plan was captured when the table was practically empty, making the optimizer quite appropriately choose an index scan. But we know that the application populates the table before invoking the SQL statement later and that a full table scan will actually be better for the plan in the long run. In such a case, we can examine the new plan later, and if it is better, we can accept it—after which the optimizer will consider it. That‘s why we get the best of both worlds: a good plan is always used, but if the optimizer generates a better one, it will be available for comparison.

If we don‘t want to use plans in the baseline for a SQL statement, we can use the following statement in the session prior to calling the SQL statement to disable the use of baselines:

alter session set

optimizer_use_sql_plan_baselines = false;

Evolving SQL Plan Baselines

Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.

SET LONG 10000

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9')

FROM   dual;

Altering Plan Baselines

The ALTER_SQL_PLAN_BASELINE function allows the following attributes of a specific plan, or all plans within a baseline to be altered:

• enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
• fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
• autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
• plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
• description : Used to amend the SQL plan description, up to a maximum of 30 character.