statspack snapshot settings

[loaded1]

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.

Thanks
Bruce

[marist89]

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.

[loaded1]

Hi there

Thats great, The server I'm using is a HP9000 l-class server with Oracle 8.1.7.0.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.

[marist89]

Setup a cronjob that executes a script similar to the following for your "snapshot".

#!/bin/ksh

exec > /dev/null 2>&1

sqlplus perfstat/perfstat << EOF
exec statspack.snap;
delete from stats\$snapshot where snap_time < sysdate - 60;
commit;
EOF

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).

Then, you can run spreport for any of the defined time periods as long as you don't cross day boundries.

[loaded1]

Jeff,

You are a star, I will set this up and let you know I how I get on.

This site is perfect isnt it?