1. SQL Tuning Guide
  2. SQL Management Objects
  3. Managing SQL Plan Baselines

29 Managing SQL Plan Baselines

This chapter explains the concepts and tasks relating to SQL plan management using the DBMS_SPM package.

This chapter contains the following topics:

See Also:

29.1 About Managing SQL Plan Baselines

This topic describes the available interfaces and basic tasks for SQL plan management.

This section contains the following topics:

29.1.1 User Interfaces for SQL Plan Management

You can access the DBMS_SPM package through Cloud Control or through the command line.

This section contains the following topics:

29.1.1.1 Accessing the SQL Plan Baseline Page in Cloud Control

The SQL Plan Control page in Cloud Control is a GUI that shows information about SQL profiles, SQL patches, and SQL plan baselines.

To access the SQL Plan Baseline page:

  1. Log in to Cloud Control with the appropriate credentials.

  2. Under the Targets menu, select Databases.

  3. In the list of database targets, select the target for the Oracle Database instance that you want to administer.

  4. If prompted for database credentials, then enter the minimum credentials necessary for the tasks you intend to perform.

  5. From the Performance menu, select SQL, then SQL Plan Control.

    The SQL Plan Control page appears.

  6. Click Files to view the SQL Plan Baseline subpage, shown in Figure 29-1.

    Figure 29-1 SQL Plan Baseline Subpage

    Description of Figure 29-1 follows
    Description of "Figure 29-1 SQL Plan Baseline Subpage"

    You can perform most SQL plan management tasks in this page or in pages accessed through this page.

See Also:

29.1.1.2 DBMS_SPM Package

On the command line, use the DBMS_SPM and DBMS_XPLAN PL/SQL packages to perform most SQL plan management tasks.

The following table describes the most relevant DBMS_SPM procedures and functions for creating, dropping, and loading SQL plan baselines.

Table 29-1 DBMS_SPM Procedures and Functions

Procedure or Function Description

CONFIGURE

This procedure changes configuration options for the SMB in name/value format.

CREATE_STGTAB_BASELINE

This procedure creates a staging table that enables you to transport SQL plan baselines from one database to another.

DROP_SQL_PLAN_BASELINE

This function drops some or all plans in a plan baseline.

LOAD_PLANS_FROM_CURSOR_CACHE

This function loads plans in the shared SQL area (also called the cursor cache) into SQL plan baselines.

LOAD_PLANS_FROM_SQLSET

This function loads plans in an STS into SQL plan baselines.

LOAD_PLANS_FROM_AWR

This function loads plans from AWR into SQL plan baselines.

PACK_STGTAB_BASELINE

This function packs SQL plan baselines, which means that it copies them from the SMB into a staging table.

UNPACK_STGTAB_BASELINE

This function unpacks SQL plan baselines, which means that it copies SQL plan baselines from a staging table into the SMB.

Also, you can use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE to show one or more execution plans for the SQL statement identified by SQL handle.

See Also:

29.1.2 Basic Tasks in SQL Plan Management

This topic explains the basic tasks in using SQL plan management to prevent performance regressions and enable the optimizer to consider new execution plans.

The tasks are as follows:

29.2 Configuring SQL Plan Management

You can configure the capture and use of SQL plan baselines, and the SPM Evolve Advisor task.

This section contains the following topics:

29.2.1 Configuring the Capture and Use of SQL Plan Baselines

You control SQL plan management with the initialization parameters OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and OPTIMIZER_USE_SQL_PLAN_BASELINES.

The default values are as follows:

  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false

    For any repeatable SQL statement that does not already exist in the plan history, the database does not automatically create an initial SQL plan baseline for the statement.

    If OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true, then you can use the DBMS_SPM.CONFIGURE procedure to configure filters that determine which statements are eligible for plan capture. By default, no filters are configured, which means that all repeatable statements are eligible for plan capture.

  • OPTIMIZER_USE_SQL_PLAN_BASELINES=true

    For any SQL statement that has an existing SQL plan baseline, the database automatically adds new plans to the SQL plan baseline as unaccepted plans.

Note:

The settings of the preceding parameters are independent of each other. For example, if OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is true, then the database creates initial plan baselines for new statements even if OPTIMIZER_USE_SQL_PLAN_BASELINES is false.

If the default behavior is what you intend, then skip this section.

The following sections explain how to change the default parameter settings from the command line. If you use Cloud Control, then set these parameters in the SQL Plan Baseline subpage. This section contains the following topics:

See Also:

29.2.1.1 Enabling Automatic Initial Plan Capture for SQL Plan Management

Setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to true is all that is necessary for the database to automatically create an initial SQL plan baseline for any eligible SQL statement not already in the plan history.

By default, the database considers all repeatable SQL statements as eligible for capture, with the following exceptions:

  • CREATE TABLE when the AS SELECT clause is not specified

  • DROP TABLE

  • INSERT INTO ... VALUES

Caution:

By default, when automatic baseline capture is enabled, the database creates a SQL plan baseline for every eligible repeatable statement, including all recursive SQL and monitoring SQL. Thus, automatic capture may result in the creation of an extremely large number of plan baselines. To limit the statements that are eligible for plan baselines, configure filters using the DBMS_SPM.CONFIGURE procedure.

The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter does not control the automatic addition of newly discovered plans to a previously created SQL plan baseline.

To enable automatic initial plan capture for SQL plan management:

  1. In SQL*Plus, log in to the database with the necessary privileges.

  2. Show the current settings for SQL plan management.

    For example, connect SQL*Plus to the database with administrator privileges and execute the following command (sample output included):

    SHOW PARAMETER SQL_PLAN

    The following sample output shows that automatic initial plan capture is disabled:

    NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

    If the parameters are set as you intend, then skip the remaining steps.

  3. To enable the automatic recognition of repeatable SQL statements and the generation of SQL plan baselines for these statements, enter the following statement:

    ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

See Also:

Oracle Database Reference to learn more about OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

29.2.1.2 Configuring Filters for Automatic Plan Capture

If OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true, then you can use the DBMS_SPM.CONFIGURE procedure to create an automatic capture filter for repeatable statements.

An automatic filter enables you to capture only statements that you want, and exclude noncritical statements. This technique saves space in the SYSAUX tablespace.

The following table describes the relevant parameters of the DBMS_SPM.CONFIGURE procedure.

Table 29-2 DBMS_SPM.CONFIGURE Parameters

Parameter Description

parameter_name

The type of filter for automatic capture.

Possible values are AUTO_CAPTURE_SQL_TEXT, AUTO_CAPTURE_PARSING_SCHEMA_NAME, AUTO_CAPTURE_MODULE, and AUTO_CAPTURE_ACTION.

parameter_value

The search criteria for the automatic capture filter.

When parameter_name is set to AUTO_CAPTURE_SQL_TEXT, the search pattern depends on the allow setting:

  • LIKE

    The parameter uses this pattern when allow=>true.

  • NOT LIKE

    The parameter uses this pattern when allow=>false.

For all other non-null parameter_name values, the search pattern depends on the allow setting:

  • =

    The parameter uses this pattern when allow=>true.

  • <>

    The parameter uses this pattern when allow=>false.

A null value removes the filter for parameter_name entirely.

allow Whether to include (true) or exclude (false) matching SQL statements and plans. If null, then the procedure ignores the specified parameter.

You can configure multiple parameters of different types. Also, you can specify multiple values for the same parameter in separate statements, which the database combines. The settings are additive: one parameter setting does not override a previous setting. For example, the following filter captures SQL in the parsing schema SYS or SYSTEM: 

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYS',true);
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYSTEM',true);

However, you cannot configure multiple values for the same parameter in the same procedure. For example, you cannot specify multiple SQL text strings for AUTO_CAPTURE_SQL_TEXT.

The DBA_SQL_MANAGEMENT_CONFIG view shows the current parameter values.

This tutorial assumes the following:

  • The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter is set to true.

  • You want to include only statements parsed in the sh schema to be eligible for baselines.

  • You want to exclude statements that contain the text TEST_ONLY.

To filter out all statements except those parsed in the sh schema:

  1. Connect SQL*Plus to the database with the appropriate privileges.

  2. To remove any existing filters for parsing schema and SQL text, execute the following PL/SQL programs:

    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME',null,true);
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT',null,true);

  3. Include only statements parsed in the sh schema for consideration for automatic capture: 

    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','sh',true);

  4. Exclude any statement that contains the text TEST_ONLY from consideration for automatic capture: 

    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%TEST_ONLY%',false);

  5. Optionally, to confirm the filters, query DBA_SQL_MANAGEMENT_CONFIG.

    For example, use the following query (sample output included):

    COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32

SELECT PARAMETER_NAME, PARAMETER_VALUE 
FROM   DBA_SQL_MANAGEMENT_CONFIG 
WHERE  PARAMETER_NAME LIKE '%AUTO%';

PARAMETER_NAME                   PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (SH)
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT            (sql_text NOT LIKE %TEST_ONLY%)

See Also:

29.2.1.3 Disabling All SQL Plan Baselines

When you set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to false, the database does not use any plan baselines in the database.

Typically, you might want to disable one or two plan baselines, but not all of them. A possible use case might be testing the benefits of SQL plan management.

To disable all SQL plan baselines in the database:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then show the current settings for SQL plan management.

    For example, connect SQL*Plus to the database with administrator privileges and execute the following command (sample output included):

    SQL> SHOW PARAMETER SQL_PLAN
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

    If the parameters are set as you intend, then skip the remaining steps.

  2. To ignore all existing plan baselines enter the following statement:

    SQL> ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES=false

See Also:

Oracle Database Reference to learn about the SQL plan baseline initialization parameters

29.2.2 Managing the SPM Evolve Advisor Task

SPM Evolve Advisor is a SQL advisor that evolves plans that have recently been added to the SQL plan baseline. The advisor simplifies plan evolution by eliminating the requirement to do it manually.

This section contains the following topics:

29.2.2.1 About the SPM Evolve Advisor Task

By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window.

The SPM Evolve Advisor task perform the following operations:

  1. Locates unaccepted plans

  2. Ranks all unaccepted plans

  3. Performs test executions of as many plans as possible during the maintenance window

  4. Selects the lowest-cost plan to compare against each unaccepted plan

  5. Accepts automatically any unaccepted plan that performs sufficiently better, using a cost-based algorithm, than the existing accepted plan

Note:

The SPM Evolve Advisor task can accept more than one plan for a single statement.

SPM Evolve Advisor locates unaccepted plans that the optimizer added to the SMB during earlier hard parses. In some cases, more optimal plans may reside in other locations. By using the DBMS_SPM.SET_EVOLVE_TASK_PARAMETER procedure, you can configure the automatic task to search the shared SQL area, AWR repository, or SQL tuning sets for plans that are not yet in the SMB plan history. If the advisor finds eligible plans in alternative locations, then it includes them along with the other unaccepted plans. By default, the automatic task does not search alternative locations.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SPM.SET_EVOLVE_TASK_PARAMETER procedure

29.2.2.2 Enabling and Disabling the Automatic SPM Evolve Advisor Task

No separate scheduler client exists for the Automatic SPM Evolve Advisor task.

One client controls both Automatic SQL Tuning Advisor and Automatic SPM Evolve Advisor. Thus, the same task enables or disables both. You can also disable it using DBMS_SPM.SET_EVOLVE_TASK_PARAMETER.

To disable the Automatic SPM Evolve Advisor task:

  1. Log in to the database with the appropriate privileges.

  2. Set the ALTERNATE_PLAN_BASELINE parameter to null: 

    BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_BASELINE',
      value => '');
END;
/

  3. Set the ALTERNATE_PLAN_SOURCE parameter to an empty string: 

    BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_SOURCE',
      value => '');
END;
/

See Also:

"Enabling and Disabling the Automatic SQL Tuning Task" to learn how to enable and disable Automatic SPM Evolve Advisor

29.2.2.3 Configuring the Automatic SPM Evolve Advisor Task

Configure automatic plan evolution by specifying the task parameters using the DBMS_SPM.SET_EVOLVE_TASK_PARAMETER procedure.

Because the SYS_AUTO_SPM_EVOLVE_TASK task is owned by SYS, only SYS can set task parameters.

The following table describes some of the procedure parameters.

Table 29-3 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER Parameters

Parameter Description

alternate_plan_source

Determines which sources to search for additional plans: CURSOR_CACHE, AUTOMATIC_WORKLOAD_REPOSITORY, or SQL_TUNING_SETS. Combine multiple values with the plus sign (+). The default is CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY.

alternate_plan_baseline

Determines which alternative plans should be loaded. EXISTING, which is the default, loads plans with for statements with existing baselines. NEW loads plans for statements without a baseline, in which case a new baseline is created. Combine multiple values with the plus sign (+).

alternate_plan_limit

Specifies the maximum number of plans to load in total (that is, not for each SQL statement). The default is 10.

accept_plans

Specifies whether to accept recommended plans automatically. When ACCEPT_PLANS is true (default), SQL plan management automatically accepts all plans recommended by the task. When set to false, the task verifies the plans and generates a report if its findings, but does not evolve the plans.

The tutorial in this section assumes the following:

  • You want the database to accept plans automatically.

  • You want the task to time out after 1200 seconds per execution.

  • You want the evolve task to look for up to a maximum of 500 plans in the shared SQL area and AWR repository

To set automatic evolution task parameters:

  1. Connect SQL*Plus to the database as SYS.

  2. Query the current parameter settings for SYS_AUTO_SPM_EVOLVE_TASK.

    For example, connect SQL*Plus to the database with administrator privileges and execute the following query:

    COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a42
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   DBA_ADVISOR_PARAMETERS
WHERE  ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
         ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
           (PARAMETER_NAME LIKE '%ALT%') OR
           (PARAMETER_NAME = 'TIME_LIMIT') ) );

    Sample output appears as follows:

    PARAMETER_NAME            VALUE
------------------------- ------------------------------------------
ALTERNATE_PLAN_LIMIT      0
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              true
TIME_LIMIT                3600

  3. Set parameters using PL/SQL code of the following form:

    BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => parameter_name
,   value     => value
);
END;
/

    For example, the following PL/SQL block configures the SYS_AUTO_SPM_EVOLVE_TASK task to automatically accept plans, seek up a maximum of 500 plans in the shared SQL area and AWR repository, and time out after 20 minutes: 

    BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => 'TIME_LIMIT'
,   value     => '1200'
);
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => 'ACCEPT_PLANS'
,   value     => 'true'
);
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => 'ALTERNATE_PLAN_LIMIT'
,   value     => '500'
);
END;
/

  4. Optionally, confirm your changes by querying the current parameter settings for SYS_AUTO_SPM_EVOLVE_TASK.

    For example, execute the following query:

    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   DBA_ADVISOR_PARAMETERS
WHERE  ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
         ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
           (PARAMETER_NAME LIKE '%ALT%') OR
           (PARAMETER_NAME = 'TIME_LIMIT') ) );

    Sample output appears as follows:

    PARAMETER_NAME            VALUE
------------------------- ------------------------------------------
ALTERNATE_PLAN_LIMIT      500
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              true
TIME_LIMIT                1200

See Also:

29.3 Displaying Plans in a SQL Plan Baseline

To view the plans stored in the SQL plan baseline for a specific statement, use the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function.

This function uses plan information stored in the plan history to display the plans. The following table describes the relevant parameters for DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.

Table 29-4 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE Parameters

Function Parameter Description

sql_handle

SQL handle of the statement. Retrieve the SQL handle by joining the V$SQL.SQL_PLAN_BASELINE and DBA_SQL_PLAN_BASELINES views on the PLAN_NAME columns.

plan_name

Name of the plan for the statement.

This section explains how to show plans in a baseline from the command line. If you use Cloud Control, then display plan baselines from the SQL Plan Baseline subpage shown in Figure 29-1.

To display plans in a SQL plan baselines:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then obtain the SQL ID of the query whose plan you want to display.

    For example, assume that a SQL plan baseline exists for a SELECT statement with the SQL ID 31d96zzzpcys9.

  2. Query the plan by SQL ID.

    The following query displays execution plans for the statement with the SQL ID 31d96zzzpcys9: 

    SELECT PLAN_TABLE_OUTPUT
FROM   V$SQL s, DBA_SQL_PLAN_BASELINES b, 
       TABLE(
       DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic') 
       ) t
WHERE  s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND    b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND    s.SQL_ID='31d96zzzpcys9';

    The sample query results are as follows:

    PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
 
------------------------------------------------------------------------
SQL handle: SQL_513f7f8a91177b1a
SQL text: select * from hr.employees where employee_id=100
------------------------------------------------------------------------

------------------------------------------------------------------------
Plan name: SQL_PLAN_52gvzja8jfysuc0e983c6         Plan id: 3236529094
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
------------------------------------------------------------------------
 
Plan hash value: 3236529094
 
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
|   2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |
-----------------------------------------------------

    The results show that the plan for SQL ID 31d96zzzpcys is named SQL_PLAN_52gvzja8jfysuc0e983c6 and was captured automatically.

See Also:

29.4 Loading SQL Plan Baselines

Using DBMS_SPM, you can initiate the bulk load of a set of existing plans into a SQL plan baseline.

This section contains the following topics:

29.4.1 About Loading SQL Plan Baselines

The DBMS_SPM package enables you to load plans from multiple sources.

The goal of this task is to load plans from the following sources:

  • AWR

    Load plans from Automatic Workload Repository (AWR) snapshots. You must specify the beginning and ending of the snapshot range. Optionally, you can apply a filter to load only plan that meet specified criteria. By default, the optimizer uses the loaded plans the next time that the database executes the SQL statements.

  • Shared SQL area

    Load plans for statements directly from the shared SQL area, which is in the shared pool of the SGA. By applying a filter on the module name, the schema, or the SQL ID you identify the SQL statement or set of SQL statements to capture. The optimizer uses the plans the next time that the database executes the SQL statements.

    Loading plans directly from the shared SQL area is useful when application SQL has been hand-tuned using hints. Because you probably cannot change the SQL to include the hint, populating the SQL plan baseline ensures that the application SQL uses optimal plans.

  • SQL tuning set (STS)

    Capture the plans for a SQL workload into an STS, and then load the plans into the SQL plan baselines. The optimizer uses the plans the next time that the database executes the SQL statements. Bulk loading execution plans from an STS is an effective way to prevent plan regressions after a database upgrade.

  • Staging table

    Use the DBMS_SPM package to define a staging table, DBMS_SPM.PACK_STGTAB_BASELINE to copy the baselines into a staging table, and Oracle Data Pump to transfer the table to another database. On the destination database, use DBMS_SPM.UNPACK_STGTAB_BASELINE to unpack the plans from the staging table and put the baselines into the SMB.

    A use case is the introduction of new SQL statements into the database from a new application module. A vendor can ship application software with SQL plan baselines for the new SQL. In this way, the new SQL uses plans that are known to give optimal performance under a standard test configuration. Alternatively, if you develop or test an application in-house, export the correct plans from the test database and import them into the production database.

  • Stored outline

    Migrate stored outlines to SQL plan baselines. After the migration, you maintain the same plan stability that you had using stored outlines while being able to use the more advanced features provided by SQL Plan Management, such as plan evolution. See .

See Also:

29.4.2 Loading Plans from AWR

This topic explains how to load plans from AWR using PL/SQL.

Load plans with the LOAD_PLANS_FROM_AWR function of the DBMS_SPM package. The following table describes some function parameters.

Table 29-5 LOAD_PLANS_FROM_AWR Parameters

Function Parameter Description

begin_snap

Number of the beginning snapshot in the range. Required.

end_snap

Number of the ending snapshot in the range. Required.

basic_filter

A filter applied to AWR to select only qualifying plans to be loaded. The default null means that all plans in AWR are selected. The filter can take the form of any WHERE clause predicate that can be specified against the column DBA_HIST_SQLTEXT.SQL_TEXT. An example is basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%'''.

fixed

Default NO means the loaded plans are used as nonfixed plans. YES means the loaded plans are fixed plans. "Plan Selection" explains that the optimizer chooses a fixed plan in the plan baseline over a nonfixed plan.

This section explains how to load plans using the command line. In Cloud Control, go to the SQL Plan Baseline subpage (shown in Figure 29-1) and click Load to load plan baselines from AWR.

This tutorial assumes the following:

  • You want to load plans for the following query into the SMB:

    SELECT /*LOAD_AWR*/ *
FROM   sh.sales
WHERE  quantity_sold > 40
ORDER BY prod_id;

  • You want the loaded plans to be nonfixed.

  • The user sh has privileges to query DBA_HIST_SNAPSHOT and DBA_SQL_PLAN_BASELINES, execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT, and execute DBMS_SPM.LOAD_PLANS_FROM_AWR.

To load plans from the shared SQL area:

  1. Log in to the database with the appropriate privileges, and then query the most recent 3 AWR snapshots.

    For example, query DBA_HIST_SNAPSHOT as follows: 

    SELECT *
FROM   (SELECT SNAP_ID, SNAP_LEVEL, 
               TO_CHAR(BEGIN_INTERVAL_TIME, 'DD/MM/YY HH24:MI:SS') BEGIN
        FROM   DBA_HIST_SNAPSHOT
        ORDER BY SNAP_ID DESC)
WHERE   ROWNUM <= 3;

   SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- -----------------
       212	    1 10/12/15 06:00:02
       211	    1 10/12/15 05:00:11
       210	    1 10/12/15 04:00:59

  2. Query sh.sales, using the LOAD_AWR tag to identify the SQL statement.

    For example, use the following query:

    SELECT /*LOAD_AWR*/ *
FROM   sh.sales
WHERE  quantity_sold > 40
ORDER BY prod_id;

  3. Take a new AWR snapshot.

    For example, use the following program:

    EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

  4. Query the most recent 3 AWR snapshots to confirm that a new snapshot was taken.

    For example, query DBA_HIST_SNAPSHOT as follows: 

    SELECT *
FROM   (SELECT SNAP_ID, SNAP_LEVEL, 
               TO_CHAR(BEGIN_INTERVAL_TIME, 'DD/MM/YY HH24:MI:SS') BEGIN
        FROM   DBA_HIST_SNAPSHOT
        ORDER BY SNAP_ID DESC)
WHERE   ROWNUM <= 3;

   SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- -----------------
       213	    1 10/12/15 06:24:53
       212	    1 10/12/15 06:00:02
       211	    1 10/12/15 05:00:11

  5. Load the plans for the most recent 2 snapshots from AWR.

    For example, execute the LOAD_PLANS_FROM_AWR function in SQL*Plus to load the plans from snapshot 212 to 213: 

    VARIABLE v_plan_cnt NUMBER
EXEC :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap => 212, end_snap => 213);

    In the preceding example, the variable v_plan_cnt contains the number of plans that were loaded.

  6. Query the data dictionary to ensure that the plans were loaded into the baseline for the LOAD_AWR statement.

    The following statement queries DBA_SQL_PLAN_BASELINES (sample output included): 

    COL SQL_HANDLE FORMAT a20
COL SQL_TEXT FORMAT a20
COL PLAN_NAME FORMAT a30
COL ORIGIN FORMAT a20

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%LOAD_AWR%';

SQL_HANDLE           SQL_TEXT          PLAN_NAME                  ORIGIN       ENA ACC
-------------------- ----------------- -------------------------- -----------  --- ---
SQL_495d29c5f4612cda SELECT /*LOAD_AWR SQL_PLAN_4kr99sru62b6u54bc MANUAL-LOAD- YES YES
                     */ * FROM         8843                       FROM-AWR 
                     sh.sales WHERE  
                     quantity_sold 
                     > 40
                     ORDER BY prod_id

    The output shows that the plan is accepted, which means that it is in the plan baseline for the statement. Also, the origin is MANUAL-LOAD-FROM-AWR, which means that the statement was loaded manually from AWR rather than automatically captured.

See Also:

29.4.3 Loading Plans from the Shared SQL Area

This topic explains how to load plans from the shared SQL area, also called the cursor cache, using PL/SQL.

Load plans with the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. The following table describes some function parameters.

Table 29-6 LOAD_PLANS_FROM_CURSOR_CACHE Parameters

Function Parameter Description

sql_id

SQL statement identifier. Identifies a SQL statement in the shared SQL area.

fixed

Default NO means the loaded plans are used as nonfixed plans. YES means the loaded plans are fixed plans. "Plan Selection" explains that the optimizer chooses a fixed plan in the plan baseline over a nonfixed plan.

This section explains how to load plans using the command line. In Cloud Control, go to the SQL Plan Baseline subpage (shown in Figure 29-1) and click Load to load plan baselines from the shared SQL area.

This tutorial assumes the following:

  • You have executed the following query:

    SELECT /*LOAD_CC*/ *
FROM   sh.sales
WHERE  quantity_sold > 40
ORDER BY prod_id;

  • You want the loaded plans to be nonfixed.

To load plans from the shared SQL area:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then determine the SQL IDs of the relevant statements in the shared SQL area.

    For example, query V$SQL for the SQL ID of the sh.sales query (sample output included): 

    SELECT   SQL_ID, CHILD_NUMBER AS "Child Num",
         PLAN_HASH_VALUE AS "Plan Hash",
         OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash"
FROM     V$SQL
WHERE    SQL_TEXT LIKE 'SELECT /*LOAD_CC*/%';
 
SQL_ID         Child Num  Plan Hash Opt Env Hash
------------- ---------- ---------- ------------
27m0sdw9snw59          0 1421641795   3160571937

    The preceding output shows that the SQL ID of the statement is 27m0sdw9snw59.

  2. Load the plans for the specified statements into the SQL plan baseline.

    For example, execute the LOAD_PLANS_FROM_CURSOR_CACHE function in SQL*Plus to load the plan for the statement with the SQL ID 27m0sdw9snw59: 

    VARIABLE v_plan_cnt NUMBER
BEGIN
  :v_plan_cnt:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( 
    sql_id => '27m0sdw9snw59');
END;

    In the preceding example, the variable v_plan_cnt contains the number of plans that were loaded.

  3. Query the data dictionary to ensure that the plans were loaded into the baseline for the statement.

    The following statement queries DBA_SQL_PLAN_BASELINES (sample output included): 

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES;
 
SQL_HANDLE            SQL_TEXT             PLAN_NAME      ORIGIN              ENA ACC
--------------------- -------------------- -------------- ------------------- --- ---
SQL_a8632bd857a4a25e  SELECT /*LOAD_CC*/   SQL_PLAN_gdkvz MANUAL-LOAD-FROM-CC YES YES
                      *                    fhrgkda71694fc
                      FROM sh.sales        6b
                      WHERE quantity_sold
                      > 40                           
                      ORDER BY prod_id

    The output shows that the plan is accepted, which means that it is in the plan baseline for the statement. Also, the origin is MANUAL-LOAD-FROM-CC, which means that the statement was loaded manually from the shared SQL area rather than automatically captured.

See Also:

29.4.4 Loading Plans from a SQL Tuning Set

A SQL tuning set (STS) is a database object that includes one or more SQL statements, execution statistics, and execution context. This topic explains how to load plans from an STS.

Load plans with the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function or using Cloud Control. The following table describes some function parameters.

Table 29-7 LOAD_PLANS_FROM_SQLSET Parameters

Function Parameter Description

sqlset_name

Name of the STS from which the plans are loaded into SQL plan baselines.

basic_filter

A filter applied to the STS to select only qualifying plans to be loaded. The filter can take the form of any WHERE clause predicate that can be specified against the view DBA_SQLSET_STATEMENTS. An example is basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%'''.

fixed

Default NO means the loaded plans are used as nonfixed plans. YES means the loaded plans are fixed plans. "Plan Selection" explains that the optimizer chooses a fixed plan in the plan baseline over a nonfixed plan.

This section explains how to load plans from the command line. In Cloud Control, go to the SQL Plan Baseline subpage (shown in Figure 29-1) and click Load to load plan baselines from SQL tuning sets.

Assumptions

This tutorial assumes the following:

  • You want the loaded plans to be nonfixed.

  • You have executed the following query:

    SELECT /*LOAD_STS*/ *
FROM   sh.sales
WHERE  quantity_sold > 40
ORDER BY prod_id;

  • You have loaded the plan from the shared SQL area into the SQL tuning set named SPM_STS, which is owned by user SPM.

  • After the operation, you want to drop the STS using DBMS_SQLTUNE.DROP_SQLSET rather than the equivalent DBMS_SQLSET.DROP_SQLSET.

To load plans from a SQL tuning set:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then verify which plans are in the SQL tuning set.

    For example, query DBA_SQLSET_STATEMENTS for the STS name (sample output included): 

    SELECT SQL_TEXT
FROM   DBA_SQLSET_STATEMENTS
WHERE  SQLSET_NAME = 'SPM_STS';
 
SQL_TEXT
--------------------
SELECT /*LOAD_STS*/
*
FROM sh.sales
WHERE quantity_sold
> 40
ORDER BY prod_id

    The output shows that the plan for the select /*LOAD_STS*/ statement is in the STS.

  2. Load the plan from the STS into the SQL plan baseline.

    For example, in SQL*Plus execute the function as follows:

    VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
           sqlset_name  => 'SPM_STS', -
           basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%''' );

    The basic_filter parameter specifies a WHERE clause that loads only the plans for the queries of interest. The variable v_plan_cnt stores the number of plans loaded from the STS.

  3. Query the data dictionary to ensure that the plan was loaded into the baseline for the statement.

    The following statement queries the DBA_SQL_PLAN_BASELINES view (sample output included). 

    SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
  2         ORIGIN, ENABLED, ACCEPTED
  3  FROM   DBA_SQL_PLAN_BASELINES;

SQL_HANDLE            SQL_TEXT        PLAN_NAME        ORIGIN               ENA ACC
--------------------- --------------- ---------------- -------------------- --- ---
SQL_a8632bd857a4a25e  SELECT          SQL_PLAN_ahstb   MANUAL-LOAD-FROM-STS YES YES
                      /*LOAD_STS*/*   v1bu98ky1694fc6b
                      FROM sh.sales
                      WHERE 
                      quantity_sold 
                      > 40 ORDER BY                    
                      prod_id

    The output shows that the plan is accepted, which means that it is in the plan baseline. Also, the origin is MANUAL-LOAD-FROM-STS, which means that the plan was loaded manually from a SQL tuning set rather than automatically captured.

  4. Optionally, drop the STS.

    For example, execute DBMS_SQLTUNE.DROP_SQLSET to drop the SPM_STS tuning set as follows: 

    EXEC SYS.DBMS_SQLTUNE.DROP_SQLSET( sqlset_name  => 'SPM_STS', -
                                   sqlset_owner => 'SPM' );

See Also:

29.4.5 Loading Plans from a Staging Table

You may want to transfer optimal plans from a source database to a different destination database.

For example, you may have investigated a set of plans on a test database and confirmed that they have performed well. You may then want to load these plans into a production database.

A staging table is a table that, for the duration of its existence, stores plans so that the plans do not disappear from the table while you are unpacking them. Use the DBMS_SPM.CREATE_STGTAB_BASELINE procedure to create a staging table. To pack (insert row into) and unpack (extract rows from) the staging table, use the PACK_STGTAB_BASELINE and UNPACK_STGTAB_BASELINE functions of the DBMS_SPM package. Oracle Data Pump Import and Export enable you to copy the staging table to a different database.

Figure 29-2 Loading Plans from a Staging Table

Description of Figure 29-2 follows
Description of "Figure 29-2 Loading Plans from a Staging Table"

Export plans with the PACK_STGTAB_BASELINE function of the DBMS_SPM package, and then import them with UNPACK_STGTAB_BASELINE. The following table describes some function parameters.

Table 29-8 PACK_STGTAB_BASELINE and UNPACK_STGTAB_BASELINE Parameters

Function Parameter Description

table_name

Specifies the table to be imported or exported.

origin

Origin of SQL plan baseline. These procedures accept all possible values of DBA_SQL_PLAN_BASELINES.ORIGIN as the origin argument. For example, the origin parameter permits MANUAL-LOAD-FROM-STS, MANUAL-LOAD-FROM-AWR, and MANUAL-LOAD-FROM-CC.

This tutorial assumes the following:

  • You want to create a staging table named stage1 in the source database.

  • You want to load all plans owned by user spm into the staging table.

  • You want to transfer the staging table to a destination database.

  • You want to load the plans in stage1 as fixed plans.

To transfer a set of SQL plan baselines from one database to another:

  1. Connect SQL*Plus to the source database with the appropriate privileges, and then create a staging table using the CREATE_STGTAB_BASELINE procedure.

    The following example creates a staging table named stage1: 

    BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE (
    table_name => 'stage1');
END;
/

  2. On the source database, pack the SQL plan baselines you want to export from the SQL management base into the staging table.

    The following example packs enabled plan baselines created by user spm into staging table stage1. Select SQL plan baselines using the plan name (plan_name), SQL handle (sql_handle), or any other plan criteria. The table_name parameter is mandatory. 

    DECLARE
  v_plan_cnt NUMBER;
BEGIN
  v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE (
    table_name => 'stage1'
,   enabled    => 'yes'
,   creator    => 'spm'
);
END;
/

  3. Export the staging table stage1 into a dump file using Oracle Data Pump Export.

  4. Transfer the dump file to the host of the destination database.

  5. On the destination database, import the staging table stage1 from the dump file using the Oracle Data Pump Import utility.

  6. On the destination database, unpack the SQL plan baselines from the staging table into the SQL management base.

    The following example unpacks all fixed plan baselines stored in the staging table stage1: 

    DECLARE
  v_plan_cnt NUMBER;
BEGIN
  v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE (
    table_name => 'stage1'
,   fixed      => 'yes'
);
END;
/

See Also:

29.5 Evolving SQL Plan Baselines Manually

You can use PL/SQL or Cloud Control to manually evolve an unaccepted plan to determine whether it performs better than any plan currently in the plan baseline.

This section contains the following topics:

See Also:

"Managing the SPM Evolve Advisor Task"

29.5.1 About the DBMS_SPM Evolve Functions

This topic describes the most relevant DBMS_SPM functions for managing plan evolution. Execute evolution tasks manually or schedule them to run automatically.

Table 29-9 DBMS_SPM Functions and Procedures for Managing Plan Evolution Tasks

Procedure or Function Description

ACCEPT_SQL_PLAN_BASELINE

This function accepts one recommendation to evolve a single plan into a SQL plan baseline.

CREATE_EVOLVE_TASK

This function creates an advisor task to prepare the plan evolution of one or more plans for a specified SQL statement. The input parameters can be a SQL handle, plan name or a list of plan names, time limit, task name, and description.

EXECUTE_EVOLVE_TASK

This function executes an evolution task. The input parameters can be the task name, execution name, and execution description. If not specified, the advisor generates the name, which is returned by the function.

IMPLEMENT_EVOLVE_TASK

This function implements all recommendations for an evolve task. Essentially, this function is equivalent to using ACCEPT_SQL_PLAN_BASELINE for all recommended plans. Input parameters include task name, plan name, owner name, and execution name.

REPORT_EVOLVE_TASK

This function displays the results of an evolve task as a CLOB. Input parameters include the task name and section of the report to include.

SET_EVOLVE_TASK_PARAMETER

This function updates the value of an evolve task parameter.

Oracle recommends that you configure SPM Evolve Advisor to run automatically. You can also evolve SQL plan baselines manually. The following graphic shows the basic workflow for managing SQL plan management tasks.

Figure 29-3 Evolving SQL Plan Baselines

Description of Figure 29-3 follows
Description of "Figure 29-3 Evolving SQL Plan Baselines"

Typically, you manage SQL plan evolution tasks in the following sequence:

  1. Create an evolve task

  2. Optionally, set evolve task parameters

  3. Execute the evolve task

  4. Implement the recommendations in the task

  5. Report on the task outcome

See Also:

29.5.2 Managing an Evolve Task

This topic describes a typical use case in which you create and execute a task, and then implement its recommendations.

The following table describes some parameters of the CREATE_EVOLVE_TASK function.

Table 29-10 DBMS_SPM.CREATE_EVOLVE_TASK Parameters

Function Parameter Description

sql_handle

SQL handle of the statement. The default NULL considers all SQL statements with unaccepted plans.

plan_name

Plan identifier. The default NULL means consider all unaccepted plans of the specified SQL handle or all SQL statements if the SQL handle is NULL.

time_limit

Time limit in number of minutes. The time limit for first unaccepted plan equals the input value. The time limit for the second unaccepted plan equals the input value minus the time spent in first plan verification, and so on. The default DBMS_SPM.AUTO_LIMIT means let the system choose an appropriate time limit based on the number of plan verifications required to be done.

task_name

User-specified name of the evolution task.

This section explains how to evolve plan baselines from the command line. In Cloud Control, from the SQL Plan Baseline subpage, select a plan, and then click Evolve.

This tutorial assumes the following:

  • You do not have the automatic evolve task enabled.

  • You want to create a SQL plan baseline for the following query:

    SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
FROM   products p, sales s
WHERE  p.prod_id = s.prod_id
AND    p.prod_category_id =203
GROUP BY prod_name;

  • You want to create two indexes to improve the query performance, and then evolve the plan that uses these indexes if it performs better than the plan currently in the plan baseline.

To evolve a specified plan:

  1. Perform the initial setup as follows:

    1. Connect SQL*Plus to the database with administrator privileges, and then prepare for the tutorial by flushing the shared pool and the buffer cache:

      ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

    2. Enable the automatic capture of SQL plan baselines.

      For example, enter the following statement:

      ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

    3. Connect to the database as user sh, and then set SQL*Plus display parameters: 

      CONNECT sh
-- enter password
SET PAGES 10000 LINES 140
SET SERVEROUTPUT ON
COL SQL_TEXT FORMAT A20
COL SQL_HANDLE FORMAT A20
COL PLAN_NAME FORMAT A30
COL ORIGIN FORMAT A12
SET LONGC 60535
SET LONG 60535
SET ECHO ON

  2. Execute the SELECT statements so that SQL plan management captures them:

    1. Execute the SELECT /* q1_group_by */ statement for the first time.

      Because the database only captures plans for repeatable statements, the plan baseline for this statement is empty.

    2. Query the data dictionary to confirm that no plans exist in the plan baseline.

      For example, execute the following query (sample output included):

      SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, 
       ACCEPTED, FIXED, AUTOPURGE
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_TEXT LIKE '%q1_group%';

no rows selected

      SQL plan management only captures repeatable statements, so this result is expected.

    3. Execute the SELECT /* q1_group_by */ statement for the second time.

  3. Query the data dictionary to ensure that the plans were loaded into the plan baseline for the statement.

    The following statement queries DBA_SQL_PLAN_BASELINES (sample output included): 

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED, FIXED 
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_TEXT LIKE '%q1_group%';

SQL_HANDLE           SQL_TEXT         PLAN_NAME              ORIGIN       ENA ACC FIX
-------------------- ---------------- ---------------------- ------------ --- --- ---
SQL_07f16c76ff893342 SELECT /* q1_gro SQL_PLAN_0gwbcfvzskcu2 AUTO-CAPTURE YES YES NO
                     up_by */ prod_na 42949306
                     me, sum(quantity
                     _sold) FROM 
                     products p, 
                     sales s WHERE 
                     p.prod_id = 
                     s.prod_id AND
                     p.prod_category
                     _id =203 GROUP
                     BY prod_name

    The output shows that the plan is accepted, which means that it is in the plan baseline for the statement. Also, the origin is AUTO-CAPTURE, which means that the statement was automatically captured and not manually loaded.

  4. Explain the plan for the statement and verify that the optimizer is using this plan.

    For example, explain the plan as follows, and then display it:

    EXPLAIN PLAN FOR  
  SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
  FROM   products p, sales s
  WHERE  p.prod_id = s.prod_id
  AND    p.prod_category_id =203
  GROUP BY prod_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));

    Sample output appears below:

    Plan hash value: 1117033222
 
------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  HASH GROUP BY        |          |
|   2 |   HASH JOIN           |          |
|   3 |    TABLE ACCESS FULL  | PRODUCTS |
|   4 |    PARTITION RANGE ALL|          |
|   5 |     TABLE ACCESS FULL | SALES    |
------------------------------------------
 
Note
-----
   - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement

    The note indicates that the optimizer is using the plan shown with the plan name listed in the previous step.

  5. Create two indexes to improve the performance of the SELECT /* q1_group_by */ statement.

    For example, use the following statements:

    CREATE INDEX ind_prod_cat_name 
  ON products(prod_category_id, prod_name, prod_id);
CREATE INDEX ind_sales_prod_qty_sold 
  ON sales(prod_id, quantity_sold);

  6. Execute the select /* q1_group_by */ statement again.

    Because automatic capture is enabled, the plan baseline is populated with the new plan for this statement.

  7. Query the data dictionary to ensure that the plan was loaded into the SQL plan baseline for the statement.

    The following statement queries DBA_SQL_PLAN_BASELINES (sample output included). 

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_HANDLE IN ('SQL_07f16c76ff893342')
ORDER BY SQL_HANDLE, ACCEPTED;

SQL_HANDLE           SQL_TEXT             PLAN_NAME              ORIGIN       ENA ACC
-------------------- -------------------- ---------------------- ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2 AUTO-CAPTURE YES NO
                     y */ prod_name, sum( 0135fd6c
                     quantity_sold)
                     FROM   products p, s
                     ales s
                     WHERE  p.prod_id = s
                     .prod_id
                     AND    p.prod_catego
                     ry_id =203
                     GROUP BY prod_name
 
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2 AUTO-CAPTURE YES YES
                     y */ prod_name, sum( 42949306
                     quantity_sold)
                     FROM   products p, s
                     ales s
                     WHERE  p.prod_id = s
                     .prod_id
                     AND    p.prod_catego
                     ry_id =203
                     GROUP BY prod_name

    The output shows that the new plan is unaccepted, which means that it is in the statement history but not the SQL plan baseline.

  8. Explain the plan for the statement and verify that the optimizer is using the original unindexed plan.

    For example, explain the plan as follows, and then display it:

    EXPLAIN PLAN FOR
  SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
  FROM   products p, sales s
  WHERE  p.prod_id = s.prod_id
  AND    p.prod_category_id =203
  GROUP BY prod_name;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));

    Sample output appears below:

    Plan hash value: 1117033222
 
------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  HASH GROUP BY        |          |
|   2 |   HASH JOIN           |          |
|   3 |    TABLE ACCESS FULL  | PRODUCTS |
|   4 |    PARTITION RANGE ALL|          |
|   5 |     TABLE ACCESS FULL | SALES    |
------------------------------------------
 
Note
-----
   - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement

    The note indicates that the optimizer is using the plan shown with the plan name listed in Step 3.

  9. Connect as an administrator, and then create an evolve task that considers all SQL statements with unaccepted plans.

    For example, execute the DBMS_SPM.CREATE_EVOLVE_TASK function and then obtain the name of the task: 

    CONNECT / AS SYSDBA
VARIABLE cnt NUMBER
VARIABLE tk_name VARCHAR2(50)
VARIABLE exe_name VARCHAR2(50)
VARIABLE evol_out CLOB
 
EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK( 
  sql_handle => 'SQL_07f16c76ff893342', 
  plan_name  => 'SQL_PLAN_0gwbcfvzskcu20135fd6c');
 
SELECT :tk_name FROM DUAL;

    The following sample output shows the name of the task:

    :EVOL_OUT
------------------------------------------------------------------------
TASK_11

    Now that the task has been created and has a unique name, execute the task.

  10. Execute the task.

    For example, execute the DBMS_SPM.EXECUTE_EVOLVE_TASK function (sample output included): 

    EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name); 
SELECT :exe_name FROM DUAL;

:EXE_NAME
------------------------------------------------------------------------
EXEC_1

  11. View the report.

    For example, execute the DBMS_SPM.REPORT_EVOLVE_TASK function (sample output included): 

    EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
SELECT :evol_out FROM DUAL;

GENERAL INFORMATION SECTION
------------------------------------------------------------------------
 
 Task Information:
 ---------------------------------------------
 Task Name            : TASK_11
 Task Owner           : SYS
 Execution Name       : EXEC_1
 Execution Type       : SPM EVOLVE
 Scope                : COMPREHENSIVE
 Status               : COMPLETED
 Started              : 01/09/2012 12:21:27
 Finished             : 01/09/2012 12:21:29
 Last Updated         : 01/09/2012 12:21:29
 Global Time Limit    : 2147483646
 Per-Plan Time Limit  : UNUSED
 Number of Errors     : 0
------------------------------------------------------------------------
 
SUMMARY SECTION
------------------------------------------------------------------------
  Number of plans processed  : 1
  Number of findings         : 1
  Number of recommendations  : 1
  Number of errors           : 0
------------------------------------------------------------------------
 
DETAILS SECTION
------------------------------------------------------------------------
 Object ID         : 2
 Test Plan Name    : SQL_PLAN_0gwbcfvzskcu20135fd6c
 Base Plan Name    : SQL_PLAN_0gwbcfvzskcu242949306
 SQL Handle        : SQL_07f16c76ff893342
 Parsing Schema    : SH
 Test Plan Creator : SH
 SQL Text          : SELECT /*q1_group_by*/ prod_name, 
                     sum(quantity_sold) 
                     FROM products p, sales s 
                     WHERE p.prod_id=s.prod_id AND p.prod_category_id=203 
                     GROUP BY prod_name
 
Execution Statistics:
-----------------------------
                    Base Plan                     Test Plan
                    ----------------------------  ---------
 Elapsed Time (s):  .044336                       .012649
 CPU Time (s):      .044003                       .012445
 Buffer Gets:       360                           99
 Optimizer Cost:    924                           891
 Disk Reads:        341                           82
 Direct Writes:     0                             0
 Rows Processed:    4                             2
 Executions:        5                             9
 
 
FINDINGS SECTION
------------------------------------------------------------------------
 
Findings (1):
-----------------------------
 1. The plan was verified in 2.18 seconds. It passed the benefit 
    criterion because its verified performance was 2.01 times 
    better than that of the baseline plan.
 
Recommendation:
-----------------------------
 Consider accepting the plan. Execute
 dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_11', 
 object_id => 2, task_owner => 'SYS');
 
EXPLAIN PLANS SECTION
------------------------------------------------------------------------
 
Baseline Plan
-----------------------------
 Plan Id          : 1
 Plan Hash Value  : 1117033222
 
-----------------------------------------------------------------------
| Id| Operation               | Name   | Rows | Bytes   |Cost | Time  |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT        |        |   21 |     861 |924| 00:00:12|
| 1 |   HASH GROUP BY         |        |   21 |     861 |924| 00:00:12|
| *2|    HASH JOIN            |        |267996|10987836 |742| 00:00:09|
| *3|     TABLE ACCESS FULL   |PRODUCTS|   21 |     714 |  2| 00:00:01|
| 4 |     PARTITION RANGE ALL |        |918843| 6431901 |662| 00:00:08|
| 5 |      TABLE ACCESS FULL  |SALES   |918843| 6431901 |662| 00:00:08|
-----------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_CATEGORY_ID"=203)
  
Test Plan
-----------------------------
 Plan Id          : 2
 Plan Hash Value  : 20315500
 
------------------------------------------------------------------------
|Id| Operation            | Name             | Rows |Bytes |Cost| Time |
------------------------------------------------------------------------
| 0|SELECT STATEMENT      |                  |   21|   861|891|00:00:11|
| 1|  SORT GROUP BY NOSORT|                  |   21|   861|891|00:00:11|
| 2|   NESTED LOOPS       |                  |267K |10987K|891|00:00:11|
|*3|    INDEX RANGE SCAN  |IND_PROD_CAT_NAME |   21|   714|  1|00:00:01|
|*4|    INDEX RANGE SCAN  |IND_SALES_PROD_QTY|12762|  9334| 42|00:00:01|
------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("P"."PROD_CATEGORY_ID"=203)
* 4 - access("P"."PROD_ID"="S"."PROD_ID")

    This report indicates that the new execution plan, which uses the two new indexes, performs better than the original plan.

  12. Implement the recommendations of the evolve task.

    For example, execute the IMPLEMENT_EVOLVE_TASK function: 

    BEGIN 
  :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( 
    task_name=>:tk_name, execution_name=>:exe_name );
END;

  13. Query the data dictionary to ensure that the new plan is accepted.

    The query provides the following sample output:

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_HANDLE IN ('SQL_07f16c76ff893342')
ORDER BY SQL_HANDLE, ACCEPTED;

SQL_HANDLE           SQL_TEXT             PLAN_NAME              ORIGIN       ENA ACC
-------------------- -------------------- ---------------------- ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2 AUTO-CAPTURE YES YES
                     y */ prod_name, sum( 0135fd6c
                     quantity_sold)
                     FROM   products p, s
                     ales s
                     WHERE  p.prod_id = s
                     .prod_id
                     AND    p.prod_catego
                     ry_id =203
                     GROUP BY prod_name
 
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2 AUTO-CAPTURE YES YES
                     y */ prod_name, sum( 42949306
                     quantity_sold)
                     FROM   products p, s
                     ales s
                     WHERE  p.prod_id = s
                     .prod_id
                     AND    p.prod_catego
                     ry_id =203
                     GROUP BY prod_name

    The output shows that the new plan is accepted.

  14. Clean up after the example.

    For example, enter the following statements:

    EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_07f16c76ff893342');
EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_9049245213a986b3');
EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_bb77077f5f90a36b');
EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_02a86218930bbb20');
DELETE FROM SQLLOG$;
CONNECT sh
-- enter password
DROP INDEX IND_SALES_PROD_QTY_SOLD;
DROP INDEX IND_PROD_CAT_NAME;

See Also:

29.6 Dropping SQL Plan Baselines

You can remove some or all plans from a SQL plan baseline. This technique is sometimes useful when testing SQL plan management.

Drop plans with the DBMS_SPM.DROP_SQL_PLAN_BASELINE function. This function returns the number of dropped plans. The following table describes input parameters.

Table 29-11 DROP_SQL_PLAN_BASELINE Parameters

Function Parameter Description

sql_handle

SQL statement identifier.

plan_name

Name of a specific plan. Default NULL drops all plans associated with the SQL statement identified by sql_handle.

This section explains how to drop baselines from the command line. In Cloud Control, from the SQL Plan Baseline subpage, select a plan, and then click Drop.

This tutorial assumes that you want to drop all plans for the following SQL statement, effectively dropping the SQL plan baseline:

SELECT /* repeatable_sql */ COUNT(*) FROM hr.jobs;

To drop a SQL plan baseline:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then query the data dictionary for the plan baseline.

    The following statement queries DBA_SQL_PLAN_BASELINES (sample output included): 

    SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
  2         ENABLED, ACCEPTED
  3  FROM   DBA_SQL_PLAN_BASELINES
  4  WHERE  SQL_TEXT LIKE 'SELECT /* repeatable_sql%';

SQL_HANDLE           SQL_TEXT             PLAN_NAME              ORIGIN       ENA ACC
-------------------- -------------------- ---------------------- ------------ --- ---
SQL_b6b0d1c71cd1807b SELECT /* repeatable SQL_PLAN_bdc6jswfd303v AUTO-CAPTURE YES YES
                     _sql */ count(*)     2f1e9c20
                     from hr.jobs

  2. Drop the SQL plan baseline for the statement.

    The following example drops the plan baseline with the SQL handle SQL_b6b0d1c71cd1807b, and returns the number of dropped plans. Specify plan baselines using the plan name (plan_name), SQL handle (sql_handle), or any other plan criteria. The table_name parameter is mandatory. 

    DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle => 'SQL_b6b0d1c71cd1807b'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/

  3. Confirm that the plans were dropped.

    For example, execute the following query:

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
       ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_TEXT LIKE 'SELECT /* repeatable_sql%';
 
no rows selected

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about the DROP_SQL_PLAN_BASELINE function

29.7 Managing the SQL Management Base

The SQL management base is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles.

This section contains the following topics:

29.7.1 About Managing the SMB

Use the DBMS_SPM.CONFIGURE procedure to set configuration options for the SMB and the maintenance of SQL plan baselines.

The DBA_SQL_MANAGEMENT_CONFIG view shows the current configuration settings for the SMB. The following table describes the parameters in the PARAMETER_NAME column.

Table 29-12 Parameters in DBA_SQL_MANAGEMENT_CONFIG.PARAMETER_NAME

Parameter Description

SPACE_BUDGET_PERCENT

Maximum percent of SYSAUX space that the SQL management base can use. The default is 10. The allowable range for this limit is between 1% and 50%.

PLAN_RETENTION_WEEKS

Number of weeks to retain unused plans before they are purged. The default is 53.
AUTO_CAPTURE_PARSING_SCHEMA_NAME A list of the form (% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...), which is the internal representation of the parsing schema name filter. If no parsing schema filters exist, then one side of the outer conjunction will be absent.
AUTO_CAPTURE_MODULE A list of the form (% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...), which is the internal representation of the module filter. If no module filters exist, then one side of the outer conjunction will be absent.
AUTO_CAPTURE_ACTION A list of the form (% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...), which is the internal representation of the action filter. If no action filters exist, then one side of the outer conjunction will be absent.
AUTO_CAPTURE_SQL_TEXT A list of the form (% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...), which is the internal representation of the SQL text filter. If no SQL text filters exist, then one side of the outer conjunction will be absent.

See Also:

29.7.2 Changing the Disk Space Limit for the SMB

A weekly background process measures the total space occupied by the SMB.

When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until either the SMB space limit is increased, the size of the SYSAUX tablespace is increased, or the disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles). This task explains how to change the limit with the DBMS_SPM.CONFIGURE procedure.

Assumptions

This tutorial assumes the following:

  • The current SMB space limit is the default of 10%.

  • You want to change the percentage limit to 30%

To change the percentage limit of the SMB:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then query the data dictionary to see the current space budget percent.

    For example, execute the following query (sample output included):

    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 
       ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
         WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
       PARAMETER_VALUE/100 * 
       ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
         WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';

PARAMETER_NAME          %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
-------------------- ---------- ----------------- -------------------
SPACE_BUDGET_PERCENT         10          211.4375            21.14375

  2. Change the percentage setting.

    For example, execute the following command to change the setting to 30%:

    EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);

  3. Query the data dictionary to confirm the change.

    For example, execute the following join (sample output included):

    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 
       ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
         WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
       PARAMETER_VALUE/100 * 
       ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
         WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
FROM   DBA_SQL_MANAGEMENT_CONFIG
WHERE  PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';

PARAMETER_NAME          %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
-------------------- ---------- ----------------- -------------------
SPACE_BUDGET_PERCENT         30          211.4375            63.43125

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SPM.CONFIGURE procedure

29.7.3 Changing the Plan Retention Policy in the SMB

A weekly scheduled purging task manages disk space used by SQL plan management.

The task runs as an automated task in the maintenance window. The database purges plans that have not been used for longer than the plan retention period, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The default retention period is 53 weeks. The period can range between 5 and 523 weeks.

This task explains how to change the plan retention period with the DBMS_SPM.CONFIGURE procedure. In Cloud Control, set the plan retention policy in the SQL Plan Baseline subpage (shown in Figure 29-1).

To change the plan retention period for the SMB:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then query the data dictionary to see the current plan retention period.

    For example, execute the following query (sample output included):

    SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
  2  FROM   DBA_SQL_MANAGEMENT_CONFIG
  3  WHERE  PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';
 
PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
PLAN_RETENTION_WEEKS                        53

  2. Change the retention period.

    For example, execute the CONFIGURE procedure to change the period to 105 weeks: 

    EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);

  3. Query the data dictionary to confirm the change.

    For example, execute the following query:

    SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
  2  FROM   DBA_SQL_MANAGEMENT_CONFIG
  3  WHERE  PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';
 
PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
PLAN_RETENTION_WEEKS                       105

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about the CONFIGURE procedure