Load plans with the LOAD_PLANS_FROM_AWR function of the DBMS_SPM package. The following table 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 28-1) and click Load to load plan baselines from AWR.

To load plans from the shared SQL area:

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

   For example, query DBA_HIST_SNAPSHOT as follows:

   SELECT *
   FROM   (SELECT SNAP_ID, SNAP_LEVEL, 
                  TO_CHAR(BEGIN_INTERVAL_TIME, 'DD/MM/YY HH24:MI:SS') BEGIN
           FROM   DBA_HIST_SNAPSHOT
           ORDER BY SNAP_ID DESC)
   WHERE   ROWNUM <= 3;
   
      SNAP_ID SNAP_LEVEL BEGIN
   ---------- ---------- -----------------
          212	    1 10/12/15 06:00:02
          211	    1 10/12/15 05:00:11
          210	    1 10/12/15 04:00:59
   

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

   For example, use the following query:

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

3. Take a new AWR snapshot.

   For example, use the following program:

   EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

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

   For example, query DBA_HIST_SNAPSHOT as follows:

   SELECT *
   FROM   (SELECT SNAP_ID, SNAP_LEVEL, 
                  TO_CHAR(BEGIN_INTERVAL_TIME, 'DD/MM/YY HH24:MI:SS') BEGIN
           FROM   DBA_HIST_SNAPSHOT
           ORDER BY SNAP_ID DESC)
   WHERE   ROWNUM <= 3;
   
      SNAP_ID SNAP_LEVEL BEGIN
   ---------- ---------- -----------------
          213	    1 10/12/15 06:24:53
          212	    1 10/12/15 06:00:02
          211	    1 10/12/15 05:00:11

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

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

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

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

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

   The following statement queries DBA_SQL_PLAN_BASELINES (sample output included):

   COL SQL_HANDLE FORMAT a20
   COL SQL_TEXT FORMAT a20
   COL PLAN_NAME FORMAT a30
   COL ORIGIN FORMAT a20
   
   SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
          ORIGIN, ENABLED, ACCEPTED
   FROM   DBA_SQL_PLAN_BASELINES
   WHERE SQL_TEXT LIKE '%LOAD_AWR%';
   
   SQL_HANDLE           SQL_TEXT          PLAN_NAME                  ORIGIN       ENA ACC
   -------------------- ----------------- -------------------------- -----------  --- ---
   SQL_495d29c5f4612cda SELECT /*LOAD_AWR SQL_PLAN_4kr99sru62b6u54bc MANUAL-LOAD- YES YES
                        */ * FROM         8843                       FROM-AWR 
                        sh.sales WHERE  
                        quantity_sold 
                        > 40
                        ORDER BY prod_id

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

Load plans with the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. The following table 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 28-1) and click Load to load plan baselines from the shared SQL area.

To load plans from the shared SQL area:

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

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

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

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

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

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

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

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

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

   The following statement queries DBA_SQL_PLAN_BASELINES (sample output included):

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

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

Load plans with the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function or using Cloud Control. The following table 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 28-1) and click Load to load plan baselines from SQL tuning sets.

Assumptions

This tutorial assumes the following:

• You want the loaded plans to be nonfixed.

• You have executed the following query:

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

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

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

To load plans from a SQL tuning set:

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

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

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

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

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

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

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

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

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

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

   SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
     2         ORIGIN, ENABLED, ACCEPTED
     3  FROM   DBA_SQL_PLAN_BASELINES;
   
   SQL_HANDLE            SQL_TEXT        PLAN_NAME        ORIGIN               ENA ACC
   --------------------- --------------- ---------------- -------------------- --- ---
   SQL_a8632bd857a4a25e  SELECT          SQL_PLAN_ahstb   MANUAL-LOAD-FROM-STS YES YES
                         /*LOAD_STS*/*   v1bu98ky1694fc6b
                         FROM sh.sales
                         WHERE 
                         quantity_sold 
                         > 40 ORDER BY                    
                         prod_id

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

4. Optionally, drop the STS.

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

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

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

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

Figure 28-3 Loading Plans from a Staging Table

Description of "Figure 28-3 Loading Plans from a Staging Table"

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

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

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

   The following example creates a staging table named stage1:

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

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

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

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

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

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

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

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

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

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

The following table 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, select a plan, and then click Evolve.

To evolve a specified plan:

1. Perform the initial setup as follows:

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

      ALTER SYSTEM FLUSH SHARED_POOL;
      ALTER SYSTEM FLUSH BUFFER_CACHE;
      

   2. Enable the automatic capture of SQL plan baselines.

      For example, enter the following statement:

      ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
      

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

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

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

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

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

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

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

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

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

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

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

   The following statement queries DBA_SQL_PLAN_BASELINES (sample output included):

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

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

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

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

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

   Sample output appears below:

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

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

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

   For example, use the following statements:

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

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

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

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

   The following statement queries DBA_SQL_PLAN_BASELINES (sample output included).

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

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

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

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

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

   Sample output appears below:

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

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

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

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

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

   The following sample output shows the name of the task:

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

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

10. Execute the task.

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

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

11. View the report.

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

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

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

12. Implement the recommendations of the evolve task.

    For example, execute the IMPLEMENT_EVOLVE_TASK function:

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

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

    The query provides the following sample output:

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

    The output shows that the new plan is accepted.

14. Clean up after the example.

    For example, enter the following statements:

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

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

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

To change the plan retention period for the SMB:

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

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

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

2. Change the retention period.

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

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

3. Query the data dictionary to confirm the change.

   For example, execute the following query:

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