Statspack-delete historical data (repost)

Delete the corresponding data in the stats $ snapshot data table. The data in other tables will be deleted horizontally:

 

SQL> select max (snap_id) from stats $ snapshot;   MAX (SNAP_ID) ------------ 166   SQL> delete from stats $ snapshot where snap_id <= 166;   143 rows deleted

 

 

You can change the snap_id range to retain the data you need.

During the above deletion process, you can see that all related tables are locked.

 

SQL> select a. object_id, a. oracle_username, B. object_name From v $ locked_object A, dba_objects B Where a. object_id = B. object_id /   Object_id oracle_usernameobject_name ---------------------------------------------------------------------------------------------- 156 perfstatsnap $ 39700 perfstatstats $ librarycache 39706 perfstatstats $ rollstat 39712 perfstatstats $ SGA 39754 perfstatstats $ Parameter 39745 perfstatstats $ SQL _statistics 39739 perfstatstats $ SQL _summary 39736 perfstatstats $ enqueuestat 39733 perfstatstats $ waitstat 39730 perfstatstats $ bg_event_summary 39724 perfstatstats $ system_event 39718 perfstatstats $ sysstat 39715 perfstatstats $ sgastat 39709 perfstatstats $ rowcache_summary 39703 PERFSTATSTATS $ BUFFER_POOL_STATISTICS 39697 PERFSTATSTATS $ LATCH_MISSES_SUMMARY 39679 PERFSTATSTATS $ SNAPSHOT 39682 PERFSTATSTATS $ FILESTATXS 39688 PERFSTATSTATS $ LATCH 174 PERFSTATJOB $   20 rows selected

 

Oracle also provides a system script used to Truncate the statistical information tables. The Script Name Is sptrunc. SQL (8i and 9i are the same)

The main content of this script is as follows, which shows all the system tables related to statspack:

 

Truncate table STATS $ FILESTATXS; 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 $ enqueuestat; Truncate table stats $ SQL _summary; Truncate table stats $ SQL _statistics; Truncate table stats $ sqltext; Truncate table stats $ parameter;   Delete from stats $ snapshot; Delete from stats $ database_instance;   Commit;

 

If a large amount of data is sampled, the direct Delete operation is very slow. You can use the preceding SQL statement to intercept all tables.

 

From: http://www.eygle.com/statspack/statspack08.htm