Statspack Purge Job

Adding a job to take hourly statspack snapshots has always been simple e.g.
variable v_jobno number;
begin
dbms_job.submit(:v_jobno,
'statspack.snap(i_snap_level=>7);',
trunc(sysdate+1/24,'HH'),
'trunc(sysdate+1/24,''HH'')',
TRUE);
commit;
end;
/
Purging snapshots automatically used to be a little more difficult, fortunately with more recent versions of statspack you can now do e.g.

variable v_jobno number;
begin
dbms_job.submit(:v_jobno,
'statspack.purge(i_num_days=>;14,i_extended_purge=>;TRUE);',
trunc(sysdate+1),
'trunc(sysdate+1)',
TRUE);
commit;
end;
/
This creates a dbms_job that runs every day at midnight purging any snapshots older than 14 days.

Advertisements

2 thoughts on “Statspack Purge Job

  1. Hi,
    After running the command and purging several snapshots, space is not released in the PERFSTAT tablespace. Have you encountered this?
    Thanks!

    Like

    1. hi mike

      the purge will only remove the records from the statspack tables, it won’t release the freespace back to the tablespace – to do that you would need to rebuild the tables ( e.g. via an export/import or alter table move )

      the space freed within the tables will get reused by future snaps so it will stop the tables growing, but if you have a lot of space you need to reclaim the simplest solution is probably to drop statspack using the spdrop script and then reinstall

      if you then implement the purge immediately following the install the amount of space used should remain fairly static as space released by the purge get’s recycled by later snaps

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.