I have just installed Statspack on my Prod dbase. Everything is setup and ready to roll. One of the questions I have and cant seem to get an answer for is as follows:
I want to set the Snapshots so it automaticaly starts at say 9:30am (snap id 1) and finishes at 7:00pm(snap id 2). I can then run the report the following day and archive them off etc. My database gets shutdown each night to do a full backup so I need to start the snapshot at the start of each day. I hope this makes sense.
You didn't specify your OS and version, so I'm assuming Solaris 8 and 8.1.7.
You're biggest issue is that your DB is shutdown every night. In you're exact scenario, I would setup a cron job to take a snapshot at 09:30 and then take a snapshot at 19:00. You would have to be smart enough to understand that only the snapshots between 09:30 and 19:00 were valid and not try to calculate across daily boundries.
Personally, I would setup a job that ran every 30 minutes between 09:30 and 19:00 that took a snapshot. This way, you could still take your 09:30 - 19:00 as well as looking at other "busy" periods during the day. I think this would give you more information in the long run.
Thats great, The server I'm using is a HP9000 l-class server with Oracle 22.214.171.124.0
I hadnt thought about a cron job? do I just need to incorporate the spauto.sql into a ksh script so it starts at 8:00. How would I stop it? Is there a better way of doing this? If you can think of some way I might be able to do this that would be great.
Thanks very much.
Setup a cronjob that executes a script similar to the following for your "snapshot".
This script will take a snapshot of the current statistics and then delete entries older than 60 days old (to keep your statspack data tidy).
exec > /dev/null 2>&1
sqlplus perfstat/perfstat << EOF
delete from stats\$snapshot where snap_time < sysdate - 60;
Then, you can run spreport for any of the defined time periods as long as you don't cross day boundries.
You are a star, I will set this up and let you know I how I get on.
This site is perfect isnt it?