23 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:

Migrating Stored Outlines to SQL Plan Baselines

About SQL Plan Management

SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans. In this context, a plan includes all plan-related information (for example, SQL plan identifier, set of hints, bind values, and optimizer environment) that the optimizer needs to reproduce an execution plan.

SQL plan management uses a mechanism called a SQL plan baseline. A plan baseline is a set of accepted plans that the optimizer is allowed to use for a SQL statement. In the typical use case, the database accepts a plan into the plan baseline only after verifying that the plan performs well.

The main components of SQL plan management are as follows:

  • Plan capture

    This component stores relevant information about plans for a set of SQL statements. See "Plan Capture".

  • Plan selection

    This component is the detection by the optimizer of plan changes based on stored plan history, and the use of SQL plan baselines to select appropriate plans to avoid potential performance regressions. See "Plan Selection".

  • Plan evolution

    This component is the process of adding new plans to existing SQL plan baselines, either manually or automatically. See "Plan Evolution".

This section contains the following topics:

Purpose of SQL Plan Management

The primary goal of SQL plan management is to prevent performance regressions caused by plan changes. A secondary goal is to gracefully adapt to changes such as new optimizer statistics or indexes by verifying and accepting only plan changes that improve performance.

Note:

SQL plan baselines cannot help when an event has caused irreversible execution plan changes, such as dropping an index.

Benefits of SQL Plan Management

Typical scenarios in which SQL plan management can improve or preserve SQL performance include:

  • A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements.

    Most plan changes result in either improvement or no performance change. However, some plan changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade.

    When you upgrade, the database only uses plans from the plan baseline. The database puts new plans that are not in the current baseline into a holding area, and later evaluates them to determine whether they use fewer resources than the current plan in the baseline. If the plans perform better, then the database promotes them into the baseline; otherwise, the database does not promote them.

  • Ongoing system and data changes can affect plans for some SQL statements, potentially causing performance regressions.

    SQL plan baselines help minimize performance regressions and stabilize SQL performance.

  • Deployment of new application modules introduces new SQL statements into the database.

    The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines over time to produce better performance.

See Also:

Oracle Database Upgrade Guide to learn how to upgrade an Oracle database

Differences Between SQL Plan Baselines and SQL Profiles

Both SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans. Both profiles and baselines are internally implemented using hints. However, these mechanisms have significant differences.

Differences include the following:

  • In general, SQL plan baselines are proactive, whereas SQL profiles are reactive.

    Typically, you create SQL plan baselines before significant performance problems occur. SQL plan baselines prevent the optimizer from using suboptimal plans in the future.

    The database creates SQL profiles when you invoke SQL Tuning Advisor, which you do typically only after a SQL statement has shown high-load symptoms. SQL profiles are primarily useful by providing the ongoing resolution of optimizer mistakes that have led to suboptimal plans. Because the SQL profile mechanism is reactive, it cannot guarantee stable performance as drastic database changes occur.

    The following graphic illustrates the difference:

    Description of the illustration tgsql_vm_028.eps follows
    Description of the illustration tgsql_vm_028.eps

  • SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.

    A SQL plan baseline is a set of accepted plans. Each plan is implemented using a set of outline hints that fully specify a particular plan. SQL profiles are also implemented using hints, but these hints do not specify any specific plan. Rather, the hints correct miscalculations in the optimizer estimates that lead to suboptimal plans. For example, a hint may correct the cardinality estimate of a table.

    Because a profile does not constrain the optimizer to any one plan, a SQL profile is more flexible than a SQL plan baseline. For example, changes in initialization parameters and optimizer statistics allow the optimizer to choose a better plan.

Oracle recommends that you use SQL Tuning Advisor. In this way, you follow the recommendations made by the advisor for SQL profiles and plan baselines rather than trying to determine which mechanism is best for each SQL statement.

See Also:

Plan Capture

SQL plan capture refers to techniques for capturing and storing relevant information about plans in the SQL Management Base for a set of SQL statements. Capturing a plan means making SQL plan management aware of this plan.

You can configure initial plan capture to occur automatically by setting an initialization parameter, or you can capture plans manually by using the DBMS_SPM package.

Automatic Initial Plan Capture

You enable automatic initial plan capture by setting the initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true (the default is false). When enabled, the database automatically creates a SQL plan baseline for any repeatable SQL statement executed on the database.

If automatic initial plan capture is enabled, and if the database executes a repeatable SQL statement, then the capture algorithm is as follows:

  • If a SQL plan baseline does not exist, then the optimizer creates a plan history and SQL plan baseline for the statement, marking the initial plan for the statement as accepted and adding it to the SQL plan baseline.

  • If a SQL plan baseline exists, then the optimizer behavior depends on the cost-based plan derived at parse time:

    • If this plan does not match a plan in the SQL plan baseline, then the optimizer marks the new plan as unaccepted and adds it to the SQL plan baseline.

    • If this plan does match a plan in the SQL plan baseline, then nothing is added to the SQL plan baseline.

The following graphic shows the decision tree for automatic initial plan capture when OPTIMIZER_USE_SQL_PLAN_BASELINES is set to true (see "Plan Selection" for more information):

Description of the illustration tgsql_vm_004.eps follows
Description of the illustration tgsql_vm_004.eps

Note:

The settings of OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and OPTIMIZER_USE_SQL_PLAN_BASELINES are independent. For example, if OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is true, then the database creates initial plan baselines regardless of whether OPTIMIZER_USE_SQL_PLAN_BASELINES is true or false.

See Also:

Oracle Database Reference to learn about the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter

Manual Plan Capture

In SQL plan management, manual plan capture refers to the user-initiated bulk load of existing plans into a SQL plan baseline. Use Cloud Control or PL/SQL to load the execution plans for SQL statements from a SQL tuning set (STS), the shared SQL area, a staging table, or a stored outline.

The following graphic illustrates loading plans into a SQL plan baseline.

Description of the illustration tgsql_vm_007.eps follows
Description of the illustration tgsql_vm_007.eps

The loading behavior varies depending on whether a SQL plan baseline exists for each statement represented in the bulk load:

  • If a baseline for the statement does not exist, then the database does the following:

    1. Creates a plan history and plan baseline for the statement

    2. Marks the initial plan for the statement as accepted

    3. Adds the plan to the new baseline

  • If a baseline for the statement exists, then the database does the following:

    1. Marks the loaded plan as accepted

    2. Adds the plan to the plan baseline for the statement without verifying the plan's performance

Manually loaded plans are always marked accepted because the optimizer assumes that any plan loaded manually by the administrator has acceptable performance.

Plan Selection

SQL plan selection is the optimizer ability to detect plan changes based on stored plan history, and the use of SQL plan baselines to select plans to avoid potential performance regressions.

When the database performs a hard parse of a SQL statement, the optimizer generates a best-cost plan. By default, the optimizer then attempts to find a matching plan in the SQL plan baseline for the statement. If no plan baseline exists, then the database runs the statement with the best-cost plan.

If a plan baseline exists, then the optimizer behavior depends on whether the newly generated plan is in the plan baseline:

  • If the new plan is in the baseline, then the database executes the statement using the found plan.

  • If the new plan is not in the baseline, then the optimizer marks the newly generated plan as unaccepted and adds it to the plan history. Optimizer behavior depends on the contents of the plan baseline:

    • If fixed plans exist in the plan baseline, then the optimizer uses the fixed plan (see "Fixed Plans") with the lowest cost.

    • If no fixed plans exist in the plan baseline, then the optimizer uses the baseline plan with the lowest cost.

    • If no reproducible plans exist in the plan baseline, which could happen if every plan in the baseline referred to a dropped index, then the optimizer uses the newly generated cost-based plan.

The following graphic shows the decision tree for SQL plan selection.

Description of the illustration tgsql_vm_003.eps follows
Description of the illustration tgsql_vm_003.eps

Plan Evolution

In general, SQL plan evolution is the process by which the optimizer verifies new plans and adds them to an existing SQL plan baseline. Specifically, plan evolution consists of the following distinct steps:

  1. Verifying that unaccepted plans perform at least as well as accepted plans in a SQL plan baseline (known as plan verification)

  2. Adding unaccepted plans to the plan baseline as accepted plans after the database has proved that they perform as well as accepted plans

In the standard case of plan evolution, the optimizer performs the preceding steps sequentially, so that a new plan is not usable by SQL plan management until the optimizer verifies plan performance relative to the SQL plan baseline. However, you can configure SQL plan management to perform one step without performing the other. The following graphic shows the possible paths for plan evolution:

Description of the illustration tgsql_vm_023.eps follows
Description of the illustration tgsql_vm_023.eps

Purpose of Plan Evolution

Typically, a SQL plan baseline for a SQL statement starts with a single accepted plan. However, some SQL statements perform well when executed with different plans under different conditions. For example, a SQL statement with bind variables whose values result in different selectivities may have several optimal plans. Creating a materialized view or an index or repartitioning a table may make current plans more expensive than other plans.

If new plans were never added to SQL plan baselines, then the performance of some SQL statements might degrade. Thus, it is sometimes necessary to evolve newly accepted plans into SQL plan baselines. Plan evolution prevents performance regressions by verifying the performance of a new plan before including it in a SQL plan baseline.

PL/SQL Procedures for Plan Evolution

The DBMS_SPM package provides procedures and functions for plan evolution. These procedures use the task infrastructure. For example, CREATE_EVOLVE_TASK creates an evolution task, whereas EXECUTE_EVOLVE_TASK executes it. All task evolution procedures have the string EVOLVE_TASK in the name.

Use the evolve procedures on demand, or configure the procedures to run automatically. The automatic maintenance task SYS_AUTO_SPM_EVOLVE_TASK executes daily in the scheduled maintenance window. The task perform the following actions automatically:

  1. Selects and ranks unaccepted plans for verification

  2. Accepts each plan if it satisfies the performance threshold

See Also:

Storage Architecture for SQL Plan Management

This section describes the SQL plan management storage architecture:

SQL Management Base

The SQL management base (SMB) is a logical repository in the data dictionary that contains the following:

  • SQL statement log, which contains only SQL IDs

  • SQL plan history, which includes the SQL plan baselines

  • SQL profiles

  • SQL patches

The SMB stores information that the optimizer can use to maintain or improve SQL performance.

The SMB resides in the SYSAUX tablespace and uses automatic segment-space management. Because the SMB is located entirely within the SYSAUX tablespace, the database does not use SQL plan management and SQL tuning features when this tablespace is unavailable.

The following graphic illustrates the SMB architecture.

Description of the illustration tgsql_vm_002.eps follows
Description of the illustration tgsql_vm_002.eps

Note:

Data visibility and privilege requirements may differ when using the SMB with pluggable databases. See Oracle Database Administrator's Guide for a table that summarizes how manageability features work in a container database (CDB).

See Also:

Oracle Database Administrator's Guide to learn about the SYSAUX tablespace

SQL Statement Log

When automatic SQL plan capture is enabled, the SQL statement log contains the SQL ID of SQL statements that the optimizer has evaluated over time. The database tracks a statement when its SQL ID exists in the SQL statement log. When the database parses or executes a statement that is tracked, the database recognizes it as a repeatable SQL statement.

See Also:

Example 23-1 Logging SQL Statements

This example illustrates how the database tracks statements in the statement log and creates baselines automatically for repeatable statements. An initial query of the statement log shows no tracked SQL statements. After a query of hr.jobs for AD_PRES, the log shows one tracked statement.

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

SQL> SELECT * FROM SQLLOG$;
 
no rows selected
 
SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';
 
JOB_TITLE
-----------------------------------
President
 
SQL> SELECT * FROM SQLLOG$;
 
 SIGNATURE     BATCH#
---------- ----------
1.8096E+19          1

Now the session executes a different jobs query. The log shows two tracked statements:

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT * FROM SQLLOG$;
 
 SIGNATURE     BATCH#
---------- ----------
1.7971E+19          1
1.8096E+19          1

A query of DBA_SQL_PLAN_BASELINES shows that no baseline for either statement exists because neither statement is repeatable:

SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
no rows selected

The session executes the query for job_id='PR_REP' a second time. Because this statement is now repeatable, and because automatic SQL plan capture is enabled, the database creates a plan baseline for this statement. The query for job_id='AD_PRES' has only been executed once, so no plan baseline exists for it.

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
SQL_HANDLE           SQL_TEXT
-------------------- --------------------
SQL_f9676a330f972dd5 SELECT job_title FRO
                     M hr.jobs WHERE job_
                     id='PR_REP'

SQL Plan History

The SQL plan history is the set of plans generated for a repeatable SQL statement over time. The history contains both SQL plan baselines and unaccepted plans.

In SQL plan management, the database detects plan changes and records the new plan in the history so that the DBA can manually evolve (verify) it. Because ad hoc SQL statements do not repeat and so do not have performance degradation, the database maintains plan history only for repeatable SQL statements.

Starting in Oracle Database 12c, the SMB stores the rows for new plans added to the plan history of a SQL statement. The DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function fetches and displays the plan from the SMB. For plans created before Oracle Database 12c, the function must compile the SQL statement and generate the plan because the SMB does not store the rows.

See Also:

Enabled Plans

An enabled plan is eligible for use by the optimizer. The database automatically marks all plans in the plan history as enabled even if they are still unaccepted. You can manually change an enabled plan to a disabled plan, which means the optimizer can no longer use the plan even if it is accepted.

Accepted Plans

A plan is accepted if and only if it is in the plan baseline. The plan history for a statement contains all plans, both accepted and unaccepted. After the optimizer generates the first accepted plan in a plan baseline, every subsequent unaccepted plan is added to the plan history, awaiting verification, but is not in the SQL plan baseline.

Figure 23-1 shows plan histories for three different SQL statements. The SQL plan baseline for one statement contains two accepted plans. The plan history for this statement includes two unaccepted plans. A DBA has marked one unaccepted plan as disabled so that the optimizer cannot use it.

Figure 23-1 SQL Plan Management Architecture

Description of
Description of "Figure 23-1 SQL Plan Management Architecture"
Fixed Plans

A fixed plan is an accepted plan that is marked as preferred, so that the optimizer considers only the fixed plans in the baseline. Fixed plans influence the plan selection process of the optimizer.

Assume that three plans exist in the SQL plan baseline for a statement. You want the optimizer to give preferential treatment to only two of the plans. As shown in Figure 23-2, you mark these two plans as fixed so that the optimizer uses only the best plan from these two, ignoring the other plans.

Figure 23-2 Fixed Plans

Description of
Description of "Figure 23-2 Fixed Plans"

If new plans are added to a baseline that contains at least one enabled fixed plan, then the optimizer cannot use the new plans until you manually declare them as fixed.

User Interfaces for SQL Plan Management

Access the DBMS_SPM package through Cloud Control or through the command line.

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. Access the Database Home page, as described in "Accessing the Database Home Page in Cloud Control."

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

    The SQL Plan Control page appears.

  3. Click Files to view the SQL Plan Baseline subpage, shown in Figure 23-3.

    Figure 23-3 SQL Plan Baseline Subpage

    Description of
    Description of "Figure 23-3 SQL Plan Baseline Subpage"

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

See Also:

DBMS_SPM Package

On the command line, use the DBMS_SPM and DBMS_XPLAN PL/SQL packages to perform most SQL plan management tasks. Table 23-1 describes the most relevant DBMS_SPM procedures and functions for creating, dropping, and loading SQL plan baselines.

Table 23-1 DBMS_SPM Procedures and Functions

Package Procedure or Function Description

DBMS_SPM

CONFIGURE

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

DBMS_SPM

CREATE_STGTAB_BASELINE

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

DBMS_SPM

DROP_SQL_PLAN_BASELINE

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

DBMS_SPM

LOAD_PLANS_FROM_CURSOR_CACHE

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

DBMS_SPM

LOAD_PLANS_FROM_SQLSET

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

DBMS_SPM

PACK_STGTAB_BASELINE

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

DBMS_SPM

UNPACK_STGTAB_BASELINE

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

DBMS_XPLAN

DISPLAY_SQL_PLAN_BASELINE

This function displays one or more execution plans for the SQL statement identified by SQL handle.

"About the DBMS_SPM Evolve Functions" describes the functions related to SQL plan evolution.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SPM package

Basic Tasks in SQL Plan Management

This section explains the basic tasks in using SQL plan management to prevent plan regressions and permit the optimizer to consider new plans. The tasks are as follows:

Configuring SQL Plan Management

This section contains the following topics:

Configuring the Capture and Use of SQL Plan Baselines

You control SQL plan management with initialization parameters. 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. See "Automatic Initial 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 nonaccepted plans. See "Plan Selection".

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 (shown in Figure 23-3).

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 SQL statement not already in the plan history. This parameter does not control the automatic addition of newly discovered plans to a previously created SQL plan baseline.

Caution:

When automatic baseline capture is enabled, the database creates a SQL plan baseline for every 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 enable automatic initial plan capture for SQL plan management:

  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 enable the automatic recognition of repeatable SQL statements and the generation of SQL plan baselines for these statements, enter the following statement:

    SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

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

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.

By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window. The SPM Evolve Advisor task ranks all unaccepted plans, and then performs test executions of as many plans as possible during the window. The evolve task selects the lowest-cost plan to compare against each unaccepted plan. If a plan performs sufficiently better than the existing accepted plan, then the database automatically accepts it. The task can accept more than one plan.

Enabling and Disabling the 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. See "Enabling and Disabling the Automatic SQL Tuning Task" to learn how to enable and disable Automatic SPM Evolve Advisor.

Configuring the Automatic SPM Evolve Advisor Task

The DBMS_SPM package enables you to configure automatic plan evolution by specifying the task parameters using the SET_EVOLVE_TASK_PARAMETER procedure. Because the task is owned by SYS, only SYS can set task parameters.

The ACCEPT_PLANS tuning task parameter 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.

Assumptions

The tutorial in this section assumes the following:

  • You do not want the database to evolve plans automatically.

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

To set automatic evolution task parameters:

  1. Connect SQL*Plus to the database with the appropriate privileges, and then optionally query the current task settings.

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

    COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a10
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 = 'TIME_LIMIT') ) );

    Sample output appears as follows:

    PARAMETER_NAME            VALUE
------------------------- ----------
ACCEPT_PLANS              TRUE
TIME_LIMIT                3600

  2. 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 sets a time limit to 20 minutes, and also automatically accepts plans:

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

See Also:

Oracle Database PL/SQL Packages and Types Reference for complete reference information for DBMS_SPM

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. Table 23-2 describes some function parameters.

Table 23-2 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 23-3.

To display SQL plans:

  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:

Loading SQL Plan Baselines

You can initiate the user-initiated bulk load of a set of existing plans into a SQL plan baseline. The goal of this task is to load plans from the following sources:

  • 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.

    Note:

    You can load plans from Automatic Workload Repository snapshots into an STS, and then load plans from the STS into the SQL plan baseline.

  • 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.

  • 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 "Migrating Stored Outlines to SQL Plan Baselines ".

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SPM.PACK_STGTAB_BASELINE Function

Loading Plans from a SQL Tuning Set

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

Load plans with the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function or using Cloud Control. Table 23-3 describes some function parameters.

Table 23-3 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.

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 23-3) 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.

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 cnt NUMBER
EXECUTE :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 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 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, which means that the plan was loaded by an end user 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:

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function

Loading Plans from the Shared SQL Area

This section explains how to load plans from the shared SQL area using PL/SQL.

Load plans with the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. Table 23-4 describes some function parameters.

Table 23-4 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 (see "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 23-3) and click Load to load plan baselines from the shared SQL area.

Assumptions

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 cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
                    sql_id => '27m0sdw9snw59');

    In the preceding example, the variable 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_gdkvzfhrgkda MANUAL-LOAD    YES YES
                      *                    71694fc6b
                      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 for the statement. Also, the origin is MANUAL-LOAD, which means that the statement was loaded by an end user rather than automatically captured.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn how to use the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function

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.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.

The following graphic depicts the basic steps.

Description of the illustration tgsql_vm_006.eps follows
Description of the illustration tgsql_vm_006.eps

Assumptions

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
  my_plans number;
BEGIN
  my_plans := 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
  my_plans NUMBER;
BEGIN
  my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE (
    table_name => 'stage1'
,   fixed      => 'yes'
);
END;
/

See Also:

Evolving SQL Plan Baselines Manually

Oracle recommends that you configure the SQL Plan Management Evolve task to run automatically, as explained in "Managing the SPM Evolve Advisor Task". You can also 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:

About the DBMS_SPM Evolve Functions

Table 23-5 describes the most relevant DBMS_SPM procedures and functions for managing plan evolution. Execute evolution tasks manually or schedule them to run automatically.

Table 23-5 DBMS_SPM Functions and Procedures for Managing Plan Evolution Tasks

Package Procedure or Function Description

DBMS_SPM

ACCEPT_SQL_PLAN_BASELINE

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

DBMS_SPM

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.

DBMS_SPM

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.

DBMS_SPM

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.

DBMS_SPM

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.

DBMS_SPM

SET_EVOLVE_TASK_PARAMETER

This function updates the value of an evolve task parameter. In this release, the only valid parameter is TIME_LIMIT.

Oracle recommends that you configure SPM Evolve Advisor to run automatically (see "Configuring the Automatic SPM Evolve Advisor Task"). You can also evolve SQL plan baselines manually. Figure 23-4 shows the basic workflow for managing SQL plan management tasks.

Figure 23-4 Evolving SQL Plan Baselines

Description of
Description of "Figure 23-4 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:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SPM package

Managing an Evolve Task

This section describes a typical use case in which you create and execute a task, and then implements its recommendations. Table 23-6 describes some parameters of the CREATE_EVOLVE_TASK function.

Table 23-6 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 (shown in Figure 23-3), select a plan, and then click Evolve.

Assumptions

This tutorial assumes the following:

  • You do not have the automatic evolve task enabled (see "Managing the SPM Evolve Advisor Task").

  • 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.

    Example 23-2 executes the following query (sample output included).

    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 Example 23-2.

  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.

    Example 23-3 executes the following query (sample output included).

    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 nonindexed 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 Example 23-2.

  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       |                  |267996|10987836|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|   89334| 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:

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

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

    Example 23-3 executes the following query (sample output included).

    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:

Oracle Database PL/SQL Packages and Types Reference for information about using the DBMS_SPM evolve functions

Example 23-2 DBA_SQL_PLAN_BASELINES

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_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO
                     up_by */ prod_na
                     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

Example 23-3 DBA_SQL_PLAN_BASELINES

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_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO
                     y */ prod_name, sum(
                     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_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
                     y */ prod_name, sum(
                     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

Example 23-4 DBA_SQL_PLAN_BASELINES

SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN       ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES YES
                     y */ prod_name, sum(
                     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_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
                     y */ prod_name, sum(
                     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

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. Table 23-8 describes input parameters.

Table 23-7 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 (shown in Figure 23-3), select a plan, and then click Drop.

Assumptions

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.

    Example 23-5 executes the following query (sample output included).

  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

Example 23-5 DBA_SQL_PLAN_BASELINES

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_bdc6jswfd303v2f1e9c20 AUTO-CAPTURE   YES YES
                     _sql */ count(*) fro
                     m hr.jobs

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 explains how to change the disk space usage parameters for the SMB, and change the retention time for plans in the SMB.

The DBA_SQL_MANAGEMENT_CONFIG view shows the current configuration settings for the SMB. Table 23-8 describes the parameters in the PARAMETER_NAME column.

Table 23-8 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.

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 about the CONFIGURE function

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 23-3).

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 about the DBMS_SPM.CONFIGURE procedure