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
Leave a Reply