/*
|| Oracle 11g SQL Plan Management Listing 3
||
|| Demonstrates Oracle 11g SQL Plan Management(SPM) advanced techniques,
|| including:
|| - Using the OPTIMIZER_FEATURES_ENABLE initialization parameter in concert with 
||   SPM and SQL Performance Analyzer to capture plans that perform better in a
||   pre-11g optimizer environment
|| - Controlling the "evolution" of SQL Plan Baselines
|| - Manually purging SQL Plan Baselines from the SQL Management Baseline (SMB)
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| SQL Plan Management features, and they should be carefully proofread
|| before executing them against any existing Oracle database(s) to avoid
|| potential damage!
*/

/* 
|| Listing 3.1:
|| Clear selected SQL Plan Baselines created via prior scenarios
*/

CONNECT / AS SYSDBA

-----
-- Clear out the SMB for Baselines labelled as "SPM_2"
-----
SET SERVEROUTPUT ON
VARIABLE nRtnCode NUMBER;
BEGIN
    :nRtnCode := 0;
    FOR r_SPMB IN (
		SELECT sql_handle, plan_name
          FROM dba_sql_plan_baselines
         WHERE sql_text LIKE '%SPM_2%'
	)
        LOOP
            :nRtnCode :=
    			DBMS_SPM.DROP_SQL_PLAN_BASELINE(r_SPMB.sql_handle, r_SPMB.plan_name);
			DBMS_OUTPUT.PUT_LINE('Drop of SPBs for Handle ' || r_SPMB.sql_handle
                || ' and Plan ' || r_SPMB.plan_name
                || ' completed: RC = ' || :nRtnCode);
		END LOOP;
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Fatal error during cleanup of SQL Plan Baselines!');
    	ROLLBACK;
END;
/

/* 
|| Listing 3.2:
|| Create a new SQL Performance Analyzer task via DBMS_SQLPA
*/

BEGIN 
    DBMS_SQLPA.DROP_ANALYSIS_TASK(
		 task_name => 'SPA_SPM_300'
	);
END;
/

VARIABLE tname VARCHAR2(30);
VARIABLE ename VARCHAR2(30);

BEGIN
    :tname := 
        DBMS_SQLPA.CREATE_ANALYSIS_TASK(
             sqlset_name => 'STS_SPM_200'
            ,basic_filter => NULL
            ,order_by => NULL
            ,top_sql => NULL
            ,task_name => 'SPA_SPM_300'
            ,description => 'Analyze STS_SPM_200'
            ,sqlset_owner => 'SYS'
        ); 

    -----
    -- Set SQL Performance Analyzer parameters apppropriately
    -----
	DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
         task_name => 'SPA_SPM_300'
        ,parameter => 'TIME_LIMIT'
        ,value => 'UNLIMITED'
    ); 					    
    DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
         task_name => 'SPA_SPM_300'
        ,parameter => 'LOCAL_TIME_LIMIT'
        ,value => 'UNLIMITED'
    );

    -----
    -- Reset the SQL Performance Analyzer task
    -----
    DBMS_SQLPA.RESET_ANALYSIS_TASK(
         task_name => 'SPA_SPM_300'
    );

END;
/

/* 
|| Listing 3.3:
|| Evaluate the captured sample workload using the newly-created SQL Performance 
|| Analyzer task using two different settings for OPTIMIZER_FEATURES_ENABLE
*/

-----
-- Evaluate the SQL workload using 10.2.0.1 optimizer settings
-----
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.1';
    
BEGIN
    :ename := 
        DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
             task_name => :tname
            ,execution_type => 'TEST EXECUTE'
            ,execution_name => 'OFE_10210'
            ,execution_desc => 'STS_SPM_200: OPTIMIZER_FEATURES_ENABLE = 10.2.0.1'
        ); 
END;
/

-----
-- Evaluate the same SQL workload using 11.1.0.6 optimizer settings
-----
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE = '11.1.0.6';

BEGIN
    :ename := 
        DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
            task_name => :tname
            ,execution_type => 'TEST EXECUTE'
            ,execution_name => 'OFE_11106'
            ,execution_desc => 'STS_SPM_200: OPTIMIZER_FEATURES_ENABLE = 11.1.0.6'
        ); 
END;
/

/* 
|| Listing 3.4:
|| Generate a performance comparison using DBMS_SQLPA to identify any regressing
|| statements
*/

-----
-- Compare the performance of the SQL workload to find any regressing statements. Note that
-- the comparison metric used is not optimizer cost, but total elapsed time (ELAPSED_TIME)
-----
BEGIN
    DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
         task_name => :tname
        ,execution_type => 'compare performance'
        ,execution_name => 'SPA_SPM_200_ET'
        ,execution_desc => 'SPA_SPM_200 Elapsed Time Performance Comparison'
        ,execution_params => 
            DBMS_ADVISOR.ARGLIST(
                 'EXECUTION_NAME1','OFE_10210'
                ,'EXECUTION_NAME2','OFE_11106'
                ,'COMPARISON_METRIC','ELAPSED_TIME'
            )
    );
END;
/

-----
-- Generate the "delta" report to identify any regressing statements
-----
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
    tLOB CLOB;
BEGIN
    SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:tname)
      INTO tLOB
      FROM DUAL;
    DBMS_OUTPUT.PUT_LINE(tLOB);
END;
/

>> Results:

General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : SPA_SPM_300                     SQL Tuning Set Name        : STS_SPM_200
  Task Owner   : SYS                             SQL Tuning Set Owner       : SYS
  Description  : Analyze STS_SPM_200             Total SQL Statement Count  : 5

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name  : SPA_SPM_200_ET                                     Started             : 03/05/2008 08:09:28
  Execution Type  : COMPARE PERFORMANCE                                Last Updated        : 03/05/2008 08:09:28
  Description     : SPA_SPM_200 Elapsed Time Performance Comparison    Global Time Limit   : UNLIMITED
  Scope           : COMPREHENSIVE                                      Per-SQL Time Limit  : UNLIMITED
  Status          : COMPLETED                                          Number of Errors    : 0

Analysis Information:
---------------------------------------------------------------------------------------------
 Comparison Metric: ELAPSED_TIME
 ------------------
 Workload Impact Threshold: 1%
 --------------------------

 SQL Impact Threshold: 1%
 ----------------------
 Before Change Execution:                                                   After Change Execution:
 ---------------------------------------------                              ---------------------------------------------
  Execution Name      : OFE_10210                                            Execution Name      : OFE_11106
  Execution Type      : TEST EXECUTE                                         Execution Type      : TEST EXECUTE
  Description         : STS_SPM_200: OPTIMIZER_FEATURES_ENABLE = 10.2.0.1    Description         : STS_SPM_200: OPTIMIZER_FEATURES_ENABLE = 11.1.0.6
  Scope               : COMPREHENSIVE                                        Scope               : COMPREHENSIVE
  Status              : COMPLETED                                            Status              : COMPLETED
  Started             : 03/05/2008 08:06:36                                  Started             : 03/05/2008 08:06:49
  Last Updated        : 03/05/2008 08:06:48                                  Last Updated        : 03/05/2008 08:06:59
  Global Time Limit   : UNLIMITED                                            Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNLIMITED                                            Per-SQL Time Limit  : UNLIMITED
  Number of Errors    : 0
Number of Errors    : 0

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

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  12.26%
 Improvement Impact  :  36.23%
 Regression Impact   :  -23.97% 

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count 
 Overall               5                  0 
 Improved              3                  0 
 Regressed             2                  0 

Projected Workload Performance Distribution
--------------------------------------------------------------------
|          | Cumulative Perf. |        | Cumulative Perf. |        |
| Bucket   | Before Change    | (%)    | After Change     | (%)    |
--------------------------------------------------------------------
| < = 1024 |                0 |     0% |              979 | 10.24% |
| < = 2048 |             2594 | 23.81% |             4970 |    52% |
| < = 4096 |             8299 | 76.19% |             3609 | 37.76% |
--------------------------------------------------------------------


Single SQL Statement Execution Count Distribution
-------------------------------------------------------
-------------------------------------------------------
|          | SQL Count     |     | SQL Count    |     |
| Bucket   | Before Change | (%) | After Change | (%) |
-------------------------------------------------------
| < = 1024 |             0 |  0% |            1 | 20% |
| < = 2048 |             2 | 40% |            3 | 60% |
| < = 4096 |             3 | 60% |            1 | 20% |
-------------------------------------------------------

SQL Statements Sorted by their Absolute Value of Change Impact on the Workload
--------------------------------------------------------------------------------------------------------
|           |               | Impact on | Metric | Metric | Impact  | % Workload | % Workload | Plan   |
| object_id | sql_id        | Workload  | Before | After  | on SQL  | Before     | After      | Change |
--------------------------------------------------------------------------------------------------------
|        24 | 2j6cr9y9xhszr |   -19.96% |   1435 |   3609 | -151.5% |     13.17% |     37.76% | n      |
|        26 | 06bj931mb8xt0 |    16.27% |   2751 |    979 |  64.41% |     25.25% |     10.24% | n      |
|        23 | ggps96xa80818 |    14.07% |   3425 |   1892 |  44.76% |     31.44% |     19.79% | n      |
|        25 | 6g5dzywfmr5u5 |     5.88% |   2123 |   1482 |  30.19% |     19.49% |     15.51% | n      |
|        22 | b935ym1bprqkr |    -4.01% |   1159 |   1596 |  -37.7% |     10.64% |      16.7% | n      |
--------------------------------------------------------------------------------------------------------

/* 
|| Listing 3.5:
|| Capture SQL Plan Baselines for the pre-11g execution plans into for the two SQL
|| statements with regressed performance
*/
VARIABLE plans_loaded NUMBER;
BEGIN
    :plans_loaded :=
    	DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    		 sqlset_name => 'STS_SPM_200'
    		,sqlset_owner => 'SYS'
            ,basic_filter => 'sql_id = ''2j6cr9y9xhszr'''
    	);
    :plans_loaded :=
    	DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    		 sqlset_name => 'STS_SPM_200'
    		,sqlset_owner => 'SYS'
            ,basic_filter => 'sql_id = ''b935ym1bprqkr'''
    	);
END;
/

>>> Results:


Thu Mar 06                                                                                                    page    1
                                               Current SQL Plan Baselines
                                             (From DBA_SQL_PLAN_BASELINES)

         SQL      Plan                                                 CBO Ena-           Auto Created     Last
Creator  Handle   Name     SQL Text                  Origin           Cost bled Acpt Fixd Purg On          Executed
-------- -------- -------- ------------------------- ------------ -------- ---- ---- ---- ---- ----------- -----------
LDGN     68516a84 07e0351f SELECT /*SPM_1.1*/        AUTO-CAPTURE      757 YES  YES  NO   YES  2008-01-20  2008-01-20
                                S.cust_id                                                      10:47:14    10:47:31
                                ,C.cust_last_name
                               ,S.prod_id
                                ,P.pro

LDGN     68516a84 ddc1fcd0 SELECT /*SPM_1.1*/        AUTO-CAPTURE     2388 YES  NO   NO   YES  2008-01-20
                                S.cust_id                                                      11:04:03
                                ,C.cust_last_name
                               ,S.prod_id
                                ,P.pro

SYS      587c0594 825d2e47 SELECT /*SPM_2_1.1*/    MANUAL-LOAD     2267 YES  YES  NO   YES  2008-03-06
                                CTY.country_total_id                                           07:13:52

                               ,PR.promo_total_id
                               ,

SYS      8ec1a586 2d9d97db SELECT /*SPM_2_1.3*/    MANUAL-LOAD     8359 YES  YES  NO   YES  2008-03-06
                                CTY.country_total_id                                           07:13:52

                               ,P.prod_id
                               ,P.prod_s

/* 
|| Listing 3.6:
|| Clear the test environment:
|| 1.) Clear the SMB's contents
|| 2.) Flush the Library Cache and Database Buffer Cache
|| 3.) Drop the index on SH.CUSTOMERS.CUST_LAST_NAME 
|| 4.) Gather statistics on SH.CUSTOMERS
*/

CONNECT / as sysdba

SET SERVEROUTPUT ON
VARIABLE nRtnCode NUMBER;
BEGIN
    :nRtnCode := 0;
    FOR r_SPMB IN (
		SELECT sql_handle, plan_name
          FROM dba_sql_plan_baselines
         WHERE sql_text LIKE '%SPM_%'
	)
        LOOP
            :nRtnCode :=
    			DBMS_SPM.DROP_SQL_PLAN_BASELINE(r_SPMB.sql_handle, r_SPMB.plan_name);
			DBMS_OUTPUT.PUT_LINE('Drop of SPBs for Handle ' || r_SPMB.sql_handle
                || ' and Plan ' || r_SPMB.plan_name
                || ' completed: RC = ' || :nRtnCode);
		END LOOP;
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Fatal error during cleanup of SQL Plan Baselines!');
    	ROLLBACK;
END;
/

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

DROP INDEX sh.customers_ln_asc_idx;

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SH', tabname =>'CUSTOMERS', cascade => TRUE);
END;
/

/* 
|| Listing 3.7:
|| Capture a SQL Plan Baseline directly from the Library Cache. Note that
|| automatic plan capture is deactivated for just this session
*/

CONNECT ldgn/ldgn
ALTER SESSION SET optimizer_capture_sql_plan_baselines=FALSE;
ALTER SESSION SET optimizer_use_sql_plan_baselines=FALSE;

@SPM_3_1.sql;

SET SERVEROUTPUT ON
VARIABLE plans_loaded NUMBER;
BEGIN
    :plans_loaded := 
        DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
             attribute_name => 'SQL_TEXT'
            ,attribute_value => '%SPM_3_1%'
            ,fixed => 'NO'
            ,enabled => 'YES'
        );
	DBMS_OUTPUT.PUT_LINE('>>> ' || :plans_loaded	 || ' SQL Plan Baseline(s) were loaded from the cursor cache.');
END;
/

/* 
|| Listing 3.8:
|| Prepare the test environment:
|| 1.) Create a non-unique index on SH.CUSTOMERS.CUST_LAST_NAME
|| 2.) Gather statistics on SH.CUSTOMERS
|| 3.) Activate automatic plan capture for just this session
|| 4.) Re-execute the original statement
*/

CONNECT / as sysdba
CREATE INDEX sh.customers_ln_asc_idx
    ON sh.customers (cust_last_name);
BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SH', tabname =>'CUSTOMERS', cascade => TRUE);
END;
/

CONNECT ldgn/ldgn
ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;
ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;

@SPM_3_1.sql;

/* 
|| Listing 3.9:
|| Request the evolution of any "superior" SQL Plan Baselines. The result is
|| captured from a CLOB datatype returned by function EVOLVE_SQL_PLAN_BASELINE
*/

CONNECT ldgn/ldgn

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET PAGESIZE 1000
VARIABLE evolution_rpt CLOB;
BEGIN
    :evolution_rpt := 
        DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
             sql_handle => NULL
            ,plan_name => NULL
            ,time_limit => DBMS_SPM.AUTO_LIMIT
            ,verify => 'NO'
            ,commit => 'YES'
        );
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(:evolution_rpt));
END;
/

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

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

Plan: SYS_SQL_PLAN_679b8840f5624fda
-----------------------------------
  Plan was changed to an accepted plan.

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 0.
Number of SQL plan baselines evolved: 1.

-----
-- Show the post-evolutionary status of selected SQL Plan Baselines. Note that
-- the Baseline that was originally loaded via manual methods is still available,
-- but that the automatically-loaded Baseline has taken advantage of the improved
-- performance the new index provides
-----

                                               Current SQL Plan Baselines
                                             (From DBA_SQL_PLAN_BASELINES)

         SQL      Plan                                                 CBO Ena-           Auto Created     Last
Creator  Handle   Name     SQL Text                  Origin           Cost bled Acpt Fixd Purg On          Executed
-------- -------- -------- ------------------------- ------------ -------- ---- ---- ---- ---- ----------- -----------
LDGN     679b8840 63495bf7 SELECT /*SPM_3.1*/        MANUAL-LOAD       912 YES  YES  NO   YES  2008-03-16  2008-03-16
                                S.cust_id                                                      18:23:59    18:24:04
                                ,C.cust_last_name
                               ,S.prod_id
                                ,P.pro

LDGN     679b8840 f5624fda SELECT /*SPM_3.1*/        AUTO-CAPTURE      518 YES  YES  NO   YES  2008-03-16
                                S.cust_id                                                      18:24:04
                                ,C.cust_last_name
                               ,S.prod_id
                                ,P.pro

SQL> EXPLAIN PLAN FOR
SELECT /*SPM_3.1*/
     S.cust_id
        ,C.cust_last_name
    ,S.prod_id
        ,P.prod_name
    ,SUM(S.amount_sold)
    ,SUM(S.quantity_sold)
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.products P
 WHERE S.cust_id = C.cust_id
   AND S.prod_id = P.prod_id
   AND S.prod_id BETWEEN :beg_prod_id AND :end_prod_id
   AND C.cust_last_name LIKE :cust_last_name
 GROUP BY 
     S.cust_id
    ,C.cust_last_name
    ,S.prod_id
    ,P.prod_name
 ORDER BY 
     S.cust_id
    ,C.cust_last_name
    ,S.prod_id
    ,P.prod_name
;
SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'));

Plan hash value: 1357501156

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |     6 |   360 |   597   (4)| 00:00:08 |       |       |
|   1 |  SORT GROUP BY                  |                      |     6 |   360 |   597   (4)| 00:00:08 |       |       |
|*  2 |   FILTER                        |                      |       |       |            |          |       |       |
|*  3 |    HASH JOIN                    |                      |     6 |   360 |   596   (4)| 00:00:08 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID | PRODUCTS             |     1 |    30 |     2   (0)| 00:00:01 |       |       |
|*  5 |      INDEX RANGE SCAN           | PRODUCTS_PK          |     2 |       |     1   (0)| 00:00:01 |       |       |
|*  6 |     HASH JOIN                   |                      |  2297 | 68910 |   593   (4)| 00:00:08 |       |       |
|   7 |      TABLE ACCESS BY INDEX ROWID| CUSTOMERS            |  2775 | 36075 |    92   (0)| 00:00:02 |       |       |
|*  8 |       INDEX RANGE SCAN          | CUSTOMERS_LN_ASC_IDX |   499 |       |     3   (0)| 00:00:01 |       |       |
|   9 |      PARTITION RANGE ALL        |                      |  2297 | 39049 |   500   (4)| 00:00:07 |     1 |    28 |
|* 10 |       TABLE ACCESS FULL         | SALES                |  2297 | 39049 |   500   (4)| 00:00:07 |     1 |    28 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:BEG_PROD_ID)<=TO_NUMBER(:END_PROD_ID))
   3 - access("S"."PROD_ID"="P"."PROD_ID")
   5 - access("P"."PROD_ID">=TO_NUMBER(:BEG_PROD_ID) AND "P"."PROD_ID"<=TO_NUMBER(:END_PROD_ID))
   6 - access("S"."CUST_ID"="C"."CUST_ID")
   8 - access("C"."CUST_LAST_NAME" LIKE :CUST_LAST_NAME)
       filter("C"."CUST_LAST_NAME" LIKE :CUST_LAST_NAME)
  10 - filter("S"."PROD_ID">=TO_NUMBER(:BEG_PROD_ID) AND "S"."PROD_ID"<=TO_NUMBER(:END_PROD_ID))

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_679b8840f5624fda" used for this statement

/* 
|| Listing 3.10:
|| Altering attributes of existing SQL Plan Baselines
*/

-----
-- Execute some SQL statements and then capture them into the SMB as 
-- SQL Plan Baselines
-----
CONNECT ldgn/ldgn;

@SPM_3_2;

SET SERVEROUTPUT ON
VARIABLE plans_loaded NUMBER;
BEGIN
    :plans_loaded := 
        DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
             attribute_name => 'SQL_TEXT'
            ,attribute_value => '%SPM_3_2%'
            ,fixed => 'NO'
            ,enabled => 'YES'
        );
	DBMS_OUTPUT.PUT_LINE('>>> ' || :plans_loaded	 || ' SQL Plan Baseline(s) were loaded from the cursor cache.');
END;
/

-----
-- Modify selected attributes of specific SQL Plan Baselines:
-----
VARIABLE plans_modified NUMBER;

-----
-- Disable specific SQL Plan Baselines
-----
BEGIN
    -- SPM_3_2.1
    :plans_modified := 
        DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle => 'SYS_SQL_604ef8151b0e7db6'
            ,plan_name => 'SYS_SQL_PLAN_1b0e7db6505a2954'
            ,attribute_name => 'ENABLED'
            ,attribute_value => 'NO'
    ); 
    -- SPM_3_2.3
    :plans_modified := 
        DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle => 'SYS_SQL_b5cc235bd31714d1'
            ,plan_name => 'SYS_SQL_PLAN_d31714d1c3252e8f'
            ,attribute_name => 'ENABLED'
            ,attribute_value => 'NO'
    ); 
END;
/

-----
-- Stop a SQL Plan Baseline from being automatically purged
-----
BEGIN
    -- SPM_3_2.2
    :plans_modified := 
        DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle => 'SYS_SQL_c5cf845190dc96f2'
            ,plan_name => 'SYS_SQL_PLAN_90dc96f25c1c114f' 
            ,attribute_name => 'AUTOPURGE'
            ,attribute_value => 'NO'
    ); 
END;
/

-----
-- Mark a specific SQL Plan Baseline as "fixed"
-----
BEGIN
    -- SPM_3_2.2
    :plans_modified := 
        DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle => 'SYS_SQL_c5cf845190dc96f2'
            ,plan_name => 'SYS_SQL_PLAN_90dc96f25c1c114f' 
            ,attribute_name => 'FIXED'
            ,attribute_value => 'YES'
    ); 
END;
/

-----
-- Enable specific SQL Plan Baseline(s)
-----
BEGIN
    -- SPM_3_2.3
    :plans_modified := 
        DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle => 'SYS_SQL_b5cc235bd31714d1'
            ,plan_name => 'SYS_SQL_PLAN_d31714d1c3252e8f'
            ,attribute_name => 'ENABLED'
            ,attribute_value => 'YES'
    ); 
END;
/

/* 
|| Listing 3.11:
|| Changing defaults for SQL Plan Baseline Management
*/

BEGIN
    -- Set the SMB's space budget to 20% (the default is 10%)
    DBMS_SPM.CONFIGURE(
         attribute_name => 'space_budget_percent'
        ,attribute_value => '20'
    ); 
    -- Set the SMB's plan retention weeks to 26 (the default is 53 weeks)
    DBMS_SPM.CONFIGURE(
         attribute_name => 'plan_retention_weeks'
        ,attribute_value => '26'
    ); 
END;
/