So we have several Virtual-DBA clients that come to us that either had previous DBAs onsite or previous remote DBA services. Everyone has different ways of setting up performance monitoring of databases, though a potentional problem that I see a lot is the installation of statspack without some kind of automated snapshot purge job. The reason this is an issue is due to the fact that statspack collects a lot of data and will fill up a 32GB file rather quickly. Some people just add a couple more datafiles instead of purging the old information.
I personally believe that if you’re not troubleshooting an issue it’s extra resources being used without much use most of the time. I usually keep statspack off unless I’m researching a performance problem or I need them for some kind of timed health report of a database. Now there are always exceptions and everyone has different opinions. I respect others opinion’s as everyone is entitled to their own, so I can’t say that one way or the other is wrong. This is just my personal thoughts on the subject. Even my own teammates may or may not agree with me. The way I see it is if you’re openminded to other’s opinions you’re able to have a reasonable debate with another DBA as to why it should be one way over the other for a particular environment.
Though when I do need to keep statspack running, I try to keep the snapshots purged by a time limit. The way I do this is by creating a copy of the following script: $ORACLE_HOME/rdbms/admin/sppurge.sql. I then modify script like below.
Before line 105 in the above mentioned script I add the below code snippet.
column max_snap_id new_val HiSnapId column min_snap_id new_val LoSnapId select min(s.snap_id) min_snap_id, max(s.snap_id) max_snap_id from stats$snapshot s, stats$database_instance di where s.dbid = :dbid and di.dbid = :dbid and s.instance_number = :inst_num and di.instance_number = :inst_num and di.startup_time = s.startup_time and s.snap_time < sysdate - 30;
What this does is autopopulate the hisnapid and losnapid based on the time limit you specified with the s.snap_time < sysdate – ndays.
I then usually use crontab to schedule this new script to be run on a rountine basis as needed and that’s it. When the script runs it will purge the snapshots older than the number of days specified in the snipet above. I hope this helps someone keep their snapshots and statspack tablespace in check without too much human intervention.