23 Managing SQL Plan Baselines

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

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

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

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

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

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.

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

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

23.1.5 Storage Architecture for SQL Plan Management

This section describes the SQL plan management storage architecture:

• SQL Management Base

• SQL Statement Log

• SQL Plan History

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

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

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

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

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'

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

• "Displaying Plans in a SQL Plan Baseline"

• Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function

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

23.1.5.3.2 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 "Figure 23-1 SQL Plan Management Architecture"

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

23.1.6 User Interfaces for SQL Plan Management

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

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

• Set initialization parameters to control whether the database captures and uses SQL plan baselines, and whether it evolves new plans.

  See "Configuring SQL Plan Management".

• Display plans in a SQL plan baseline.

  See "Displaying Plans in a SQL Plan Baseline".

• Manually load plans into SQL plan baselines.

  Load plans from SQL tuning sets, the shared SQL area, a staging table, or stored outlines.

  See "Loading SQL Plan Baselines".

• Manually evolve plans into SQL plan baselines.

  Use PL/SQL to verify the performance of specified plans and add them to plan baselines.

  See "Evolving SQL Plan Baselines Manually".

• Drop all or some plans in SQL plan baselines.

  See "Dropping SQL Plan Baselines".

• Manage the SMB.

  Alter disk space limits and change the length of the plan retention policy.

  See "Managing the SQL Management Base".

• Migrate stored outlines to SQL plan baselines.

  See "Migrating Stored Outlines to SQL Plan Baselines".

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

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

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

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.

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

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.

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

   Example 23-2 DBA_SQL_PLAN_BASELINES

   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 /*LOAD_STS*/  SQL_PLAN_ahstbv1bu98ky1694fc6b MANUAL-LOAD    YES YES
                            *
                            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' );
   

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

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.

   Example 23-3 queries DBA_SQL_PLAN_BASELINES (sample output included).

   Example 23-3 DBA_SQL_PLAN_BASELINES

   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_gdkvzfhrgkda71694fc6b MANUAL-LOAD    YES YES
                            *
                            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.

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

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

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

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

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

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-4 executes the following query (sample output included).

   Example 23-4 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_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES 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
   

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

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-5 executes the following query (sample output included).

   Example 23-5 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
   

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

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_SOLD| 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-5 executes the following query (sample output included).

    Example 23-6 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
    

    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;
    

23.7.1 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
   

23.7.2 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