Tuesday, May 26, 2009

Oracle Statspack

Oracle STATSPACK installation steps:
Refer the below link for statspack installation and scheduling the same for automatic statspack data collection.

Overview of STATSPACK Scripts:
The STATSPACK scripts have completely changed. All of the STATSPACK scripts are located in the $ORACLE_HOME/rdbms/admin directory. Here is the link to understand the importance of the scripts.

Oracle Statspack Maintenance:
The Statspack data maintenance scripts must be run as the PERFSTAT user.

SPPURGE.SQL: Purges a limited range of Snapshot IDs for a given database instance.
SPTRUNC.SQL: Truncates all performance data in Statspack tables
SPUEXP.PAR: An export parameter file supplied for exporting the whole PERFSTAT user.

This is how you can know what is going on with the statspack owner and tablespace usage.

SQL> select OWNER, sum(bytes)/1024/1024 "Size in MB" from dba_segments where TABLESPACE_NAME='PERFSTAT' group by OWNER order by sum(bytes)/1024/1024 desc;
OWNER Size in MB
------------------------------ ----------
PERFSTAT 21238.6094
1 row selected.

This is how we can know how long the maintenance has not been done on statspack data. This will also give an idea about the statspack data availability.

SQL> select to_char(min(SNAP_TIME), 'YYYYMMDD HH24:MI:SS') "Oldest Snap", to_char(max(SNAP_TIME), 'YYYYMMDD HH24:MI:SS') "Latest Snap", round(max(SNAP_TIME)-min(SNAP_TIME)) "sppurge run before x days" from stats$snapshot;
Oldest Snap Latest Snap sppurge run before x days
----------------- ----------------- -------------------------
20071025 15:00:01 20090526 10:15:03 579

Based on the above information you can decide whether you want to keep the data or do some maintenance like purging old statspack data to avoid the growth of the tablespace.

No comments:

Post a Comment