DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: statspack snapshot settings

  1. #1
    Join Date
    Sep 2001
    Location
    London
    Posts
    26

    Question

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter

  3. #3
    Join Date
    Sep 2001
    Location
    London
    Posts
    26
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Setup a cronjob that executes a script similar to the following for your "snapshot".

    Code:
    #!/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.
    Jeff Hunter

  5. #5
    Join Date
    Sep 2001
    Location
    London
    Posts
    26
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width