Configuration options for SQL Plan Management (SPM)

The DBMS_SPM.CONFIGURE package provides various configuration options for SQL Plan Management (SPM). It allows you to control different aspects of plan capture, plan selection, and plan retention.
Here are the few details and example commands for using DBMS_SPM.CONFIGURE:


1.Automatic Plan Capture Configuration:
   
  AUTO_CAPTURE: Specifies whether automatic plan capture is enabled for the entire database.

  
  -- Enable automatic plan capture
  
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE', 'TRUE');

  -- Disable automatic plan capture
  
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE', 'FALSE');


2.AUTO_CAPTURE_PDB_STATS: Specifies whether automatic plan capture is enabled for pluggable databases (PDBs) individually.

    
 -- Enable automatic plan capture for PDBs
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PDB_STATS', 'TRUE');
    
    
 -- Disable automatic plan capture for PDBs
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PDB_STATS', 'FALSE');

   
3.Automatic Plan Synchronization Configuration:


 AUTO_PURGE: Specifies whether to automatically purge plans that are no longer accepted or used.
  
 -- Enable automatic plan purging
EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE', 'TRUE');


 -- Disable automatic plan purging
EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE', 'FALSE');



4.AUTO_PURGE_CANCELED_PLANS: Specifies whether to automatically purge plans that have been canceled.
    
 -- Enable automatic purging of canceled plans
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE_CANCELED_PLANS', 'TRUE');
    
    
 -- Disable automatic purging of canceled plans
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE_CANCELED_PLANS', 'FALSE');



5.Optimizer Mode Configuration:
    
 OPTIMIZER_MODE: Sets the optimizer mode to be used for selecting plans. Options are 'ALL_ROWS', 'FIRST_ROWS', or 'CHOOSE'.

    
 -- Set optimizer mode to 'ALL_ROWS'
    
 EXEC DBMS_SPM.CONFIGURE('OPTIMIZER_MODE', 'ALL_ROWS');
    
    
 -- Set optimizer mode to 'FIRST_ROWS'
    
 EXEC DBMS_SPM.CONFIGURE('OPTIMIZER_MODE', 'FIRST_ROWS');
    
    
 -- Set optimizer mode to 'CHOOSE'
    
 EXEC DBMS_SPM.CONFIGURE('OPTIMIZER_MODE', 'CHOOSE');


 
6.Plan Retention Configuration:
    
 PLAN_RETENTION_ENABLED: Specifies whether plan retention is enabled.

  
 -- Enable plan retention

 EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_ENABLED', 'TRUE');
 

-- Disable plan retention
 
EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_ENABLED', 'FALSE');


 
7.PLAN_RETENTION_WEEKS: Sets the number of weeks that plans should be retained in the SQL plan baseline.
    
  -- Set plan retention to 26 weeks
    
  EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS', 26);
  
 
8.Verification and Reporting Configuration:

  
  VERIFICATION_ENABLED: Specifies whether plan verification is enabled.
  
  -- Enable plan verification

  EXEC DBMS_SPM.CONFIGURE('VERIFICATION_ENABLED', 'TRUE');


  -- Disable plan verification
  
EXEC DBMS_SPM.CONFIGURE('VERIFICATION_ENABLED', 'FALSE');



9.VERIFICATION_RATE: Sets the rate at which SQL execution plans are verified.

  -- Set verification rate to 50%
o    
  EXEC DBMS_SPM.CONFIGURE('VERIFICATION_RATE', 50);

 
10.SPACE_BUDGET_PERCENT: Parameter specifies the maximum percentage of the shared pool that can be used for storing SQL plan baselines.
  -- Set space usage to 10%
  
EXEC DBMS_SPM.CONFIGURE('space_budget_percent', 10);