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>

--See current jobs in perfstat user

SET LINESIZE 120

COL WHAT FOR A30

COL INTERVAL FOR A40

SELECT job, what, interval FROM USER_JOBS;

--Create a job to call Oracle STATSPACK.purge

DECLARE

 v_job_number NUMBER;

BEGIN

 DBMS_JOB.submit(v_job_number, 

    WHAT => 'STATSPACK.purge(i_purge_before_date=>sysdate-14,i_extended_purge=>true);',

    NEXT_DATE => TRUNC(SYSDATE+1)+3/24,

    INTERVAL  => 'TRUNC(SYSDATE+1)+3/24'); 

 COMMIT;

END;

/

--Create a manual purge Perfstat Tables

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

DECLARE

 v_job_number NUMBER;

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

SET LINESIZE 120

COL WHAT FOR A30

COL INTERVAL FOR A40

SELECT job, what, interval FROM USER_JOBS;

 WHAT                           INTERVAL

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

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

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