How to delete historical data generated by statspack

How to delete historical data generated by statspack

When $ Oracle_HOME/rdbms/admin/spauto is used. after SQL creates a default job, snapshots are collected every hour in the database, so that you can use $ ORACLE_HOME/rdbms/admin/spreport at any time. SQL generates a report diagnosis analysis report, but the amount of sampled data is quite astonishing. If this job is ignored or the remaining space is not regularly checked, some production environments may cause other unpredictable conditions due to insufficient space. Therefore, we need to regularly Delete the historical data collected by statspack, provided that the backup is required, delete historical data that is no longer needed. There are two ways to delete it: manual deletion and automatic script deletion.

1. Back up objects under the perfstat user
[Oracle @ rac1 ~] $ Export NLS_LANG = american_america.ZHS16GBK
[Oracle @ rac1 ~] $ Cd/data
[Oracle @ rac1 ~] $ Exp perfstat/oracle @ orcl file =./perfstat_backup.dmp wner = perfstat
[Oracle @ rac1 ~] $ Ll-t
-Rw-r -- 1 oracle dba 1893620736 Apr 27 perfstat_backup.dmp
2. Delete historical data generated by statspack
A: manually delete the history records in statspack.
1) retain data for the last month
Delete from perfstat. stats $ snapshot where snap_time <= add_months (trunc (sysdate),-1)
-- 2520 records
-- 1313.869 s
Note: It takes 25 minutes to delete 2520 records because delete from stats $ snapshot will delete the records related to snap_id in all the tables on behalf of snap_id, therefore, it takes a long time (except stats $ undostat and stats $ sqltext:
To delete statspack data, you can use the delete stats $ SNAPSHOT method. Except STATS $ UNDOSTAT, all other tables containing SNAP_ID will be cleared. However, PERFSTAT has some tables that do not contain SNAP_ID:
Select B. segment_name, sum (B. bytes)/1024/1024
From user_segments B
Where B. segment_name in (SELECT TABLE_NAME FROM USER_TABLES
MINUS
SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'snap _ id ')
Group by B. segment_name
Order by 2
Table_name M
------------------------------------------------------
STATS $ IDLE_EVENT 0.125
STATS $ LEVEL_DESCRIPTION 0.125
STATS $ maid 0.125
STATS $ SEG_STAT_OBJ 1
STATS $ SQLTEXT 176

2) Clear the non-join deletion table (stats $ undostat, stats $ sqltext)
Truncate table perfstat. stats $ undostat
-- 19440 records
Truncate table perfstat. stats $ sqltext
-- 8060778 records
Note: Due to the large number of records in PERFSTAT. stats $ sqltext, truncate is generally selected. Of course, you can delete the data a month ago, but the speed will be quite slow.
Delete from stats $ undostat where begin_time <= add_months (trunc (sysdate),-1)
Delete from stats $ sqltext bb where hash_value in (select. hash_value from stats $ sqltext a, stats $ SQL _summary B where. hash_value = B. hash_value (+) and B. hash_value is null)

B: oracle provides an automatic script to delete historical records in statspack.
In addition to manual deletion of historical records, oracle also provides statistical information tables such as system script user truncate,
$ ORACLE_HOME // rdbms/admin/sptrunc. SQL
Truncate table STATS $ FILESTATXS;
Truncate table STATS $ TEMPSTATXS;
Truncate table STATS $ LATCH;
Truncate table STATS $ LATCH_CHILDREN;
Truncate table STATS $ LATCH_MISSES_SUMMARY;
Truncate table STATS $ LATCH_PARENT;
Truncate table STATS $ LIBRARYCACHE;
Truncate table STATS $ BUFFER_POOL_STATISTICS;
Truncate table STATS $ ROLLSTAT;
Truncate table STATS $ ROWCACHE_SUMMARY;
Truncate table STATS $ SGA;
Truncate table STATS $ SGASTAT;
Truncate table STATS $ SYSSTAT;
Truncate table STATS $ SESSTAT;
Truncate table STATS $ SYSTEM_EVENT;
Truncate table STATS $ SESSION_EVENT;
Truncate table STATS $ BG_EVENT_SUMMARY;
Truncate table STATS $ WAITSTAT;
Truncate table STATS $ ENQUEUE_STATISTICS;
Truncate table STATS $ SQL _SUMMARY;
Truncate table STATS $ SQL _STATISTICS;
Truncate table STATS $ SQLTEXT;
Truncate table STATS $ PARAMETER;
Truncate table STATS $ RESOURCE_LIMIT;
Truncate table STATS $ DLM_MISC;
Truncate table STATS $ UNDOSTAT;
Truncate table STATS $ SQL _PLAN;
Truncate table STATS $ SQL _PLAN_USAGE;
Truncate table STATS $ SEG_STAT;
Truncate table STATS $ SEG_STAT_OBJ;
Truncate table STATS $ DB_CACHE_ADVICE;
Truncate table STATS $ PGASTAT;
Truncate table STATS $ INSTANCE_RECOVERY;
Truncate table STATS $ JAVA_POOL_ADVICE;
Truncate table STATS $ THREAD;
Truncate table STATS $ CR_BLOCK_SERVER;
Truncate table STATS $ CURRENT_BLOCK_SERVER;
Truncate table STATS $ INSTANCE_CACHE_TRANSFER;
Truncate table STATS $ FILE_HISTOGRAM;
Truncate table STATS $ TEMP_HISTOGRAM;
Truncate table STATS $ EVENT_HISTOGRAM;
Truncate table STATS $ TIME_MODEL_STATNAME;
Truncate table STATS $ SYS_TIME_MODEL;
Truncate table STATS $ SESS_TIME_MODEL;
Truncate table STATS $ STREAMS_CAPTURE;
Truncate table STATS $ STREAMS_APPLY_SUM;
Truncate table STATS $ PROPAGATION_SENDER;
Truncate table STATS $ PROPAGATION_RECEIVER;
Truncate table STATS $ BUFFERED_QUEUES;
Truncate table STATS $ BUFFERED_SUBSCRIBERS;
Truncate table STATS $ RULE_SET;
Truncate table STATS $ OSSTAT;
Truncate table STATS $ OSSTATNAME;
Truncate table STATS $ PROCESS_ROLLUP;
Truncate table STATS $ PROCESS_MEMORY_ROLLUP;
Truncate table STATS $ STREAMS_POOL_ADVICE;
Truncate table STATS $ SGA_TARGET_ADVICE;
Truncate table STATS $ MUTEX_SLEEP;
Truncate table STATS $ DYNAMIC_REMASTER_STATS;

Delete from STATS $ SNAPSHOT;
Delete from STATS $ DATABASE_INSTANCE;

Commit;
In this case, most system tables related to statspack are cleared. If a large amount of data is sampled, direct delete is very slow, therefore, you can use the default script provided by oracle to clear historical records in perfstat.