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