STATSPACK数据清除（二）

最新推荐文章于 2022-12-30 18:51:17 发布

ckawt40802

最新推荐文章于 2022-12-30 18:51:17 发布

阅读量159

点赞数

文章标签：数据库

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掉。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-401614/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-401614/