Statspack Purge.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Statspack Purge.

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    Statspack Purge.

    Hi guys
    I am working on a project that will need STATSPACK.
    I have installed it and also run the spauto.sql to gather hourly snapshot on the database.
    Our goal here is to keep 12 months of hourly snapshot (that 8064 snaps)and purge anything beyond 12 months and I was asked not to use a cron, maestro or any other scheduler to do the purge but to use something similar the Oracle "spauto" that get hourly snap everyday.

    Can sppurge.sql be manipulated and run as dbms_job and act the same way as spauto that you run once and get the snapshot every hour for you and ONLY purge if we have beyond 12 months and if we do not have 12 months of
    snap retrun "no rows purged" or "No snapshot purge is necessary"

    Any idea
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    What version of Oracle? If 10g, Have a look at Automatic Workload Repository? Let MMON do the task of collecting and purging snapshots.

    If earlier than 10g, You have to manually delete statspack report using dbms_job. Make a copy of SPPURGE.sql and then manipulate to your need.
    -- Dilip

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Well use the fact that "STATSPACK uses foreign-key referential integrity constraints with the ON CASCADE DELETE option".

    http://www.dba-oracle.com/tips_oracl...ge_utility.htm

    Code:
    SQL> select SNAP_ID,to_char(SNAP_TIME,'dd-mon-yyyy hh24:mi') from stats$snapshot;
    
       SNAP_ID TO_CHAR(SNAP_TIME
    ---------- -----------------
             1 07-mar-2007 11:35
             2 07-mar-2007 11:36
             3 07-mar-2007 11:36
    
    3 rows selected.
    
    SQL> delete from stats$snapshot where SNAP_TIME < sysdate - (1/(24*60)) ;
    
    3 rows deleted.
    Extend this to 12 months and test it. Good luck.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    use sppurge

  5. #5
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Thanks for all the response.

    1) I have suggested the Automatic Workload Repository for few 10g databases we have but upper management would like consistentcy accross the bord.(STATSPACK all the way) so this is not an option.

    2) sppurge is manual as you need to supply losnapid and hisnapid. They something that you run once and will call the dbms_job to purge on its on every day provided that the number of day or snapshots to keep is repected( ie we only need 12 months worth of snapshot..anything old beyond 12 months that will be automatically purged.
    Thanks
    Last edited by Ablakios; 03-07-2007 at 09:49 AM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    configure it then to accept inputs

  7. #7
    Join Date
    Jun 2005
    Location
    Florida
    Posts
    41

    Thumbs up

    Dude , check this out:
    http://www.idevelopment.info/data/Or...tom_pkg_9i.sql

    Please test it before you use it.
    Milind
    -----------------------------
    www.milinds.com
    My Private World on the Web !

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