STATSPACK資料清除（二）

Oracle文件推薦的STATSPACK過期資料產生的方法是直接刪除STATS$SNAPSHOT表中的記錄。

STATSPACK資料清除（一）：http://yangtingkun.itpub.net/post/468/466248

 

 

上一篇文章提到了，刪除STATSPACK資料可以使用DELETE STATS$SNAPSHOT的方法，除了STATS$UNDOSTAT之外，其他的包含SNAP_ID的表都會被清除掉。

不過PERFSTAT使用者下還有一些表不包含SNAP_ID：

SQL> SELECT TABLE_NAME FROM USER_TABLES
  2  MINUS
  3  SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'SNAP_ID';

TABLE_NAME
------------------------------
STATS$IDLE_EVENT
STATS$LEVEL_DESCRIPTION
STATS$SEG_STAT_OBJ
STATS$SQLTEXT
STATS$STATSPACK_PARAMETER

其中四張表包含的基本上屬於靜態資料，資料量很小：

SQL> SELECT COUNT(*) FROM STATS$IDLE_EVENT;

  COUNT(*)
----------
        25

SQL> SELECT COUNT(*) FROM STATS$LEVEL_DESCRIPTION;

  COUNT(*)
----------
         5

SQL> SELECT COUNT(*) FROM STATS$SEG_STAT_OBJ;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM STATS$STATSPACK_PARAMETER;

  COUNT(*)
----------
         1

但是STATS$SQLTEXT表中存放的是SQL記錄資訊，資料量很大：

SQL> SELECT COUNT(*) FROM STATS$SQLTEXT;

  COUNT(*)
----------
  28213854

透過前文可以看到，刪除STATS$SNAPSHOT的操作一直在進行，不過這張表的記錄一直不會被清除，而且這張表和索引經過長時間的積累，已經佔用了很大的空間：

SQL> SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 M  
  2  FROM USER_SEGMENTS
  3  GROUP BY SEGMENT_NAME
  4  HAVING SUM(BYTES)/1024/1024 > 100
  5  ORDER BY 2 DESC;

SEGMENT_NAME                            M
------------------------------ ----------
STATS$SQLTEXT                        3712
STATS$SQLTEXT_PK                     2176
STATS$SQL_SUMMARY                     752
STATS$SQL_SUMMARY_PK                  617

這張表和索引已經佔用了將近6G的空間。

檢查Oracle的sppurge.sql，發現對這張表的刪除指令碼是被註釋狀態，Oracle給出的指令碼和說明為：

/*  Delete any dangling SQLtext  */
/*
Rem  The following statement deletes any dangling SQL statements which
Rem  are no longer referred to by ANY snapshots.  This statment has been
Rem  commented out as it can be very resource intensive.

alter session set hash_area_size=1048576;
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     < :lo_snap
                    or snap_id     > :hi_snap
                   )
                   and dbid            = :dbid
                   and instance_number = :inst_num
               )
            or (   dbid            != :dbid
                or instance_number != :inst_num)
        );

Rem Adding an optional STATS$SEG_STAT_OBJ delete statement

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     < :lo_snap
                  or snap_id     > :hi_snap
                 )
                 and dbid            = :dbid
                 and instance_number = :inst_num
               )
            or (   dbid            != :dbid
                or instance_number != :inst_num)
        );
*/

由於這個表的刪除很消耗系統資源，因此Oracle既沒有在刪除STATS$SNAPSHOT時自動刪除，也沒有在sppurge.sql中呼叫。

為了清除STATS$SQLTEXT，可以將sppurge.sql裡面的註釋去掉，然後執行指令碼。

當然如果只是為了清除記錄，可以先備份現有的資料，然後呼叫sptrunc.sql，將所有表中的記錄TRUNCATE掉。