SQL Plan Baseline

Baselines, Execution Plans, SQL Profiles... can be a blackbox for everyday DBAs that are not well grounded in Performance Tuning. Though the aforementioned concepts are very intertwined, this post is dedicated to SQL Plan Baselines only. Baselining is a very tiny little technique in performance tuning but I've used it to eliminate a 4 hour overhead caused by an SQL statement that was using a costly execution plan.

SQL Plan Baseline is a feature in Oracle Database 11g that helps prevent SQL statements that are run multiple times on the database from regressing because a less effective execution plan compared to what was originally in the library cache is used to run the statement. Oracle had/has introduced several major features over several releases designed to help the DBA manage and control the execution plans that the optimizer generates for repetitive statements. These features include stored outlines, SQL Profiles and most recently with Oracle 11g, SQL Plan Baselines.

An SQL Plan Baseline is essentially a set of accepted plans that contain hints, the plan hash value and other plan related data. In addition to the actual baseline, Oracle also maintains a SQL Plan History. This history is a list of all execution plans generated for a statement, including those that have and have not been moved into the SQL Plan Baseline. Acceptable execution plans are moved from the SQL Plan History into the SQL Plan Baseline. The process of moving plans from SQL Plan History to the Baseline is termed as evolving the plan.

SQL Plan Baseline can be captured manually or automatically. To enable automatic capturing set the dynamic parameter OPTIMIZER_CATURE_SQL_PLAN_BASELINE to TRUE. For this method, the first plan generated is accepted into the baseline by default while subsequent plans are placed in the plan history.

Manual loading of SQL baseline can be done through the DBMS_SPM package. See sample script below, you can run this by simply substituting the correct values for SQL_ID and PLAN_HASH_VALUE;

declare

xx PLS_INTEGER;

BEGIN

xx :=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE

    (sql_id=>'0tkgcru9dgby1',

    plan_hash_value=>3942349795,fixed=>'NO');

END;

/

Alternatively you could also create a baseline manually with the output of an SQL Tuning Set using dbms_spm.LOAD_PLANS_FROM_SQLSET.

To confirm if base is created --> 

SET SERVEROUTPUT ON

SET LONG 10000

DECLARE

    xc clob;

BEGIN

xc := dbms_spm.evolve_sql_plan_baseline

    (sql_handle=>'SQL_6a33948b941d18d6',

    plan_name=>'SQL_PLAN_6ncwnjfa1u6fq68bcd9e5',

    VERIFY=>'NO' , COMMIT=>'YES');

dbms_output.put_line(x);

END;

/

PLAN_NAME and SQL_HANDLE can be gotten from the dictionary view DBA_SQL_PLAN_BASELINES which stores information about SQL Plan Baselines. One would think there would be a SQL_ID column in this view but Oracle does not provide this. A method I use to track my baselines is by noting the time it was created and use it to search DBA_SQL_PLAN_BASELINES on the CREATED column. When in doubt, you can verify by crosschecking the SQL_TEXT clob column. 

To review execution plan for an SQL Handle run -->

select * from table(dbms_xplan.display_sql_plan_baseline('SQL_HANDLE'));

Dropping a Baseline --> 

declare

xx PLS_INTEGER;

BEGIN

xx :=dbms_spm.drop_sql_plan_baseline

    (sql_handle=>'SQL_f43d570db14a8066',

    plan_name=>'SQL_PLAN_g8gar1qsnp036d00ac7eb');

END;

/

There are several other concepts such as Evolving Plans and Migrating Plans and SQL Baseline Space Management within this topic, but this is meant to be a quick reference :-)  For further study please lookup http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm


Hope this helps!

Kehinde.