如何刪除statspack產生的曆史資料

如何刪除statspack產生的曆史資料

當用$Oracle_HOME/rdbms/admin/spauto.sql建立一個預設的job以後，資料庫中會每1個小時收集一次快照，方便我們可以隨時用$ORACLE_HOME/rdbms/admin/spreport.sql產生report診斷分析報告，但是採樣的資料量是相當驚人的，如果忽略了這個job，或者沒有週期性檢查剩餘空間大小，有的生產環境很可能會由於空間不足到導致其它的不可預測狀況，所以我們要週期性刪除statspack收集的曆史資料，前提條件是要做好備份，然後刪除不再需要的曆史資料，刪除方法有2種：手工刪除和自動指令碼刪除。

1.備份perfstat使用者下的對象
[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--r--  1 oracle    dba        1893620736 Apr 27 10:40 perfstat_backup.dmp
2.刪除statspack產生的曆史資料
  a：手工刪除statspack中的記錄
        1)保留最近1個月的資料
            delete from perfstat.stats$snapshot where snap_time <= add_months(trunc(sysdate),-1)
            --2520條記錄
            --1313.869s
      備忘：刪除2520條記錄，需要25分鐘，這是因為delete from stats$snapshot會及聯刪除代snap_id的所有表中相關snap_id的記錄，所以需要的時間會很長（但是stats$undostat,stats$sqltext除外:
    刪除STATSPACK資料可以使用DELETE STATS$SNAPSHOT的方法，除了STATS$UNDOSTAT之外，其他的包含SNAP_ID的表都會被清除掉。不過PERFSTAT使用者下還有一些表不包含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$STATSPACK_PARAMETER    0.125
      STATS$SEG_STAT_OBJ                        1
      STATS$SQLTEXT                                    176

        2)清空非關聯刪除表(stats$undostat,stats$sqltext)
            TRUNCATE TABLE PERFSTAT.stats$undostat
            --19440條記錄
          TRUNCATE TABLE PERFSTAT.stats$sqltext
            --8060778條記錄
          備忘：由於PERFSTAT.stats$sqltext中的記錄數量很大，所以一般選擇truncate，當然也可以delete一個月前的資料，但是速度會相當的慢。
      delete from stats$undostat where begin_time <= add_months(trunc(sysdate),-1)
        delete from stats$sqltext bb  where hash_value in (select a.hash_value from stats$sqltext  a,stats$sql_summary b where a.hash_value = b.hash_value(+) and b.hash_value is null )
       
    b：oracle提供自動指令碼刪除statspack中的記錄
      除了手工刪除記錄以外,oracle還提供了系統指令碼使用者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;
此時，與statspack相關的大部分系統資料表都會被清空，如果採樣了大量的資料，直接delete是非常緩慢的，所以可以考慮使用oracle提供的預設指令碼清空perfstat中的記錄。