Friday, January 11, 2008

Oracle STATSPACK recreation

Introduction

The PERFSTAT schema (and tablespace) contains all the database’s performance statistics. However over a period of time, this tablespace can grow to a large size and the stored statistical data can be purged to make room for more freespace in this tablespace. Occasionally a complete reorganization of the PERFSTAT tablespace can be performed to delete all the historical statistics data, thus reclaiming disk space consumed for this tablespace.

At first glance, it is easy to think that this reorg procedure can be done hot (while the database applications are up and running). But its not quite true. This post explains the "behind the scenes" part of STATSPACK reorganization and why its not a straightforward procedure.

Background

Our 9.2.0.6 Oracle Applications database's PERFSTAT tablespace grew more than 50GB in size with all the thousands of statspack snaps in the schema and its just the right time for a purge and reorganization of this tablespace. Since perfstat schema is not used by any other jobs except the statspack.snap cronjob, it looked like a straight forward operation and which can be done online. Based on this flawed reasoning, the reorg was completed in our development instance. Soon there were users complaining that they were getting java exceptions once they login to Oracle Applications 11i. All the exceptions were due to the DBMS_UTILITY package getting invalidated. Because statspack could not have done this (this was the flawed assumption until then), we guessed it could have been a result of bad code migration from the applications team.

Whats the unexpected
There were no changes nor were any code migrations from the applications team. Upon further investigation, it was identified that the reinstallation of STATSPACK environment (spcreate.sql) invalidates critical DBMS packages including DBMS_UTILITY, DBMS_SNAPSHOT, DBMS_IREFRESH, UTL_RECOMP, DBMS_STATS, DBMS_PRVTAQIP, DBMS_PCLXUTIL etc and we recompiled them immediately to solve the problem. The metalink note 392352.1 mentions this is an expected outcome, but nothing of that sort is written in the official guide.
  1. Take a list of the invalid objects from dba_objects view
  2. Reorg the statspack environment (drop it and recreate it)
  3. Take a new list of the invalid objects and compare it with the list taken above. Recompile the new objects that are invalid after the reorg.
Conclusion

There is no "big change" or "small change". Every change should be given due respect and tested properly before GOLIVE or else expect the unexpected !
Related Posts Plugin for WordPress, Blogger...