Monthly Archives: April 2011

SPM(SQL plan management) Purging

Oracle April 22, 2011 Leave a comment

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;

ADR Purging

Oracle April 22, 2011 Leave a comment

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:

  • TRACE
  • CDUMP
  • UTSCDMP
  • IPS

2.  LONGP_POLICY (Default is 8760 hours or 365 days). LONGP_POLICY is used for the following:

  • ALERT
  • INCIDENT
  • SWEEP
  • STAGE
  • HM

Significance of De-normalization

Oracle April 1, 2011 Comments: 5
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.