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