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.

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.

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

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

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.