SQL Plan Management in Oracle Database 11g Release 1

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

How Does SQL Plan Management Work?

When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline. If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Oracle call this a "conservative plan selection strategy", as the optimizer preferentially uses a tried an tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.

The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default. Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.

Automatic Plan Capture

The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.

SQL> SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

System altered.

SQL>

I would advise doing considerable testing before using automatic plan capture in a production environment.

Manual Plan Loading

Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.

The following code uses the LOAD_PLANS_FROM_SQLSET function to load all statements in an existing SQL tuning set into SQL baselines. A filter can be applied to limit the SQL statements loaded if necessary.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    sqlset_name => 'my_sqlset');
END;
/

The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache. There are four overloads, allowing statements to be identified by a number of criteria, including: SQL_ID, SQL_TEXT, PARSING_SCHEMA_NAME, MODULE and ACTION. The following example identifies the SQL statement using the SQL_ID.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1fkh93md0802n');
END;
/

The return value of the LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM_CURSOR_CACHE functions indicates the number of plan loaded by the function call.

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;

Step-By-Step Example

This section represents a step-by-step example of using SQL plan baselines. It uses manual loading, so switch off automatic plan capture.

CONN sys/password@db11g AS SYSDBA
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

Create and populate a test table.

CONN test/test@db11g

CREATE TABLE spm_test_tab (
  id           NUMBER,
  description  VARCHAR2(50)
);

DECLARE
  TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE;
  l_tab t_tab := t_TAB();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;
  
  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO spm_test_tab VALUES l_tab(i);
  
  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

Query the table using an unindexed column, which results in a full table scan.

SET AUTOTRACE TRACE

SELECT description
FROM   spm_test_tab
WHERE  id = 99;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=1 Bytes=24)
   1    0   TABLE ACCESS (FULL) OF 'SPM_TEST_TAB' (TABLE) (Cost=13 Card=1 Bytes=24)

Identify the SQL_ID of the SQL statement by querying the V$SQL view.

CONN sys/password@db11g AS SYSDBA

SELECT sql_id
FROM   v$sql
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND    sql_text NOT LIKE '%EXPLAIN%';

SQL_ID
-------------
gat6z1bc6nc2d

1 row selected.

SQL>

Use this SQL_ID to manually load the SQL plan baseline.

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;
/
Plans Loaded: 1

PL/SQL procedure successfully completed.

SQL>

The DBA_SQL_PLAN_BASELINES view provides information about the SQL plan baselines. We can see there is a single plan associated with our baseline, which is both enabled and accepted.

CONN sys/password@db11g AS SYSDBA

SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9b65c37c8  YES YES

1 row selected.

SQL>

Flush the shared pool to force another hard parse, create an index on the ID column, then repeat the query to see the affect on the execution plan.

CONN sys/password@db11g AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;

CONN test/test@db11g

CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);
EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

SET AUTOTRACE TRACE

SELECT description
FROM   spm_test_tab
WHERE  id = 99;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=13 Card=1 Bytes=24)
   1    0   TABLE ACCESS (FULL) OF 'SPM_TEST_TAB' (TABLE) (Cost=13 Card=1 Bytes=24)

Notice the query doesn't use the newly created index, even though we forced a hard parse. Looking at the DBA_SQL_PLAN_BASELINES view we can see why.

CONN sys/password@db11g AS SYSDBA

SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SYS_SQL_7b76323ad90440b9';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9b65c37c8  YES YES
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9ed3324c0  YES NO

2 rows selected.

SQL>

The SQL plan baseline now contains a second plan, but it has not yet been accepted.

Note: If you don't see the new row in the DBA_SQL_PLAN_BASELINES view go back and rerun the query from "spm_test_tab" until you do. It sometimes takes the server a few attempts before it notices the need for additional plans.

The following query uses the EVOLVE_SQL_PLAN_BASELINE function to evolve the SQL plan baseline and output the associated report.

CONN sys/password@db11g AS SYSDBA

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9')
FROM   dual;


DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_7b76323ad90440b9
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
  COMMIT     = YES

Plan: SYS_SQL_PLAN_d90440b9ed3324c0
-----------------------------------
  Plan was verified: Time used .05 seconds.
  Passed performance criterion: Compound improvement ratio >= 15.4.
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
  Rows Processed:                 1              1
  Elapsed Time(ms):               2              0
  CPU Time(ms):                   2              0
  Buffer Gets:                   46              3             15.33
  Disk Reads:                     0              0
  Direct Writes:                  0              0
  Fetches:                        0              0
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.


1 row selected.

SQL>

The DBA_SQL_PLAN_BASELINES view shows the second plan as been accepted.

CONN sys/password@db11g AS SYSDBA

SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SYS_SQL_7b76323ad90440b9';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9b65c37c8  YES YES
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9ed3324c0  YES YES

2 rows selected.

SQL>

Repeating the earlier test shows the more efficient plan is now available for use.

CONN test/test@db11g

SET AUTOTRACE TRACE

SELECT description
FROM   spm_test_tab
WHERE  id = 99;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=24)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SPM_TEST_TAB' (TABLE) (Cost=2 Card=1 Bytes=24)
   2    1     INDEX (RANGE SCAN) OF 'SPM_TEST_TAB_IDX' (INDEX) (Cost=1 Card=1)

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.

The following shows a specific plan being marked as fixed.

CONN sys/password@db11g AS SYSDBA

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;
/
Plans Altered: 1

PL/SQL procedure successfully completed.

SQL>

These attributes are present in the DBA_SQL_PLAN_BASELINES view.

Displaying SQL Plan Baselines

In addition to querying the DBA_SQL_PLAN_BASELINES view, information about SQL plan baselines is available via the DBMS_XPLAN package. The DISPLAY_SQL_PLAN_BASELINE table function displays formatted information about a specific plan, or all plans in the SQL plan baseline in one of three formats (BASIC, TYPICAL or ALL). The following example displays the default format (TYPICAL) report for a specific plan.

SET LONG 10000

SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_d90440b9ed3324c0'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_7b76323ad90440b9
SQL text: SELECT description FROM   spm_test_tab WHERE  id = 99
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_d90440b9ed3324c0
Enabled: YES     Fixed: YES     Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3121206333

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("ID"=99)

25 rows selected.

SQL>

SQL Management Base

The SQL management base resides in the SYSAUX tablespace and stores SQL plan baselines, as well as statement logs, plan histories and SQL profiles. Space usage is controlled by altering two name-value attributes using the CONFIGURE procedure of the DBMS_SPM package.

space_budget_percent (default 10) : Maximum size as a percentage of SYSAUX space. Allowable values 1-50.
plan_retention_weeks (default 53) : Number of weeks unused plans are retained before being purged. Allowable values 5-523 weeks.

The current settings are visible using the DBA_SQL_MANAGEMENT_CONFIG view.

SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        10
PLAN_RETENTION_WEEKS                        53

2 rows selected.

SQL>

The following example shows both values being reset.

BEGIN
  DBMS_SPM.configure('space_budget_percent', 11);
  DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/

SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        11
PLAN_RETENTION_WEEKS                        54

2 rows selected.

SQL>

Transferring SQL Plan Baselines

The DBMS_SPM package provides functionality for transferring SQL plan baselines between databases. First, a staging table must be created in the source database using the CREATE_STGTAB_BASELINE procedure.

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    tablespace_name => 'USERS');
END;
/

The PACK_STGTAB_BASELINE function exports the SQL plan baselines to the staging table. There are several parameters allowing you to limit amount and type of data you export. The following example exports all SQL plan baselines.

SET SERVEROUTPUT ON
DECLARE
  l_plans_packed  PLS_INTEGER;
BEGIN
  l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST');

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

PL/SQL procedure successfully completed.

SQL>

The staging table is then transferred to the destination database using data pump or the original export/import utilities. Once in the destination database, the SQL plan baselines are imported into the dictionary using the UNPACK_STGTAB_BASELINE function. Once again, there are several parameters allowing you to limit amount and type of data you import. The following example imports all SQL plan baselines owned by the user "TEST".

SET SERVEROUTPUT ON
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    creator         => 'TEST');

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

PL/SQL procedure successfully completed.

SQL>

Dropping Plans and Baselines

The DROP_SQL_PLAN_BASELINE function can drop a specific plan from a baseline, or all plans if the plan name is not specified.

CONN sys/password@db11g AS SYSDBA

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;
/

Enterprise Manager

SQL plan baseline management is integrated into Enterprise Manager. Click on the "Server" tab. Then click on the "SQL Plan Control" link in the "Query Optimizer" section. On the resulting page, click the "SQL Plan Baseline" tab. The "SQL Plan Baseline" screen, shown below, allows you to manage the SQL plan baselines.

For more information see:

Hope this helps. Regards Tim...

All about Databases - Oracle, PostgeSQL, Mysql, Big Data .....

Tuesday, June 5, 2018