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);

Move a LOBSEGMENT (Large Object Segment) to another tablespace in an Oracle database

To move a LOBSEGMENT (Large Object Segment) to another tablespace in an Oracle database, you can follow these steps:

 

Get lob Segment Details:

SELECT table_name, column_name, segment_name, tablespace_name

FROM dba_lobs

WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

 

Move to other tablespace:

ALTER TABLE your_schema.your_table MOVE LOB(column_name) STORE AS (TABLESPACE new_tablespace);

Replace 'your_schema' with the schema name, 'your_table' with the table name, 'column_name' with the name of the LOB column, and 'new_tablespace' with the name of the new tablespace where you want to move the LOB segment.

After the LOB segment has been moved, you can verify

SELECT table_name, column_name, segment_name, tablespace_name  FROM dba_lobs  WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

Ensure that the tablespace_name column shows the name of the new tablespace where the LOB segment resides.

 

Note: Moving a LOB segment is a resource-intensive operation, and it should be performed during a maintenance window or a period of low database activity to minimize impact. Additionally, take appropriate backups before making any changes to critical database objects.