Category - TUNING

The SQL PLAN Management - The Practical

Managing SQL Plan Baselines

This chapter explains the concepts and tasks relating to SQL plan management using the DBMS_SPM package.
In below section we will cover Confgure to Drop of SQL PLAN MANAGEMENT.

A => Configuring SQL Plan Management

You control SQL plan management with the initialization parameters OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and OPTIMIZER_USE_SQL_PLAN_BASELINES.

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.

· 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 unaccepted plans.

SQL> show parameter sql_plan

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>
SQL> alter system set optimizer_capture_sql_plan_baselines=TRUE;

System altered.

SQL> show parameter sql_plan

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>

NOTE => As soon as we set parameter optimizer_capture_sql_plan_baselines = true all repeatable statements will be captured in Baseline.
               Thus, automatic capture may result in the creation of an extremely large number of plan baselines.
               To limit the statements that are eligible for plan baselines, configure filters using the DBMS_SPM.CONFIGURE procedure.
               Let we see demo as below.

Demo

Step 1 => First let we check if any Filter is configured or Not as below.

SQL>
PARAMETER_NAME			 PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_SQL_TEXT
AUTO_SPM_EVOLVE_TASK		 OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL	 3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
PLAN_RETENTION_WEEKS		 53
SPACE_BUDGET_PERCENT		 10
9 rows selected.
SQL> /

So as we can see NO FILTERS are used for SPM.

Step 2=> Check current value of sql_plan parameter as below

SQL> show parameter sql_plan

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE

Step 3 => Now we will verify if any plan exists for ABHI_TEST user in DBA_SQL_PLAN_BASELINES.

SQL> select count(*) from  DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST'  ;

  COUNT(*)
----------
     0

Step 4 => Now let we connect to ABHI_TEST user and execute REPTEATED  statement as below.

[oracle@ace2oracledb ~]$ sqlplus abhi_test/Oracle_4U

SQL> select count(*) from DATA_EMP;
  COUNT(*)
----------
      6500

SQL> /
  COUNT(*)
----------
      6500

Step 5 => Now let we check again from DBA_SQL_PLAN_BASELINES if SQL plan is captured.

SQL> select count(*) from  DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST';
  COUNT(*)
----------
     1
SQL>

So we can see that SQL PLAN is being captured for every REPEATED Query. And due to this SYSAUX tablespace may exhaust , 
so to overcome this problem we need to configure filters using the DBMS_SPM.CONFIGURE procedure.

B =>  Configuring Filters for Automatic Plan Capture

If OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true, then you can use the DBMS_SPM.CONFIGURE procedure to create an automatic capture filter for repeatable statements.

DBMS_SPM.CONFIGURE (
   parameter_name    IN VARCHAR2,
   parameter_value   IN VARCHAR2 := NULL,
   allow             IN BOOLEAN := TRUE);

We can set below allowed parameters for prameter_name variable.



Demo

we will set AUTO_CAPTURE_SQL_TEXT to %TECH_FOR_SPD% to capture ONLY sql which contains TECH_FOR_SPD in SQL Query.

Step 1 => Login to sys and execute as below

SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', '%TECH_FOR_SPD%');

PL/SQL procedure successfully completed.

Step 2 => Let we check numbers of plan captures in base line for ABHI_TEST schema as below.

SQL> select count(*) from  DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST';

  COUNT(*)
----------
     7

Step 3 => Now let we login to ABHI_TEST user and execute 2 diffrrent repeated Qeury as below,

SQL> select count(*) from DEMO_TAB; ( this will not be captured due to filter)

  COUNT(*)
----------
     42100

SQL> /

  COUNT(*)
----------
     42100

Now check if it's got captured.

SQL> select count(*) from  DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST';

  COUNT(*)
----------
     7

as we can see NOT CAPTURED sue to filter.

Now let we execute repeated statement containing  TECH_FOR_SPD

SQL> select count(*) from TECH_FOR_SPD;

  COUNT(*)
----------
     32090

SQL> /

  COUNT(*)
----------
     32090

Now check if it's got captured.

SQL> select count(*) from  DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST';

  COUNT(*)
----------
     8

SO we can see filter is okay now.

Step 4 =>Now we will check Filter and remove FIlter as below.

COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32
SELECT PARAMETER_NAME, PARAMETER_VALUE FROM   DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME             PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_SQL_TEXT         (sql_text LIKE %TECH_FOR_SPD%)
AUTO_SPM_EVOLVE_TASK         OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL     3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
PLAN_RETENTION_WEEKS         53
SPACE_BUDGET_PERCENT         10

9 rows selected.

SQL>

Now let we remove filter as below and set new filter to capture only statement which executed by ABHI_TEST schema.

SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', NULL, NULL);

PL/SQL procedure successfully completed.

SQL> COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32

SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM   DBA_SQL_MANAGEMENT_CONFIG;
SQL> SQL> SQL>   2  
PARAMETER_NAME             PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_SQL_TEXT
AUTO_SPM_EVOLVE_TASK         OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL     3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
PLAN_RETENTION_WEEKS         53
SPACE_BUDGET_PERCENT         10

9 rows selected.

SQL>

SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'ABHI_TEST');

PL/SQL procedure successfully completed.

SQL> COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32

SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM   DBA_SQL_MANAGEMENT_CONFIG;SQL> SQL> SQL>   2  

PARAMETER_NAME             PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (ABHI_TEST)
AUTO_CAPTURE_SQL_TEXT
AUTO_SPM_EVOLVE_TASK         OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL     3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
PLAN_RETENTION_WEEKS         53
SPACE_BUDGET_PERCENT         10

9 rows selected.

C =>  MANUALLY Loading SQL Plan Baselines

In some cases we can not change optimizer_capture_sql_plan_baselines to true due to nature of working for this parameter.

So we need to load PLAN manually in SQL PLAN BASELINES , and for that we have DBMS_SPM package to do the same.

The DBMS_SPM package enables you to load plans from multiple sources.

SQL>
SQL> show parameter sql_plan

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>

The goal of this task is to load plans from the following sources:

1-> Load SQL PLAN from AWR

In this example we will be loeading SQL PLANS from AWR repository with conditions  as below,

DBMS_SPM.LOAD_PLANS_FROM_AWR
   begin_snap      IN  NUMBER,
   end_snap        IN  NUMBER,
   basic_filter    IN  VARCHAR2 := NULL,
   fixed           IN  VARCHAR2 := 'NO',
   enabled         IN  VARCHAR2 := 'YES',
   commit_rows     IN  NUMBER := 1000)
 RETURN PLS_INTEGER;

Here Basic_Filter can be used from SQLSET_ROW.

We will load as per below

Step 1 => Get list of SQL_ID's with thier snap_id's for begin and end interval as below,

col parsed format a6
col sql_text format a40
set lines 200
set pages 300

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 parsing_schema_name like 'ABHI_TEST' and
 parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by snaptime asc;

SQL_TEXT                 PARSED     ELAPSED_SEC    SNAP_ID SNAPTIME       SQL_ID
---------------------------------------- ------ ----------- ---------- -------------- -------------
select count(*) from DATA_EMP         ABHI_TEST     .211495       226 16.02 18:30:24 cj309pqvjgzpb
select count(*) from DEMO_TAB         ABHI_TEST     .058343       227 16.02 19:30:42 9srq6w5hstq74   <<< we will load this SQL ID in SQL PLAN BASELINE
                    

Step 2 => Let we check if any SQL PLAN loaded for DEMO_TAB sql as below.

SQL> 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 '%DEMO_TAB%';

no rows selected

Step 3 => Now we will load in SQL PLANBASELINE as below.

SQL> EXEC :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap => 226,end_snap => 227,basic_filter => 'parsing_schema_name =''ABHI_TEST'' and sql_id = ''9srq6w5hstq74''');

PL/SQL procedure successfully completed.

Step 4 => Let we verify if this is available in SQL PLAN BASELINE.

SQL>  SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
  	    ORIGIN, ENABLED, ACCEPTED
    FROM   DBA_SQL_PLAN_BASELINES
  WHERE SQL_TEXT LIKE '%DEMO_TAB%';
SQL_HANDLE	     SQL_TEXT				      PLAN_NAME 		     ORIGIN		  ENABLED			 ACCEPTED
-------------------- ---------------------------------------- ------------------------------ -------------------- ------------------------------ ------------------------------
SQL_000c4dc6bf8feb39 select count(*) from DEMO_TAB	      SQL_PLAN_0032dsuzszuttf74f07de MANUAL-LOAD-FROM-AWR YES				 YES

So as we can see plan is loaded and ACCEPTED is YES as because of Manual loading method.

2-> Load SQL PLAN from Shared SQL area
This function loads one or more plans present in the cursor cache for a SQL statement, or a set of SQL statements.
It has four overloads: using SQL statement text, using SQL handle, using SQL ID, or using attribute_name and attribute_value pair.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_text          IN  CLOB,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_handle        IN  VARCHAR2,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   attribute_name   IN VARCHAR2,
   attribute_value  IN VARCHAR2,
   fixed            IN VARCHAR2 := 'NO',
   enabled          IN VARCHAR2 := 'YES')
  RETURN PLS_INTEGER;

Loading SQL PLANS from Cursor_Cache as below.

Step 1 =>  Get SQL_ID for sql as below.

SQL> select count(*) from UNIQ_TEST;

 COUNT(*)
----------
     3
SQL> select sql_id from v$sql where sql_text like 'select count(*) from UNIQ_TEST';

SQL_ID
-------------
4gv0au4vua43c

Step 2=>  Check of any sql plan loaded for SQL QUERY

SQL> 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 '%UNIQ_TEST%';SQL> SQL> SQL> SQL> SQL>   2    3    4  

no rows selected
SQL>

Step 3=> Load Using SQL_ID as below

SQL>
VARIABLE v_plan_cnt NUMBER
BEGIN
  :v_plan_cnt:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '4gv0au4vua43c');
END;
/

PL/SQL procedure successfully completed.

Step 3=>  Check if PLAN is available now.

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%UNIQ_TEST%';SQL>   2    3    4  

SQL_HANDLE	     SQL_TEXT		  PLAN_NAME			 ORIGIN 					    ENABLED			   ACCEPTED
-------------------- -------------------- ------------------------------ -------------------------------------------------- ------------------------------ ------------------------------
SQL_5046547a4fdeff45 select count(*) from SQL_PLAN_50jkng97xxzu5c1a7b2a8 MANUAL-LOAD-FROM-CURSOR-CACHE			    YES 			   YES
		      UNIQ_TEST
SQL> 

3-> Load SQL PLAN from SQL tuning set (STS)

This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines.

DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
   sqlset_name      IN  VARCHAR2,
   sqlset_owner     IN  VARCHAR2 := NULL,
   basic_filter     IN  VARCHAR2 := NULL,
   fixed            IN  VARCHAR2 := 'NO',
   enabled          IN  VARCHAR2 := 'YES'
   commit_rows      IN  NUMBER   := 1000)
RETURN PLS_INTEGER


Now we will Load SQL PLAN avaialble  from STS as below

Step 1=>   Let we get available STS in database for user as below

SQL> SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;

NAME				   SQLCNT DESCRIPTION
------------------------------ ---------- --------------------------
SQLT_FROM_AWR				1 STS From awr
SQLT_FROM_TRACE 			2 STS From TRACE
STS_FROM_QUERY				3 Creating STS from Query

Step 2=> Get details of SQL Query from STS for demo purpose as below 

SQL> select sql_text FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_FROM_AWR' ) );

SQL_TEXT
------------------------------
create table DEP_TEST as select * from HR.DEPARTMENTS

Step 3=> Check if SQL PLAN for this Query is already loaded

SQL> 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 '%DEP_TEST%';
SQL> SQL> SQL> SQL> SQL>   2    3    4  
no rows selected

Step 4=>  Let we load SQL PLAN now as below

SQL> VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name  => 'SQLT_FROM_AWR',sqlset_owner => 'ABHI_TEST');
SQL> 
PL/SQL procedure successfully completed.

Step 5=> Check if SQL PLAN for this Query is already loaded 
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 '%DEP_TEST%';
SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4  
SQL_HANDLE	     SQL_TEXT		  PLAN_NAME			 ORIGIN 	      ENABLED			     ACCEPTED
-------------------- -------------------- ------------------------------ -------------------- ------------------------------ ------------------------------
SQL_3452df65a7e5fdbf create table DEP_TES SQL_PLAN_38nqzcqmybzdzf1f8ff0f MANUAL-LOAD-FROM-STS YES			     YES
		     T as select * from H
		     R.DEPARTMENTS

D => PLAN Evolutions through SPM

A newly added plan can be evoluted either Automatically through SYS_AUTO_SPM_EVOLVE_TASK or can be done Manually through DBMS_SPM . We will cover both.

=> Automatic Plan Evolutions using SPM Evolve Advisor Task

SPM Evolve Advisor is a SQL advisor that evolves plans that have recently been added to the SQL plan baseline.

By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window.

A SQL plan baseline prevents performance regressions caused by suboptimal plans.

If a SQL statement does not have a SQL plan baseline, and if the alternate_plan_baseline parameter is set to AUTO,
then SQM Evolve Advisor can sometimes resolve such performance regressions automatically.

The advisor compares all available plans and chooses the best-performing plan as the baseline.
Whenever it runs in the maintenance window, SPM Evolve Advisor performs the following tasks:

=> Checks AWR for top SQL

AWR stores the most resource-intensive SQL statements.
SPM Evolve Advisor searches AWR for statements that are most likely to benefit from SQL plan baselines,
and then adds plans for these statements to the baselines.

=> Looks for alternative plans in all available sources

By default (alternate_plan_source=AUTO), the automatic task searches all available repositories for plans that are not yet in the SMB plan history.
The setting for alternate_plan_source is shown in the DBA_ADVISORS_PARAMETERS view.

=> Adds unaccepted plans to the plan history
These plans are not yet in the SQL plan baseline for any SQL statement.

=> Tests the execution of as many plans as possible during the maintenance window
For every alternative plan, the database test executes the statement and records the performance statistics.
The goal is to use a cost-based algorithm to compare the performance of every alternative plan with the plan that the optimizer would otherwise choose.

=> Performs either of the following actions, depending on whether the alternative plan performs better than the current plan:

If performance is better, then SPM Evolve Advisor accepts the plan. The alternative plan is now in the baseline.
If performance is worse, then the plan remains in the statement history, but not the baseline.

check parameter settings for auto_task as below,

SQL> COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a42
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   DBA_ADVISOR_PARAMETERS
WHERE  TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';SQL> SQL>   2    3  

PARAMETER_NAME    VALUE
------------------------- ------------------------------------------
ALTERNATE_PLAN_LIMIT   UNLIMITED
ALTERNATE_PLAN_SOURCE   AUTO
ALTERNATE_PLAN_BASELINE   AUTO
ACCEPT_PLANS    TRUE
DAYS_TO_EXPIRE    UNLIMITED
JOURNALING    INFORMATION
MODE     COMPREHENSIVE
TARGET_OBJECTS    1
TIME_LIMIT    3600
DEFAULT_EXECUTION_TYPE   SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE  30

32 rows selected.
SQL> 

=> Manually Plan Evolutions using DBMS_SPM

We can use PL/SQL to manually evolve an unaccepted plan to determine whether it performs better than any plan currently in the plan baseline.

Evolving SQL Plan Baselines Manually

You can use PL/SQL or Cloud Control to manually evolve an unaccepted plan to determine whether it performs better than any plan currently in the plan baseline.

Oracle recommends that you configure SPM Evolve Advisor to run automatically. You can also evolve SQL plan baselines manually.
The following graphic shows the basic workflow for managing SQL plan management tasks.

Steps invloved in Manual Plan Evolutions




First we will execute Repeated SQL Query , and when SQL PLAN captured in Baseline we will create index for that table which are part of DEMO QUERY.

So as per rule INDEX would be faster but PLAN baseline would be using FULL TABLE SCAN plan (OLD) So it will regress and thus we will do manual evolution ,

Post manual evolution SQL PLAN with index will be selected for QUERY.

==> check if any Filter configured as below.

SQL> COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32

SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM   DBA_SQL_MANAGEMENT_CONFIG;SQL> SQL> SQL>   2  

PARAMETER_NAME             PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_SQL_TEXT
AUTO_SPM_EVOLVE_TASK         OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL     3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
PLAN_RETENTION_WEEKS         70
SPACE_BUDGET_PERCENT         30

9 rows selected.

SQL>
SQL>

===> check sql_plan value. It must be true to create initial plan load for demo.
SQL> show parameter sql_plan

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>

==> Now let we execute repeated SQL QUERY from ABH_tEST schema as below and see if plan baseling got created.

SQL>  SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold)
FROM   PROD p, sales s
WHERE  p.prod_id = s.prod_id
AND    p.prod_category_id =203
GROUP BY prod_name;   2    3    4    5  

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
18" Flat Panel Graphics Monitor                  5205
Standard Mouse                            10156
Model A3827H Black Image Cartridge                20490
SIMM- 16MB PCMCIAII card                    15950
Multimedia speakers- 5" cones                    11253
Envoy External 8X CD-ROM                    17430
Model SM26273 Black Ink Cartridge                19233
PCMCIA modem/fax 19200 baud                    22768
Model CD13272 Tricolor Ink Cartridge                15984
SIMM- 8MB PCMCIAII card                     19557
Internal 8X CD-ROM                        13319
17" LCD w/built-in HDTV Tuner                     6010
Multimedia speakers- 3" cones                    12163
Envoy External 6X CD-ROM                    13008
Deluxe Mouse                            12837
PCMCIA modem/fax 28800 baud                    20948
External 8X CD-ROM                        16494
Internal 6X CD-ROM                         9523
Model NM500X High Yield Toner Cartridge              7557
Envoy External Keyboard                      3441
External 6X CD-ROM                        13043

21 rows selected.

SQL>
SQL>
SQL> /

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
18" Flat Panel Graphics Monitor                  5205
Standard Mouse                            10156
Model A3827H Black Image Cartridge                20490
SIMM- 16MB PCMCIAII card                    15950
Multimedia speakers- 5" cones                    11253
Envoy External 8X CD-ROM                    17430
Model SM26273 Black Ink Cartridge                19233
PCMCIA modem/fax 19200 baud                    22768
Model CD13272 Tricolor Ink Cartridge                15984
SIMM- 8MB PCMCIAII card                     19557
Internal 8X CD-ROM                        13319
17" LCD w/built-in HDTV Tuner                     6010
Multimedia speakers- 3" cones                    12163
Envoy External 6X CD-ROM                    13008
Deluxe Mouse                            12837
PCMCIA modem/fax 28800 baud                    20948
External 8X CD-ROM                        16494
Internal 6X CD-ROM                         9523
Model NM500X High Yield Toner Cartridge              7557
Envoy External Keyboard                      3441
External 6X CD-ROM                        13043

21 rows selected.

SQL>
SQL> /

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
18" Flat Panel Graphics Monitor                  5205
Standard Mouse                            10156
Model A3827H Black Image Cartridge                20490
SIMM- 16MB PCMCIAII card                    15950
Multimedia speakers- 5" cones                    11253
Envoy External 8X CD-ROM                    17430
Model SM26273 Black Ink Cartridge                19233
PCMCIA modem/fax 19200 baud                    22768
Model CD13272 Tricolor Ink Cartridge                15984
SIMM- 8MB PCMCIAII card                     19557
Internal 8X CD-ROM                        13319
17" LCD w/built-in HDTV Tuner                     6010
Multimedia speakers- 3" cones                    12163
Envoy External 6X CD-ROM                    13008
Deluxe Mouse                            12837
PCMCIA modem/fax 28800 baud                    20948
External 8X CD-ROM                        16494
Internal 6X CD-ROM                         9523
Model NM500X High Yield Toner Cartridge              7557
Envoy External Keyboard                      3441
External 6X CD-ROM                        13043

21 rows selected.

 ==>  Let we check of plan baseline got created.
SQL> 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

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED, FIXED
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_TEXT LIKE '%test_for_man_evol%' and SQL_TEXT NOT LIKE '%SQL_HANDLE%' ;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4  

SQL_HANDLE         SQL_TEXT          PLAN_NAME             ORIGIN       ENA ACC FIX
-------------------- -------------------- ------------------------------ ------------ --- --- ---
SQL_2fbd2ed148e7bfc5  SELECT /* test_for_ SQL_PLAN_2zg9fu54fggy5ff8cca40 AUTO-CAPTURE YES YES NO
             man_evol */ prod_nam
             e, sum(quantity_sold
             )
             FROM   PROD p, sales
              s
             WHERE  p.prod_id = s
             .prod_id
             AND    p.prod_catego
             ry_id =203
             GROUP BY prod_name

SQL>

==>> Now let we execute DEMO Query again an check what is plan in cursor

SQL>  SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold)
FROM   PROD p, sales s
WHERE  p.prod_id = s.prod_id
AND    p.prod_category_id =203
GROUP BY prod_name;   2    3    4    5  

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
18" Flat Panel Graphics Monitor                  5205
Standard Mouse                            10156
Model A3827H Black Image Cartridge                20490
SIMM- 16MB PCMCIAII card                    15950
Multimedia speakers- 5" cones                    11253
Envoy External 8X CD-ROM                    17430
Model SM26273 Black Ink Cartridge                19233
PCMCIA modem/fax 19200 baud                    22768
Model CD13272 Tricolor Ink Cartridge                15984
SIMM- 8MB PCMCIAII card                     19557
Internal 8X CD-ROM                        13319
17" LCD w/built-in HDTV Tuner                     6010
Multimedia speakers- 3" cones                    12163
Envoy External 6X CD-ROM                    13008
Deluxe Mouse                            12837
PCMCIA modem/fax 28800 baud                    20948
External 8X CD-ROM                        16494
Internal 6X CD-ROM                         9523
Model NM500X High Yield Toner Cartridge              7557
Envoy External Keyboard                      3441
External 6X CD-ROM                        13043

21 rows selected.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_cursor());

PLAN_TABLE_OUTPUT
--------------------------
SQL_ID    f7kd44252vg1d, child number 1
-------------------------------------
SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM
PROD p, sales s WHERE  p.prod_id = s.prod_id AND    p.prod_category_id
=203 GROUP BY prod_name

Plan hash value: 865300185

-----------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |        |        |  1241 (100)|        |
|   1 |  HASH GROUP BY        |        |     14 |    574 |  1241   (2)| 00:00:01 |
|*  2 |   HASH JOIN        |        |    183K|  7357K|  1237   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| PROD  |     14 |    476 |      3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES |    918K|  6281K|  1231   (1)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("P"."PROD_ID"="S"."PROD_ID")
   3 - filter("P"."PROD_CATEGORY_ID"=203)

Note
-----
   - SQL plan baseline SQL_PLAN_2zg9fu54fggy5ff8cca40 used for this statement

28 rows selected.

SQL>

So as we can see SQL PLAN BASELINE is being used and this is for FULL TABLE SCAN,

=====>>> Now we will create INDEX as below

SQL> CREATE INDEX ind_prod_cat_name
  ON PROD(prod_category_id, prod_name, prod_id);
CREATE INDEX ind_sales_prod_qty_sold
  ON sales(prod_id, quantity_sold);  2 
Index created.

SQL>   2  

Index created.

SQL> SQL>

===>>> Post that we will gather stats as below .

SQL> @gather_stats.sql
Enter value for owner: ABHI_TEST
Enter value for name: PROD
old   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);
new   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'PROD', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> @gather_stats.sql
Enter value for owner: ABHI_TEST
Enter value for name: SALES
old   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);
new   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);

PL/SQL procedure successfully completed.

SQL>

==>>> Next we will execute repeated  DEMO Query  as below and  check whether SQL PLAN CAPUTRED in BASE LINE or not.

SQL> SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold)
FROM   PROD p, sales s
WHERE  p.prod_id = s.prod_id
AND    p.prod_category_id =203
GROUP BY prod_name;   2    3    4    5  

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
18" Flat Panel Graphics Monitor                  5205
Standard Mouse                            10156
Model A3827H Black Image Cartridge                20490
SIMM- 16MB PCMCIAII card                    15950
Multimedia speakers- 5" cones                    11253
Envoy External 8X CD-ROM                    17430
Model SM26273 Black Ink Cartridge                19233
PCMCIA modem/fax 19200 baud                    22768
Model CD13272 Tricolor Ink Cartridge                15984
SIMM- 8MB PCMCIAII card                     19557
Internal 8X CD-ROM                        13319

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
17" LCD w/built-in HDTV Tuner                     6010
Multimedia speakers- 3" cones                    12163
Envoy External 6X CD-ROM                    13008
Deluxe Mouse                            12837
PCMCIA modem/fax 28800 baud                    20948
External 8X CD-ROM                        16494
Internal 6X CD-ROM                         9523
Model NM500X High Yield Toner Cartridge              7557
Envoy External Keyboard                      3441
External 6X CD-ROM                        13043

21 rows selected.

SQL>
SQL>
SQL> /

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
18" Flat Panel Graphics Monitor                  5205
Standard Mouse                            10156
Model A3827H Black Image Cartridge                20490
SIMM- 16MB PCMCIAII card                    15950
Multimedia speakers- 5" cones                    11253
Envoy External 8X CD-ROM                    17430
Model SM26273 Black Ink Cartridge                19233
PCMCIA modem/fax 19200 baud                    22768
Model CD13272 Tricolor Ink Cartridge                15984
SIMM- 8MB PCMCIAII card                     19557
Internal 8X CD-ROM                        13319

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
17" LCD w/built-in HDTV Tuner                     6010
Multimedia speakers- 3" cones                    12163
Envoy External 6X CD-ROM                    13008
Deluxe Mouse                            12837
PCMCIA modem/fax 28800 baud                    20948
External 8X CD-ROM                        16494
Internal 6X CD-ROM                         9523
Model NM500X High Yield Toner Cartridge              7557
Envoy External Keyboard                      3441
External 6X CD-ROM                        13043

21 rows selected.

SQL>
SQL>
SQL> /

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
18" Flat Panel Graphics Monitor                  5205
Standard Mouse                            10156
Model A3827H Black Image Cartridge                20490
SIMM- 16MB PCMCIAII card                    15950
Multimedia speakers- 5" cones                    11253
Envoy External 8X CD-ROM                    17430
Model SM26273 Black Ink Cartridge                19233
PCMCIA modem/fax 19200 baud                    22768
Model CD13272 Tricolor Ink Cartridge                15984
SIMM- 8MB PCMCIAII card                     19557
Internal 8X CD-ROM                        13319

PROD_NAME                       SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
17" LCD w/built-in HDTV Tuner                     6010
Multimedia speakers- 3" cones                    12163
Envoy External 6X CD-ROM                    13008
Deluxe Mouse                            12837
PCMCIA modem/fax 28800 baud                    20948
External 8X CD-ROM                        16494
Internal 6X CD-ROM                         9523
Model NM500X High Yield Toner Cartridge              7557
Envoy External Keyboard                      3441
External 6X CD-ROM                        13043

21 rows selected.

SQL>
SQL>
SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8834jdxv5mfjs, child number 1
-------------------------------------
SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM
PROD p, sales s WHERE  p.prod_id = s.prod_id AND    p.prod_category_id
=203 GROUP BY prod_name

Plan hash value: 865300185

-----------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |        |        |  1243 (100)|        |
|   1 |  HASH GROUP BY        |        |     21 |    861 |  1243   (2)| 00:00:01 |
|*  2 |   HASH JOIN        |        |    267K|     10M|  1237   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| PROD  |     21 |    714 |      3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES |    918K|  6281K|  1231   (1)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("P"."PROD_ID"="S"."PROD_ID")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - filter("P"."PROD_CATEGORY_ID"=203)

Note
-----
   - SQL plan baseline SQL_PLAN_2zg9fu54fggy5ff8cca40 used for this statement

28 rows selected.

SQL>

=====>>>> CHECK if new SQL PLAN available in Baseline.

 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

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED, FIXED
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_TEXT LIKE '%test_for_man_evol%' and SQL_TEXT NOT LIKE '%SQL_HANDLE%' ;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4  

SQL_HANDLE         SQL_TEXT          PLAN_NAME             ORIGIN       ENA ACC FIX
-------------------- -------------------- ------------------------------ ------------ --- --- ---
SQL_2fbd2ed148e7bfc5  SELECT /* test_for_ SQL_PLAN_2zg9fu54fggy56003e314 AUTO-CAPTURE YES NO  NO
             man_evol */ prod_nam
             e, sum(quantity_sold
             )
             FROM   PROD p, sales
              s
             WHERE  p.prod_id = s
             .prod_id
             AND    p.prod_catego
             ry_id =203
             GROUP BY prod_name

SQL_2fbd2ed148e7bfc5  SELECT /* test_for_ SQL_PLAN_2zg9fu54fggy5ff8cca40 AUTO-CAPTURE YES YES NO
             man_evol */ prod_nam
             e, sum(quantity_sold
             )
             FROM   PROD p, sales
              s
             WHERE  p.prod_id = s
             .prod_id
             AND    p.prod_catego
             ry_id =203
             GROUP BY prod_name

SQL>

as we can see SQL_PLAN_2zg9fu54fggy56003e314 has been created(and this is sql plan post INDEX creation). Now let we see if this is being used in DEMO QUERY post repeated execution.

SQL> SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold)
FROM   PROD p, sales s
WHERE  p.prod_id = s.prod_id
AND    p.prod_category_id =203
GROUP BY prod_name;

SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8834jdxv5mfjs, child number 1
-------------------------------------
SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM
PROD p, sales s WHERE  p.prod_id = s.prod_id AND    p.prod_category_id
=203 GROUP BY prod_name

Plan hash value: 865300185

-----------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |        |        |  1243 (100)|        |
|   1 |  HASH GROUP BY        |        |     21 |    861 |  1243   (2)| 00:00:01 |
|*  2 |   HASH JOIN        |        |    267K|     10M|  1237   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| PROD  |     21 |    714 |      3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES |    918K|  6281K|  1231   (1)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("P"."PROD_ID"="S"."PROD_ID")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - filter("P"."PROD_CATEGORY_ID"=203)

Note
-----
   - SQL plan baseline SQL_PLAN_2zg9fu54fggy5ff8cca40 used for this statement

28 rows selected.

SQL>

AS WE CAN SEE still OLD PLAN BASE LINE is being used (which is for FULL TABLE SCAN).This may create regression .Now we need to evolve that plan as per below mentioned.

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;

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

EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
SELECT :evol_out FROM DUAL;


****PENDING FOR SETUP **


=====>>>>

E => Configuring SQL Management Base

The SQL management base is a part of the data dictionary that resides in the SYSAUX tablespace. 
It stores statement logs, plan histories, SQL plan baselines, and SQL profiles.

About Managing the SMB

Use the DBMS_SPM.CONFIGURE procedure to set configuration options for the SMB and the maintenance of SQL plan baselines.

We can configure below 2 parameters in terms of SQL Management Base .

Parameter                 Description

SPACE_BUDGET_PERCENT => Maximum percent of SYSAUX space that the SQL management base can use. The default is 10. The allowable range for this limit is between 1% and 50%.

PLAN_RETENTION_WEEKS  => Number of weeks to retain unused plans before they are purged. The default is 53.
   
Changing the Disk Space Limit for the SMB

A weekly background process measures the total space occupied by the SMB.

When the defined limit is exceeded, the process writes a warning to the alert log. 
The database generates alerts weekly until either the SMB space limit is increased, the size of the SYSAUX tablespace is increased, or
the disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles). 
This task explains how to change the limit with the DBMS_SPM.CONFIGURE procedure.

SQL> 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							840		     84
SQL> 

Now let we configure limit to 20% as below .

SQL> EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);
PL/SQL procedure successfully completed.
SQL> 

SQL> 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';   2    3    4    5    6    7    8  

PARAMETER_NAME				 %_LIMIT				  SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
---------------------------------------- ---------------------------------------- ----------------- -------------------
SPACE_BUDGET_PERCENT			 30							840		    252

SQL> 

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

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

PARAMETER_NAME				 PARAMETER_VALUE
---------------------------------------- ----------------------------------------
PLAN_RETENTION_WEEKS			 53

SQL> 

We can configure using below method.

SQL> EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',70);
PL/SQL procedure successfully completed.
SQL> 
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
 FROM   DBA_SQL_MANAGEMENT_CONFIG
 WHERE  PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';
   2    3  
PARAMETER_NAME				 PARAMETER_VALUE
---------------------------------------- ----------------------------------------
PLAN_RETENTION_WEEKS			 70

F => Configuring the High-Frequency Automatic SPM Evolve Advisor Task

The high-frequency SPM Evolve Advisor task complements the standard Automatic SPM Evolve Advisor task.
By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled AutoTask maintenance window.
If data changes frequently between two consecutive task executions, then the optimizer may choose suboptimal plans.
For example, if product list prices change more frequently than executions of SYS_AUTO_SPM_EVOLVE_TASK, then more out-of-range queries may occur, possibly leading to suboptimal plans.
When you enable the high-frequency Automatic SPM Evolve Advisor task, SYS_AUTO_SPM_EVOLVE_TASK runs more frequently, 
performing the same operations during every execution. The high-frequency task runs every hour and runs for no longer than 30 minutes. 

Both the standard Automatic SPM Evolve Advisor task and high-frequency task have the same name: SYS_AUTO_SPM_EVOLVE_TASK. 
In DBA_ADVISOR_EXECUTIONS, the two tasks are distinguished by execution name. 
The name of the standard task execution has the form EXEC_number, whereas the name of the high-frequency execution has the form SYS_SPM_timestamp.
DBMS_SPM.CONFIGURE enables the high-frequency task, but has no dependency on the SPM Evolve Advisor. 

Enabling the High-Frequency Automatic SPM Evolve Advisor Task: Tutorial

To enable and disable the high-frequency Automatic SPM Evolve Advisor task, use the DBMS_SPM.CONFIGURE procedure.
You can set auto_spm_evolve_task to any of the following values:
=> ON — Enables the high-frequency SPM Evolve Advisor task.
=> OFF — Disables the high-frequency SPM Evolve Advisor task. This is the default.
=> AUTO — Allows the database to determine when to execute the high-frequency SPM Evolve Advisor task. In this release, AUTO is equivalent to OFF.

Note that the task interval and runtime are fixed and cannot be adjusted by the user. This Feature is currently supported on EXADATA Flavors only.

SQL> COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32
SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM   DBA_SQL_MANAGEMENT_CONFIG
WHERE  PARAMETER_NAME LIKE '%SPM%';SQL> SQL>   2    3  

PARAMETER_NAME                   PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_SPM_EVOLVE_TASK             OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL    3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800

3 rows selected.

SQL> alter system set "_exadata_feature_on"=TRUE scope=spfile;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.
Total System Global Area 3388995104 bytes
Fixed Size                  8902176 bytes
Variable Size             687865856 bytes
Database Buffers         2684354560 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');

PL/SQL procedure successfully completed.

SQL> SET LINESIZE 150
COL TASK_NAME FORMAT a30
COL EXECUTION_NAME FORMAT a30

SELECT TASK_NAME, EXECUTION_NAME, STATUS
FROM   DBA_ADVISOR_EXECUTIONS
WHERE  TASK_NAME LIKE '%SPM%'
AND    (EXECUTION_NAME LIKE 'SYS_SPM%' OR EXECUTION_NAME LIKE 'EXEC_%')
ORDER BY EXECUTION_END;SQL> SQL> SQL> SQL>   2    3    4    5  


TASK_NAME                      EXECUTION_NAME                 STATUS
------------------------------ ------------------------------ ---------
SYS_AUTO_SPM_EVOLVE_TASK       SYS_SPM_2019-06-03/13:15:26    COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK       SYS_SPM_2019-06-03/14:16:04    COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK       EXEC_6                         COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK       SYS_SPM_2019-06-03/15:16:32    COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK       SYS_SPM_2019-06-03/16:17:00    COMPLETED
...

7 rows selected.

SQL>
In the preceding output, EXEC_6 is the execution name of the standard SPM Automatic Advisor task. The other executions are of the high-frequency task.