Oracle11g has introduced a lot of very good features.  It is very important to understand the default purging policies s of these features.  I will discuss  SPM purging policies in the blog

SPM Purging Policy

1. Plan Retention

SPM purging Policy for plan retention:  The default is 53 weeks. The default unused plan retention period is one year and one week, which means a plan will be automatically purged if it has not been used for more than a year and maximum supported is 10 years.

Example:  BEGIN  DBMS_SPM.CONFIGURE(    ‘plan_retention_weeks’,53); END;

2. Space Usage

SPM purging  for space usage:  The default space for  SPM is no more than 10% of the size of SYSAUX tablespace.   You can allocate up to 50% of the space for SPM in SYSAUX tablespace.

Example: BEGIN  DBMS_SPM.CONFIGURE(‘space_budget_percent’,30);END;

Tagged: DBMS_SPM.CONFIGURE, plan_retention_weeks, space_budget_percent, SPM, SQL plan management