Periodic Job to Purge Perfstat Stats Pack Metrics

Periodic Job to Purge Perfstat Stats Pack Metrics

You can use the following procedure to setup a database job to periodically purge the statspack metrics within

the perfstat schema ever "x" number of days , this can be used as an alternative to sppurge to manage space within

the perfstat schema

As the Perfstat User run create the following procedure

CREATE OR REPLACE PROCEDURE PERFSTAT.USP_SNAPSPURGE (vDAYS NUMBER)

AS

vMIN_SNAP_ID NUMBER;

vMAX_SNAP_ID NUMBER;

vDBID NUMBER;

vDBNAME VARCHAR2(50);

vINST_NUM NUMBER;

vINST_NAME VARCHAR2(50);

vSTIME DATE;

vBTIME VARCHAR2(25);

vETIME VARCHAR2(25);

SQL_STMT VARCHAR2(200);

ERROR_NUM NUMBER;

ERROR_MSG VARCHAR2(500);

BEGIN

select min(snap_id),max(snap_id) into vMIN_SNAP_ID,vMAX_SNAP_ID

from STATS$SNAPSHOT

where snap_time <=(sysdate-vDAYS);

select dbid,name into vDBID,vDBNAME

from v$database;

select instance_number,instance_name into vINST_NUM,vINST_NAME

from v$instance;

select to_char(snap_time, 'YYYYMMDD HH24:MI:SS') into vBTIME

from stats$snapshot b

where b.snap_id = vMIN_SNAP_ID

and b.dbid = vDBID

and b.instance_number = vINST_NUM;

select to_char(snap_time, 'YYYYMMDD HH24:MI:SS') into vETIME

from stats$snapshot e

where e.snap_id = vMAX_SNAP_ID

and e.dbid = vDBID

and e.instance_number = vINST_NUM;

-- **** check variables **** ---

delete from stats$snapshot

where instance_number = vINST_NUM

and dbid = vDBID

and snap_id between vMIN_SNAP_ID and vMAX_SNAP_ID;

SQL_STMT:='alter session set hash_area_size=1048576';

execute immediate (SQL_STMT);

delete --+ index_ffs(st)

from stats$sqltext st

where (hash_value, text_subset) not in

(select --+ hash_aj full(ss) no_expand

hash_value, text_subset

from stats$sql_summary ss

where ( ( snap_id < vMIN_SNAP_ID

or snap_id > vMAX_SNAP_ID

)

and dbid = vDBID

and instance_number = vINST_NUM

)

or ( dbid != vDBID

or instance_number != vINST_NUM)

);

delete --+ index_ffs(sso)

from stats$seg_stat_obj sso

where (dbid, dataobj#, obj#) not in

(select --+ hash_aj full(ss) no_expand

dbid, dataobj#, obj#

from stats$seg_stat ss

where ( ( snap_id < vMIN_SNAP_ID

or snap_id > vMAX_SNAP_ID

)

and dbid = vDBID

and instance_number = vINST_NUM

)

or ( dbid != vDBID

or instance_number != vINST_NUM)

);

delete from stats$undostat us

where dbid = vDBID

and instance_number = vINST_NUM

and begin_time >= to_date(vBTIME, 'YYYYMMDD HH24:MI:SS')

and end_time <= to_date(vETIME, 'YYYYMMDD HH24:MI:SS');

-- delete from stats$undostat us

-- where dbid = vDBID

-- and instance_number = vINST_NUM

-- and begin_time < to_date(vBTIME, 'YYYYMMDD HH24:MI:SS')

-- and end_time > to_date(vETIME, 'YYYYMMDD HH24:MI:SS');

/* Delete any dangling database instance rows for that startup time */

delete from stats$database_instance di

where instance_number = vINST_NUM

and dbid = vDBID

and not exists (select 1

from stats$snapshot s

where s.dbid = di.dbid

and s.instance_number = di.instance_number

and s.startup_time = di.startup_time);

/* Delete any dangling statspack parameter rows for the database instance */

delete from stats$statspack_parameter sp

where instance_number = vINST_NUM

and dbid = vDBID

and not exists (select 1

from stats$snapshot s

where s.dbid = sp.dbid

and s.instance_number = sp.instance_number);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

ERROR_NUM := SQLCODE;

ERROR_MSG := substr(SQLERRM,1,100);

DBMS_OUTPUT.PUT_LINE(ERROR_NUM || ': ' || ERROR_MSG);

END;

/

You can call the above procedure as follows , for example to keep only the only last 30 days of metrics within the

perfstat schema:

SQL> exec PERFSTAT.USP_SNAPSPURGE(30);

As the Perstat User Setup a database job to periodically purge the stats pack schema , for example to purge the statspack

metrics every 31 days , keeping only 30 days worth of metrics:

DECLARE jobno number; BEGIN DBMS_JOB.SUBMIT(job => jobno, what =>

'PERFSTAT.USP_SNAPSPURGE (30);', next_date =>

to_date('14-May-2008 03:00:00 PM','dd-Mon-yyyy HH:MI:SS AM'),

interval => '/*31:Days*/ sysdate + 31'); END;

/