STATSPACK数据清除（一）

最新推荐文章于 2024-10-17 18:58:27 发布

ckawt40802

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量116

点赞数

文章标签：数据库

Oracle文档推荐的STATSPACK过期数据产生的方法是直接删除STATS$SNAPSHOT表中的记录。

观察PERFSTAT用户下的表：

SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME
------------------------------
STATS$BG_EVENT_SUMMARY
STATS$BUFFER_POOL_STATISTICS
STATS$DATABASE_INSTANCE
STATS$DB_CACHE_ADVICE
STATS$DLM_MISC
STATS$ENQUEUE_STAT
.
.
.
STATS$UNDOSTAT
STATS$WAITSTAT

已选择41行。

用于进行STATSPACK统计表多达41个，那么仅仅删除STATS$SNAPSHOT表，能否达到清除数据的目的。

检查一个已经部署STATSPACK的产品数据库：

SQL> SELECT JOB, WHAT FROM USER_JOBS;

       JOB WHAT
---------- ----------------------------------------------------------------------
       141 statspack.snap;
     44190 begin delete stats$snapshot where snap_time <= trunc(sysdate) - 61;
           commit; end;

可以看到，这个数据库就是通过删除STATS$SNAPSHOT来清除数据的，那么PERFSTAT用户下的表中的记录是否已经被删除：

SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM STATS$SNAPSHOT;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
24312 25799

对于STATS$SNAPSHOT表来说，记录已经清除到了24312，那么其他的表是否也是如此呢：

SQL> SET SERVEROUT ON SIZE 1000000
SQL> DECLARE
2     TYPE T_TAB IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3     V_TAB T_TAB;
4     V_RESULT VARCHAR2(32767);
5 BEGIN
6     SELECT TABLE_NAME BULK COLLECT INTO V_TAB
7     FROM USER_TABLES
8     INTERSECT
9     SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'SNAP_ID' ;
10     FOR I IN 1..V_TAB.COUNT LOOP
11             EXECUTE IMMEDIATE 'SELECT ''' || V_TAB(I)
12                     || ':MIN_ID('' || MIN(SNAP_ID) || ''), MAX_ID('' || MAX(SNAP_ID) || '')'' FROM ' || V_TAB(I)
13                     INTO V_RESULT;
14     DBMS_OUTPUT.PUT_LINE(V_RESULT);
15     END LOOP;
16 END;
17 /
STATS$BG_EVENT_SUMMARY:MIN_ID(24312), MAX_ID(25799)
STATS$BUFFER_POOL_STATISTICS:MIN_ID(24312), MAX_ID(25799)
STATS$DATABASE_INSTANCE:MIN_ID(1), MAX_ID(25202)
STATS$DB_CACHE_ADVICE:MIN_ID(24312), MAX_ID(25799)
STATS$DLM_MISC:MIN_ID(), MAX_ID()
STATS$ENQUEUE_STAT:MIN_ID(24312), MAX_ID(25799)
STATS$FILESTATXS:MIN_ID(24312), MAX_ID(25799)
STATS$INSTANCE_RECOVERY:MIN_ID(24312), MAX_ID(25799)
STATS$LATCH:MIN_ID(24312), MAX_ID(25799)
STATS$LATCH_CHILDREN:MIN_ID(), MAX_ID()
STATS$LATCH_MISSES_SUMMARY:MIN_ID(24312), MAX_ID(25799)
STATS$LATCH_PARENT:MIN_ID(), MAX_ID()
STATS$LIBRARYCACHE:MIN_ID(24312), MAX_ID(25799)
STATS$PARAMETER:MIN_ID(24312), MAX_ID(25799)
STATS$PGASTAT:MIN_ID(24312), MAX_ID(25799)
STATS$PGA_TARGET_ADVICE:MIN_ID(24312), MAX_ID(25799)
STATS$RESOURCE_LIMIT:MIN_ID(24312), MAX_ID(25799)
STATS$ROLLSTAT:MIN_ID(24312), MAX_ID(25799)
STATS$ROWCACHE_SUMMARY:MIN_ID(24312), MAX_ID(25799)
STATS$SEG_STAT:MIN_ID(), MAX_ID()
STATS$SESSION_EVENT:MIN_ID(), MAX_ID()
STATS$SESSTAT:MIN_ID(), MAX_ID()
STATS$SGA:MIN_ID(24312), MAX_ID(25799)
STATS$SGASTAT:MIN_ID(24312), MAX_ID(25799)
STATS$SHARED_POOL_ADVICE:MIN_ID(24312), MAX_ID(25799)
STATS$SNAPSHOT:MIN_ID(24312), MAX_ID(25799)
STATS$SQL_PLAN:MIN_ID(), MAX_ID()
STATS$SQL_PLAN_USAGE:MIN_ID(), MAX_ID()
STATS$SQL_STATISTICS:MIN_ID(24312), MAX_ID(25799)
STATS$SQL_SUMMARY:MIN_ID(24312), MAX_ID(25799)
STATS$SQL_WORKAREA_HISTOGRAM:MIN_ID(24312), MAX_ID(25799)
STATS$SYSSTAT:MIN_ID(24312), MAX_ID(25799)
STATS$SYSTEM_EVENT:MIN_ID(24312), MAX_ID(25799)
STATS$TEMPSTATXS:MIN_ID(24312), MAX_ID(25799)
STATS$UNDOSTAT:MIN_ID(1), MAX_ID(25799)
STATS$WAITSTAT:MIN_ID(24312), MAX_ID(25799)

PL/SQL procedure successfully completed.

可以看到，除了个别记录为空之外，只有STATS$DATABASE_INSTANCE和STATS$UNDOSTAT两张表的记录没有被清除掉，其他表的记录是和STATS$SNAPSHOT表保持一致的。

而观察上STATS$DATABASE_INSTANCE表可以发现，里面记录的应该是数据库的启动记录，这张表的记录并非根据SNAP_ID进行统计，因此显然也无法根据SNAP_ID进行删除。而STATS$UNDOSTAT是根据SNAP_ID进行的统计，而没有这里没有删除，这是ORACLE的一个bug，可以参考：http://yangtingkun.itpub.net/post/468/466098

那么现在就有一个问题了，由于用户只删除了STATS$SNAPSHOT表的记录，那么Oracle是如何对其他表进行清除的呢。

首先想到的就是触发器，但是当前用户下并没有触发器：

SQL> SELECT * FROM USER_TRIGGERS;

no rows selected

难道PERFSTAT用户也像基础数据字典表一样采用CLUSTER存储：

SQL> SELECT * FROM USER_CLUSTERS;

no rows selected

但是当前用户下也没有CLUSTER。

莫非是用户删错了STATS$SNAPSHOT表的SNAP_ID之后，Oracle下次运行STATSPACK.SNAP的时候会自动删除掉其他表中的对应记录，不过检查STATSPACK包中，未发现DELETE语句：

SQL> SELECT COUNT(*) FROM USER_SOURCE
2 WHERE UPPER(TEXT) LIKE '%DELETE%';

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

而且测试发现，在删除STATS$SNAPSHOT表的同时，其他表对应SNAP_ID的记录也不见了：

SQL> DELETE STATS$SNAPSHOT WHERE SNAP_ID = 24312;

1 row deleted.

SQL> DECLARE
2     TYPE T_TAB IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3     V_TAB T_TAB;
4     V_RESULT VARCHAR2(32767);
5 BEGIN
6     SELECT TABLE_NAME BULK COLLECT INTO V_TAB
7     FROM USER_TABLES
8     INTERSECT
9     SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'SNAP_ID' ;
10     FOR I IN 1..V_TAB.COUNT LOOP
11             EXECUTE IMMEDIATE 'SELECT ''' || V_TAB(I)
12                     || ':MIN_ID('' || MIN(SNAP_ID) || ''), MAX_ID('' || MAX(SNAP_ID) || '')'' FROM ' || V_TAB(I)
13                     INTO V_RESULT;
14     DBMS_OUTPUT.PUT_LINE(V_RESULT);
15     END LOOP;
16 END;
17 /
STATS$BG_EVENT_SUMMARY:MIN_ID(24313), MAX_ID(25800)
STATS$BUFFER_POOL_STATISTICS:MIN_ID(24313), MAX_ID(25800)
STATS$DATABASE_INSTANCE:MIN_ID(1), MAX_ID(25202)
STATS$DB_CACHE_ADVICE:MIN_ID(24313), MAX_ID(25800)
STATS$DLM_MISC:MIN_ID(), MAX_ID()
STATS$ENQUEUE_STAT:MIN_ID(24313), MAX_ID(25800)
STATS$FILESTATXS:MIN_ID(24313), MAX_ID(25800)
STATS$INSTANCE_RECOVERY:MIN_ID(24313), MAX_ID(25800)
STATS$LATCH:MIN_ID(24313), MAX_ID(25800)
STATS$LATCH_CHILDREN:MIN_ID(), MAX_ID()
STATS$LATCH_MISSES_SUMMARY:MIN_ID(24313), MAX_ID(25800)
STATS$LATCH_PARENT:MIN_ID(), MAX_ID()
STATS$LIBRARYCACHE:MIN_ID(24313), MAX_ID(25800)
STATS$PARAMETER:MIN_ID(24313), MAX_ID(25800)
STATS$PGASTAT:MIN_ID(24313), MAX_ID(25800)
STATS$PGA_TARGET_ADVICE:MIN_ID(24313), MAX_ID(25800)
STATS$RESOURCE_LIMIT:MIN_ID(24313), MAX_ID(25800)
STATS$ROLLSTAT:MIN_ID(24313), MAX_ID(25800)
STATS$ROWCACHE_SUMMARY:MIN_ID(24313), MAX_ID(25800)
STATS$SEG_STAT:MIN_ID(), MAX_ID()
STATS$SESSION_EVENT:MIN_ID(), MAX_ID()
STATS$SESSTAT:MIN_ID(), MAX_ID()
STATS$SGA:MIN_ID(24313), MAX_ID(25800)
STATS$SGASTAT:MIN_ID(24313), MAX_ID(25800)
STATS$SHARED_POOL_ADVICE:MIN_ID(24313), MAX_ID(25800)
STATS$SNAPSHOT:MIN_ID(24313), MAX_ID(25800)
STATS$SQL_PLAN:MIN_ID(), MAX_ID()
STATS$SQL_PLAN_USAGE:MIN_ID(), MAX_ID()
STATS$SQL_STATISTICS:MIN_ID(24313), MAX_ID(25800)
STATS$SQL_SUMMARY:MIN_ID(24313), MAX_ID(25800)
STATS$SQL_WORKAREA_HISTOGRAM:MIN_ID(24313), MAX_ID(25800)
STATS$SYSSTAT:MIN_ID(24313), MAX_ID(25800)
STATS$SYSTEM_EVENT:MIN_ID(24313), MAX_ID(25800)
STATS$TEMPSTATXS:MIN_ID(24313), MAX_ID(25800)
STATS$UNDOSTAT:MIN_ID(1), MAX_ID(25800)
STATS$WAITSTAT:MIN_ID(24313), MAX_ID(25800)

PL/SQL procedure successfully completed.

而且，当操作回滚的时候，这些表的记录又恢复了：

SQL> ROLLBACK;

Rollback complete.

SQL> DECLARE
2     TYPE T_TAB IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3     V_TAB T_TAB;
4     V_RESULT VARCHAR2(32767);
5 BEGIN
6     SELECT TABLE_NAME BULK COLLECT INTO V_TAB
7     FROM USER_TABLES
8     INTERSECT
9     SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'SNAP_ID' ;
10     FOR I IN 1..V_TAB.COUNT LOOP
11             EXECUTE IMMEDIATE 'SELECT ''' || V_TAB(I)
12                     || ':MIN_ID('' || MIN(SNAP_ID) || ''), MAX_ID('' || MAX(SNAP_ID) || '')'' FROM ' || V_TAB(I)
13                     INTO V_RESULT;
14     DBMS_OUTPUT.PUT_LINE(V_RESULT);
15     END LOOP;
16 END;
17 /
STATS$BG_EVENT_SUMMARY:MIN_ID(24312), MAX_ID(25800)
STATS$BUFFER_POOL_STATISTICS:MIN_ID(24312), MAX_ID(25800)
STATS$DATABASE_INSTANCE:MIN_ID(1), MAX_ID(25202)
STATS$DB_CACHE_ADVICE:MIN_ID(24312), MAX_ID(25800)
STATS$DLM_MISC:MIN_ID(), MAX_ID()
STATS$ENQUEUE_STAT:MIN_ID(24312), MAX_ID(25800)
STATS$FILESTATXS:MIN_ID(24312), MAX_ID(25800)
STATS$INSTANCE_RECOVERY:MIN_ID(24312), MAX_ID(25800)
STATS$LATCH:MIN_ID(24312), MAX_ID(25800)
STATS$LATCH_CHILDREN:MIN_ID(), MAX_ID()
STATS$LATCH_MISSES_SUMMARY:MIN_ID(24312), MAX_ID(25800)
STATS$LATCH_PARENT:MIN_ID(), MAX_ID()
STATS$LIBRARYCACHE:MIN_ID(24312), MAX_ID(25800)
STATS$PARAMETER:MIN_ID(24312), MAX_ID(25800)
STATS$PGASTAT:MIN_ID(24312), MAX_ID(25800)
STATS$PGA_TARGET_ADVICE:MIN_ID(24312), MAX_ID(25800)
STATS$RESOURCE_LIMIT:MIN_ID(24312), MAX_ID(25800)
STATS$ROLLSTAT:MIN_ID(24312), MAX_ID(25800)
STATS$ROWCACHE_SUMMARY:MIN_ID(24312), MAX_ID(25800)
STATS$SEG_STAT:MIN_ID(), MAX_ID()
STATS$SESSION_EVENT:MIN_ID(), MAX_ID()
STATS$SESSTAT:MIN_ID(), MAX_ID()
STATS$SGA:MIN_ID(24312), MAX_ID(25800)
STATS$SGASTAT:MIN_ID(24312), MAX_ID(25800)
STATS$SHARED_POOL_ADVICE:MIN_ID(24312), MAX_ID(25800)
STATS$SNAPSHOT:MIN_ID(24312), MAX_ID(25800)
STATS$SQL_PLAN:MIN_ID(), MAX_ID()
STATS$SQL_PLAN_USAGE:MIN_ID(), MAX_ID()
STATS$SQL_STATISTICS:MIN_ID(24312), MAX_ID(25800)
STATS$SQL_SUMMARY:MIN_ID(24312), MAX_ID(25800)
STATS$SQL_WORKAREA_HISTOGRAM:MIN_ID(24312), MAX_ID(25800)
STATS$SYSSTAT:MIN_ID(24312), MAX_ID(25800)
STATS$SYSTEM_EVENT:MIN_ID(24312), MAX_ID(25800)
STATS$TEMPSTATXS:MIN_ID(24312), MAX_ID(25800)
STATS$UNDOSTAT:MIN_ID(1), MAX_ID(25800)
STATS$WAITSTAT:MIN_ID(24312), MAX_ID(25800)

PL/SQL procedure successfully completed.

这种DML语句的特性和触发器特性很相似，可是数据库中明明没有触发器，难道是内部触发器造成的：

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

SQL> DELETE STATS$SNAPSHOT WHERE SNAP_ID = 24312;

1 row deleted.

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

Session altered.

检查对应的TRACE文件：

/opt/oracle/admin/data01/udump/data01_ora_3265.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0
System name: SunOS
Node name: bjdb03
Release: 5.8
Version: Generic_108528-22
Machine: sun4u
Instance name: data01
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 3265, image: oracle@bjdb03 (TNS V1-V3)

*** 2008-07-07 18:16:47.055
*** SESSION ID:(70.9231) 2008-07-07 18:16:47.023
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=34 dep=0 uid=87 ct=42 lid=87 tim=5678516443929 hv=4177740527 ad='e4193760'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #1:c=0,e=10102,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5678516411866
*** 2008-07-07 18:16:58.285
=====================
PARSING IN CURSOR #1 len=43 dep=0 uid=87 ct=7 lid=87 tim=5678527411060 hv=2481130925 ad='ea68a600'
DELETE STATS$SNAPSHOT WHERE SNAP_ID = 24312
END OF STMT
PARSE #1:c=0,e=92291,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5678527411039
=====================
PARSING IN CURSOR #2 len=103 dep=1 uid=0 ct=7 lid=0 tim=5678527418652 hv=1216362171 ad='fdc5ff10'
delete from "PERFSTAT"."STATS$PGASTAT" where "SNAP_ID" = :1 and "DBID" = :2 and "INSTANCE_NUMBER" = :3
END OF STMT
PARSE #2:c=0,e=2896,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=5678527418641
EXEC #2:c=0,e=1848,p=0,cr=2,cu=39,mis=0,r=13,dep=1,og=4,tim=5678527420771
=====================
PARSING IN CURSOR #3 len=105 dep=1 uid=0 ct=7 lid=0 tim=5678527421650 hv=1380708534 ad='fd561fc8'
delete from "PERFSTAT"."STATS$PARAMETER" where "SNAP_ID" = :1 and "DBID" = :2 and "INSTANCE_NUMBER" = :3
END OF STMT
PARSE #3:c=0,e=758,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=5678527421642
EXEC #3:c=20000,e=36565,p=0,cr=5,cu=1060,mis=0,r=259,dep=1,og=4,tim=5678527458447
=====================
PARSING IN CURSOR #4 len=104 dep=1 uid=0 ct=7 lid=0 tim=5678527459408 hv=3658160377 ad='fd5b0608'
delete from "PERFSTAT"."STATS$ROLLSTAT" where "SNAP_ID" = :1 and "DBID" = :2 and "INSTANCE_NUMBER" = :3
END OF STMT
PARSE #4:c=0,e=803,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=5678527459399
EXEC #4:c=10000,e=1671,p=0,cr=2,cu=41,mis=0,r=13,dep=1,og=4,tim=5678527461347
=====================
.
.
.
=====================
PARSING IN CURSOR #1 len=35 dep=0 uid=87 ct=42 lid=87 tim=5678535939396 hv=855351039 ad='f6c9b728'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #1:c=0,e=710,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5678535939383
EXEC #1:c=0,e=155,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5678535939717