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;
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 ADR purging policies in the blog. The defaults are sufficient for most databases. You can change the defaults using ADRCI.
ADR Purging Policy
ADR has 2 types of policies. Both the policies are specified in hours.
1. SHORTP_POLICY (Default is 720 hour or 30 days). SHORTP_POLICY is used for the following:
2. LONGP_POLICY (Default is 8760 hours or 365 days). LONGP_POLICY is used for the following:
De-normalization is adding of redundant data to already normalized data set to improve performance without incurring additional data maintenance and sacrificing the integrity of data
Why should you De-normalize?
While normalization makes it easy to input correct data , it also makes it difficult to get it out. This is why you should de-normalization your data. The advantages of de-normalization are faster selects & reduced joins at the cost of slower updates and additional storage.
Personally I have seen up to 300% performance improvement with de-normalization when complex joins are involved. I fully support exploring of de-normalization to dba’s/developers.