Database Administration Tips: Step By Step Guide of How to Use SQL Plan Baseline

Introduction:

In this topic I'll discuss the technical steps of using SQL Plan Baseline (SPM) which its main rule is to pick the best execution plan for SQL statements.

Before I dive into technical details, let me shed some light on SQL Plan Baseline history:

In Oracle 11g Oracle introduced SQL Plan Management as a new feature to manage the execution plan to use only best plans, but Oracle started this effort since Oracle 8i with "Stored Outlines" which can help in fixing "hints" --not the plan itself to a SQL statement. In Oracle 10g, Oracle introduced "SQL Profile" to fix the plan instead of fixing the hint and this worked fine especially if the DBA used a tool like SQLT to create a SQL Profile and copy this profile from one database to another, although Oracle was saying that SQL Profile will choose the best plan among existing plans, but that was not the case all the time, many plans that get chosen by SQL Profile can bring the server on its knees.

In Oracle 11g, Oracle tried to fix this gap by introducing SPM which collects all the execution plans of a statement and classify them into two pools (accepted plans and unaccepted plans) by default any new plan will be added automatically to the Unaccepted plans pool until it get vetted by a process called Evolution process, this process will examine the unaccepted plans and move the ones with the lowest cost from "unaccepted pool" to the "accepted pool". The question here, which plan will be finally used by the optimizer? The optimizer will use the best plan from the "Accepted pool" which is having the lowest cost.

What is your role as a DBA in this complicated process?

1- First, you have to decide whether SQL Plan Baseline will help you with stabilizing your environment or not. From my personal perspective, I used to use SQL Plan Baseline only during the migrations from 11g to higher versions to stabilize the plans in order to avoid having original SQLs to pick wrong plans in the new version.
I believe Oracle needs to invest more in this feature in order to make it mature enough to be relied on.

2- If you decide to use SQL Plan Baseline, then you have to enable it, because it's disabled by default.

3- You have to maintain the accuracy of SQL Plan Baseline by evolving the unaccepted plans or manually fix best plans inside the baseline.

Now, let's jump to the technical steps of how to use SPM:

Enabling SQL Plan BASELINE:

Set the parameter optimizer_capture_sql_plan_baselines to TRUE to start capturing Execution Plans for (Hard Parsed) SQL Statements, it should be enabled long enough to cover most or all of the application activities which varies from one application to another, once you feel that most statements got captured you can set it back to FALSE.

Notes:
- Enable this parameter only when you are sure that the current plans are performing well.
- As long as you keep optimizer_capture_sql_plan_baselines parameter enabled you should closely monitor the SYSAUX tablespace because it will get inflated as more plans will get stored into it.

To ENABLE the Execution Plan capturing for ALL SQL Statements
   SQL>     alter system set optimizer_capture_sql_plan_baselines = true;

-- You can enable SQL Baseline for Specific Statement instead of enabling it database wide: [Without the need of setting optimizer_capture_sql_plan_baselines to TRUE]
   SQL>     SET SERVEROUTPUT ON
        DECLARE
        l_plans_loaded PLS_INTEGER;
        BEGIN
            l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
            sql_id => 'gat6z1bc6nc2d');
        DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
        END;
        /

Notes:
- It's recommended to flush the SHARED POOL to enforce the hard parse of SQL Statements to be considered in the BASELINE:
   SQL>    alter system flush shared_pool;

- Baseline will capture only the statements that been executed at least twice.

- optimizer_capture_sql_plan_baselines parameter should be set to TRUE until you feel that all application workload statements have ran twice or Hard parsed.

- Statements with outlines will not be considered in the Baseline if the parameter use_stored_outlines is set to TRUE.

- In case you want to use SQL Plan Baseline for upgrade purpose but you didn't enable the plan capturing long enough before the upgrade, you still can make use of SPM to stabilize the plans; after the upgrade you can set optimizer_features_enable to the old DB version and set optimizer_capture_sql_plan_baselines = true for sometime till you feel that all application statements have executed twice to be considered in the Plan Baseline then turn it back to False and reset optimizer_features_enable parameter back to the current DB version.

Later to make sure that the Optimizer will use SQL Plan BASELINE all the time instead of using new plans, make sure the optimizer_use_sql_plan_baselines parameter is set to TRUE all the time:

Using SQL Plan BASELINE:

SPM aware optimizer should be enabled to use SQL Plan BASELINES: [Default Enabled]
   SQL>     alter system set optimizer_use_sql_plan_baselines = true;        --Default

Note:
The following line at the end of any SQL statement execution plan indicates that SQL Baseline was used for this statement.
SQL> select * from table(dbms_xplan.display_cursor('&SQLID', 0, 'basic note'));
...
Note
-----
- SQL plan baseline SYS_SQL_PLAN_fcc170b0a62d0f4d used for this statement

-- Display all plans for SQL Statements that are inserted to the SQL Plan BASELINE:
   SQL>     SET LONG 10000 pages 1000 lines 170
        SELECT SQL_HANDLE,sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines
        where sql_text NOT LIKE '%dba_sql_plan_baselines%'
        and sql_text like '%DELETE FROM RESERVATION_SEAT_INFO WHERE RESERVATION_SEAT_INFO.ITEMID%';
   Or:
   -- For a specific SQL statement:
   SQL>     SET LONG 10000
      SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_d90440b9ed3324c0'));

Evolve the SQL Plan BASELINE to consider new plans:

As far optimizer_use_sql_plan_baselines parameter is set to TRUE, new plans will be added to the BASELINE but will not be accepted.
In case you want SQL Plan BASELINE to re-evaluate/consider the new plans if their cost is LOWER, you have to evolve it manually:

-- Evolve For ALL SQL statements in the BASELINE:
   SQL>    SET LONG 10000
        SELECT DBMS_SPM.evolve_sql_plan_baseline() FROM dual;

-- Evolve For Specific SQL statements: using its sql_handle:
   SQL>    SET LONG 10000
        SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9') FROM dual;
        Or
        --
        By Using SQL Tuning Advisor
        SQL> var tname varchar2(30);
        SQL> exec :tname := dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');
        SQL> exec dbms_sqltune.execute_tuning_task(task_name => :tname);
        SQL> select dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;

In 12c:

- The evolve task is automated under automated maintenance task "SYS_AUTO_SPM_EVOLVE_TASK" | As far SQL Tune Advisor is enabled.
- To display the actions taken by the last run of that task:
   SQL>    SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
        SELECT DBMS_SPM.report_auto_evolve_task FROM   dual;
- Due to auto-evolve SPM may accept new least cost plans automatically, If you don't want this to happen:
   SQL>    BEGIN
        DBMS_SPM.set_evolve_task_parameter(
        task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
        parameter => 'ACCEPT_PLANS',
        value     => 'FALSE');
        END;
        /

Manually FIX a specific plan from the Baseline:

In case you want to fix a specific plan manually regardless of Evolve result: [Provide sql_handle & plan_name]
   SQL>     select SQL_HANDLE,sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines where sql_text NOT LIKE '%dba_sql_plan_baselines%';

   SQL>     SET SERVEROUTPUT ON
        DECLARE
          l_plans_altered PLS_INTEGER;
        BEGIN
          l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
            sql_handle      => 'SYS_SQL_7b76323ad90440b9',
            plan_name       => 'SYS_SQL_PLAN_d90440b9ed3324c0',
            attribute_name => 'fixed',
            attribute_value => 'YES');

          DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
        END;
        /

Fix all plans inside the Baseline:

Now the plans are loaded to the baselines but still, the optimizer can add more plans and use them in case it finds better plans.
In case you want to force the optimizer to use the plans inside the baseline regardless, they are good or bad, you can fix them using this command:
SQL> begin
      for i in (select rownum n,plan_name from dba_sql_plan_baselines) loop
       dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'FIXED',attribute_value=>'YES'));
      end loop;
     end;
     /
SQL> select SQL_HANDLE,ENABLED,ACCEPTED,FIXED from DBA_SQL_PLAN_BASELINES;

Or simply: Switch OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE and it won't add more plans to the Baseline and will use whatever exists inside the Baselines:

SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

Manually DROP a specific plan from the Baseline:

-- If plan_name is NULL it will drop all BASELINE plans:
   SQL>    SET SERVEROUTPUT ON
        DECLARE
        l_plans_dropped PLS_INTEGER;
        BEGIN
        l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
        sql_handle => NULL,
        plan_name => 'SYS_SQL_7b76323ad90440b9');

        DBMS_OUTPUT.put_line(l_plans_dropped);
        END;
        /

Manually DISABLE a plan:

   SQL>     variable cnt number;
   SQL>     exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        SQL_HANDLE     => 'SYS_SQL_bf5c9b08f72bde3e',
        PLAN_NAME    => 'SQL_PLAN_byr4v13vkrrjy42949306',
        ATTRIBUTE_NAME     => 'enabled',
        ATTRIBUTE_VALUE => 'NO');

    -- Check the plan status:
   SQL>     SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines;

Maintaning SQL Plan Baseline impact:

-- Check how much space consumed by SPM on SYSAUX tbs:
   SQL>     SELECT occupant_desc, space_usage_kbytes FROM v$sysaux_occupants WHERE   occupant_name='SQL_MANAGEMENT_BASE';

   SQL>    SELECT * FROM (
        SELECT bytes/(1024*1024) MBytes, tablespace_name, segment_name, segment_type
        FROM    dba_segments WHERE owner = 'SYS' ORDER BY bytes DESC)
        WHERE   rownum <= 20;

-- Control the space used by SPM as a percentage of SYSAUX tablespace total size:
   SQL>    SELECT * FROM   dba_sql_management_config;
        BEGIN
          DBMS_SPM.configure('space_budget_percent', 11);    --SPM to use 11% of SYSAUX tablespace size [Default is 10%]
        END;
        /

-- Control the Purge of un-used SPM plans:
   SQL>    SELECT * FROM   dba_sql_management_config;
   -- set the retention of UN-used Plans first in weeks [After which the un-used plans will be purged]:
   SQL>    EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',10);

-- Run the purge manually:
   SQL>    VARIABLE ret NUMBER;
        BEGIN
       :ret := sys.dbms_spm_internal.auto_purge_sql_plan_baseline;
        END;
        /
        PRINT ret;

-- Identifying When Baselines were Last Used:
   SQL>     SELECT FLOOR(EXTRACT(DAY FROM (SYSDATE - last_executed)) / 7) AS "Weeks Since Last Execute",
               COUNT(*) AS "Num Baselines"
        FROM   dba_sql_plan_baselines
        WHERE accepted = 'YES'
        AND    autopurge = 'YES'
        GROUP BY FLOOR(EXTRACT(DAY FROM (SYSDATE - last_executed)) / 7)
        ORDER BY 1;

Misc SPM related tasks:
----
   -- Create SQL Plan Baseline One Time only: [From Cursor Cache]
   SQL>    variable pls number;
        exec :pls := dbms_spm.load_plans_from_cursor_cache( attribute_name => 'SQL_TEXT', attribute_value => 'select%p.prod_name%');

Transfer SQL Plan Baseline between databases:

-- Create Staging table: [Source DB]
   SQL>     BEGIN
          DBMS_SPM.CREATE_STGTAB_BASELINE(
            table_name      => 'SPM_STAGING_TAB',
            table_owner     => 'SCOTT',
            tablespace_name => 'USERS');
        END;
        /

-- Populate the staging table with SQL plan Baselines: [Source DB]
   SQL>    SET SERVEROUTPUT ON
        DECLARE
          l_plans_packed PLS_INTEGER;
        BEGIN
            l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
            table_name      => 'SPM_STAGING_TAB',
            table_owner     => 'SCOTT');

          DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
        END;
        /

-- Export the table: [Source DB]
#expdp \' / as sysdba \' tables=SCOTT.SPM_STAGING_TAB directory=EXPORT_FILES_DBA_BUNDLE dumpfile=SPM_STAGING_TAB.dmp logfile=SPM_STAGING_TAB.log REUSE_DUMPFILES=Y

-- Import the table: [Target DB]
# impdp \'/ as sysdba\' tables=SCOTT.SPM_STAGING_TAB directory=EXPORT_FILES_DBA_BUNDLE dumpfile=SPM_STAGING_TAB.dmp logfile=IMP_SPM_STAGING_TAB.log

-- Load SQL Plan Baselines into Dictionary: [Target DB]
   SQL>     SET SERVEROUTPUT ON
        DECLARE
        l_plans_unpacked PLS_INTEGER;
        BEGIN
        l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
            table_name      => 'SPM_STAGING_TAB',
            table_owner     => 'SCOTT',
            creator         => 'SCOTT');

        DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
        END;
        /