cleanup PERFSTAT tables

How to cleanup PERFSTAT tables

PERFSTAT tables grow in size over time

OWNER           TABLESPACE_NAME  SEGMENT_NAME             USED_MB
--------------- ---------------- ------------------------ -------PERFSTAT        WORKAREA         STATS$SQL_SUMMARY           1664
PERFSTAT        WORKAREA         STATS$SYSSTAT_PK             888
PERFSTAT        WORKAREA         STATS$SQL_SUMMARY_PK         664
PERFSTAT        WORKAREA         STATS$SYSSTAT                608
PERFSTAT        WORKAREA         STATS$EVENT_HISTOGRAM_PK     416
PERFSTAT        WORKAREA         STATS$LATCH                  360
PERFSTAT        WORKAREA         STATS$LATCH_PK               328

PERFSTAT        WORKAREA         STATS$EVENT_HISTOGRAM        272
PERFSTAT        WORKAREA         STATS$PARAMETER_PK           184
PERFSTAT        WORKAREA         STATS$SQLTEXT                176

sqlplus perfstat/perfstat@igt

To make the perfstat job to run every hour:

$ORACLE_HOME/rdbms/admin/spauto.sql

To query current status:

SELECT MIN(SNAP_TIME) FROM stats$snapshot;

MIN(SNAP_TIME)

------------------

11-JAN-24

SELECT SYSDATE FROM DUAL;

SYSDATE

------------------

11-AUG-24

To manually truncate perfstat tables

$ORACLE_HOME/rdbms/admin/sptrunc.sql

DESCRIPTION:    Truncates data in Statspack tables

SQL> @$ORACLE_HOME/rdbms/admin/sptrunc.sql

Warning

~~~~~~~

Running sptrunc.sql removes ALL data from Statspack tables.  You may

wish to export the data before continuing.

About to Truncate Statspack Tables

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If would like to exit WITHOUT truncating the tables, enter any text at the

begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin

the truncate operation, press <return>

Enter value for begin_or_exit:

Table truncated.

Table truncated.

5108 rows deleted.

1 row deleted.

Commit complete.

Package altered.

... Truncate operation complete

SQL>

Per Oracle Technote How To Automate Purging of Statspack Snapshots (Doc ID 464214.1)

CREATE OR REPLACE PACKAGE SPPURPKG

IS

  PROCEDURE purge(in_days_older_than IN INTEGER);

END SPPURPKG;

/

CREATE OR REPLACE PACKAGE BODY SPPURPKG

IS

  PROCEDURE purge(in_days_older_than IN INTEGER)IS

    CURSOR get_snaps(in_days IN INTEGER) IS

      SELECT s.rowid,

s.snap_id,

s.dbid,

s.instance_number

    FROM stats$snapshot s,

sys.v_$database d,

sys.v_$instance i

       WHERE s.dbid = d.dbid

         AND s.instance_number = i.instance_number

         AND s.snap_time < TRUNC(SYSDATE) - in_days;

 

errcontext VARCHAR2(100);

errmsg VARCHAR2(1000);

save_module VARCHAR2(48);

save_action VARCHAR2(32);

  BEGIN

    errcontext := 'save settings of DBMS_APPLICATION_INFO';

dbms_application_info.read_module(save_module, save_action);

dbms_application_info.set_module('SPPURPKG.PURGE', 'begin');

errcontext := 'open/fetch get_snaps';

dbms_application_info.set_action(errcontext);

FOR x IN get_snaps(in_days_older_than) LOOP

    errcontext := 'delete (cascade) STATS$SNAPSHOT';

  dbms_application_info.set_action(errcontext);

  DELETE FROM stats$snapshot

       WHERE ROWID = x.rowid;

      errcontext := 'delete "dangling" STATS$SQLTEXT rows';

      dbms_application_info.set_action(errcontext);

  

      DELETE FROM stats$sqltext

       WHERE (old_hash_value, text_subset) not in

               (SELECT /*+ hash_aj (ss) */ old_hash_value, text_subset

                  FROM stats$sql_summary ss

               );

      errcontext := 'delete "dangling" STATS$DATABASE_INSTANCE rows';

      dbms_application_info.set_action(errcontext);

  DELETE FROM stats$database_instance i

       WHERE i.instance_number = x.instance_number

         AND i.dbid = x.dbid

         AND NOT EXISTS

                    (SELECT 1

                       FROM stats$snapshot s

                      WHERE s.dbid = i.dbid

                        AND s.instance_number = i.instance_number

                        AND s.startup_time = i.startup_time

                    );

      errcontext := 'delete "dangling" STATS$STATSPACK_PARAMETER rows';

      dbms_application_info.set_action(errcontext);

  

      DELETE FROM stats$statspack_parameter p

       WHERE p.instance_number = x.instance_number

         AND p.dbid = x.dbid

         AND NOT EXISTS

                    (SELECT 1

                       FROM stats$snapshot s

                      WHERE s.dbid = p.dbid

                        AND s.instance_number = p.instance_number

                    );

      errcontext := 'fetch/close get_snaps';

      dbms_application_info.set_action(errcontext);

    END LOOP;

commit;

    errcontext := 'restore saved settings of DBMS_APPLICATION_INFO';

    dbms_application_info.set_module(save_module, save_action);

  EXCEPTION

    WHEN OTHERS THEN

  rollback;

      errmsg := sqlerrm;

      dbms_application_info.set_module(save_module, save_action);

      raise_application_error(-20000, errcontext || ': ' || errmsg);

  END purge;

END SPPURPKG;

/

--Create a job to delete data older than 10 days

BEGIN

 DBMS_JOB.submit(v_job_number, 

    WHAT => 'SPPURPKG.purge(10);',

    NEXT_DATE => TRUNC(SYSDATE) +1 + 0/24 + 15/1440,

    INTERVAL  => 'TRUNC(SYSDATE) +1 + 0/24 + 15/1440'); 

 COMMIT;

END;

/

--Execute the job

BEGIN

  SPPURPKG.purge(10);

  commit;

END;

/

--See jobs under perfstat user

COL WHAT FOR A30

COL INTERVAL FOR A40

SELECT what, interval FROM USER_JOBS;

 WHAT                           INTERVAL

------------------------------ ----------------------------------

SPPURPKG.purge(10);            TRUNC(SYSDATE) +1 + 0/24 + 15/1440

statspack.snap;                trunc(SYSDATE+1/24,'HH')