How to Purge Statspack Snapshots in Oracle

I will explain How to Purge Statspack Snapshots in Oracle in this post.

 

Purge Statspack Snapshots in Oracle

 

If you want to learn more details about Statspack in Oracle, you should read the following post.

How to Use Oracle AWR in Standard Edition Using Statspack

 

 

 

 

 

 

There is a purge script called sppurge under the “$ORACLE_HOME/rdbms/admin” directory to be executed manually from time to time.

 

-- SQL> @?/rdbms/admin/sppurge;
-- 
-- 
-- Warning
-- ~~~~~~~
-- sppurge.sql deletes all snapshots ranging between the lower and
-- upper bound Snapshot Ids specified, f0r the database instance
-- you are connected to. Snapshots identified as Baseline snapshots
-- whIch lie within the snapshot range will not be purged.
-- 
-- It is NOT possible to rollback changes once the purge begins.
-- 
-- You may wish to exp0rt this data before continuing.
-- 
-- 
-- Specify the Lo Snap Id and Hi Snap Id range to purge
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Enter value f0r losnapid: 1453 <---------
-- Using 1 f0r lower bound.
-- 
-- Enter value f0r hisnapid: 2021 <---------
-- Using 200 f0r upper bound.
-- 
-- Deleting snapshots 1 - 200.
-- 
-- Number of Snapshots purged: 200
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- 
-- Purge of specified Snapshot range complete.

 

 

 

 

STATSPACK.PURGE Procedure in Oracle

To purge the Statspack historical data, you should use the STATSPACK.PURGE procedure. This procedure’s parameters accepts the number of days and removes all snapshots that are older that value.

 

SQL> exec statspack.purge(number_of_days);

 

For Example, to purge all snapshots older than 10 days

SQL> exec statspack.purge(10);

 

 

  • The statspack.purge procedure is no longer deleting old snapshots
  • Even specifying number of days, specifying a specific day, and specifying snap_ids, does not purge the snapshots:
    SQL> select min(snap_id) from perfstat.stats$snapshot;
    
    MIN(SNAP_ID)
    ------------
          11499
    
    SQL> variable num_snaps number;
    SQL> begin
     2  :num_snaps := statspack.purge
    ( i_begin_snap=>11499, i_end_snap=>11500
    , i_extended_purge=>TRUE);
    end;   3    4    5  
     6  /
    begin
    *
    ERROR at line 1:
    ORA-20200: Snapshot 11499 does not exist for DB Id/Instance 3843533403/1
    ORA-06512: at "PERFSTAT.STATSPACK", line 731
    ORA-06512: at "PERFSTAT.STATSPACK", line 1493
    ORA-06512: at line 2
    
    
    SQL> select snap_id from perfstat.stats$snapshot where snap_id between 11499 and 11503;
    
      SNAP_ID
    ----------
        11499
        11500
        11501
        11502
        11503

 

The Test database was cloned from Prod, and the Prod database was migrated to a new server.

From Test:
SQL> select dbid, min(snap_time), max(snap_time) from perfstat.stats$snapshot group by dbid ORDER BY 2;

  DBID MIN(SNAP_TIME) MAX(SNAP_TIME)
---------- -------------- --------------
1066742040 01/28/17 19:00 05/31/17 12:50
3837490976 05/31/17 13:00 08/08/17 10:26
3843533403 08/08/17 11:24 09/04/17 20:00

From Prod:

SQL> select dbid, min(snap_time), max(snap_time) from perfstat.stats$snapshot group by dbid;

  DBID MIN(SNAP_TIME) MAX(SNAP_TIME)
---------- -------------- --------------
1066742040 05/18/17 19:00 09/16/17 17:12
1130860148 09/16/17 18:00 11/01/17 10:00

Other database DBID was present and purge script executed which is default using the current database id. However, statspack was existing for other database id and these script was not deleting the other database ID.

 

 

Add below parameters to specify DBId/inst number:

exec perfstat.STATSPACK.PURGE(268,true);
OR
You can delete rows of stats$snapshot table to purge the old data as follows.

SQL > delete from stats$snapshot where snap_id < 4000;
1362138 rows deleted.

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *