SPM(SQL plan management) Purging

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: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: