【AWR】该怎样清理SYSAUX表空间相关数据

最新推荐文章于 2021-05-06 17:40:09 发布

crygaj7140

最新推荐文章于 2021-05-06 17:40:09 发布

阅读量158

点赞数

在检查数据库时发现表空间SYSAUX使用率很高，如下所示

SQL> set pagesize 9999

SQL> set linesize 132

SQL> col TABLESPACE_NAME for a25

SQL> select

2 f.tablespace_name,

3 a.total,

4 f.free,

5 round((f.free/a.total)*100) "% Free"

6 from

7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

8 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

9 WHERE a.tablespace_name = f.tablespace_name(+)

10 order by "% Free"

11 /

TABLESPACE_NAME TOTAL FREE % Free

------------------------- ---------- ---------- ----------

SYSAUX 81920 1662 2

…………………………………………………………………….后边省略

查看快照信息，发现快照最小值为31074

Listing the last 8 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

eicdb1 EICDB 31074 18 Dec 2013 01:00 1

31075 18 Dec 2013 02:00 1

31076 18 Dec 2013 03:00 1

31077 18 Dec 2013 04:00 1

通过以下语句查看，发现下面几个表占用空间较大

SQL> select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

------------------------------ ---------------------------- --------------- ---------------

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 22881

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 16125

I_WRI$_OPTSTAT_H_ST INDEX 12002

WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 10008

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2065804525_25627 INDEX PARTITION 3030

WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 2135

WRH$_SYSMETRIC_HISTORY TABLE 1732

I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1685

WRH$_SYSMETRIC_HISTORY_INDEX INDEX 1671

WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2065804525_25594 INDEX PARTITION 1291

10 rows selected.

查看分区表snap_id信息，发现其最小值为25627，也就是说里边有许多过期数据存放）

SQL> select * from (select distinct SNAP_ID,DBID from sys.wrh$_active_session_history partition(WRH$_ACTIVE_2065804525_25627) order by SNAP_ID) where rownum<=10;

SNAP_ID DBID

---------- ----------

25627 2065804525

25628 2065804525

25629 2065804525

25630 2065804525

25631 2065804525

25632 2065804525

25633 2065804525

25634 2065804525

25635 2065804525

25636 2065804525

10 rows selected

查看表wrh$_active_session_history信息

SQL> select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' and segment_name='WRH$_ACTIVE_SESSION_HISTORY' order by 3;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

------------------------------ ------------------------------ ------------------ ---------------

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 22881

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .0625

检查其他数据库中sys.wrh$_active_session_history 表，发现该表每天都会创建一个分区表，末尾并以起始snap_id命名（很奇怪，为啥前一个数据库就一个分区表）。

SQL> select * from (select distinct SNAP_ID,DBID from sys.wrh$_active_session_history order by SNAP_ID) where rownum<=10;

SNAP_ID DBID

---------- ----------

1008 1358035033

1009 1358035033

1010 1358035033

1011 1358035033

1012 1358035033

1013 1358035033

1014 1358035033

1015 1358035033

1016 1358035033

1017 1358035033

10 rows selected.

SQL> set lines 999

SQL> col SEGMENT_NAME for a30

SQL> select OBJECT_NAME,SUBOBJECT_NAME,object_type,CREATED from dba_objects where OBJECT_NAME like 'WRH$_ACTIVE%' order by object_type;

OBJECT_NAME UBOBJECT_NAME OBJECT_TYPE CREATED

---------------------------------------- ------------------- ------------------- ------------

WRH$_ACTIVE_SESSION_HISTORY_PK INDEX 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1119 INDEX PARTITION 17-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1095 INDEX PARTITION 16-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN INDEX PARTITION 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1047 INDEX PARTITION 14-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1191 INDEX PARTITION 19-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1071 INDEX PARTITION 15-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1143 INDEX PARTITION 18-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1167 INDEX PARTITION 19-DEC-13

WRH$_ACTIVE_SESSION_HISTORY TABLE 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY_BL TABLE 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1167 TABLE PARTITION 18-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1143 TABLE PARTITION 17-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1119 TABLE PARTITION 16-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1095 TABLE PARTITION 15-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1047 TABLE PARTITION 13-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1191 TABLE PARTITION 19-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1071 TABLE PARTITION 14-DEC-13

SQL> alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_1358035033_1008 update global indexes;

Table truncated.

SQL>

SQL> col SNAP_INTERVAL for a18

SQL> col RETENTION for a18

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

---------- ------------------ ------------------ ----------

1358035033 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 – Production

通过上述，查看oracle10g数据库信息，发现，有8天的分区表数据，而数据库AWR保留策略为7天，也就是当清除snap_id信息时并没有完全清除表中数据信息。

暂时没想到好的解决办法，就先delete部分数据（该11g生产库运行期间cpu大多在百分之80-90甚至更高，所以只能一点点的先delete部分数据（货架上的东西是没了，但货架还在，暂时也就这样了，再找找其他方法，其中有两个索引数据量也不小，可以分析一下）。语句如下：

delete from sys.wrh$_active_session_history partition(WRH$_ACTIVE_2065804525_25627) p where p.snap_id<=25850;

曾想试试一下命令，删除之前统计信息（这个无法确定运行时长，当时没用）：

exec dbms_stats.purge_stats(systimestamp - 200);

参数说明：

Parameters

Table 141-78 PURGE_STATS Procedure Parameters

Parameter

Description

before_timestamp

Versions of statistics saved before this timestamp are purged. If NULL, it uses the purging policy used by automatic purge. The automatic purge deletes all history older than the older of (current time - statistics history retention) and (time of recent analyze in the system - 1). The statistics history retention value can be changed using ALTER_STATS_HISTORY_RETENTION Procedure.The default is 31 days.

附：

删除快照命令：

exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>777,high_snap_id=>800,dbid=>2530619508)

清除数据库中其他数据库AWR信息可以使用下面的语句：

exec dbms_swrf_internal.unregister_database(2065804525);

遗留问题：

1、 delete分区表中部分数据后并没有完全收回空间，高水位问题（move占用资源，可避开高峰期），有其他什么方法可以解决；

2、 oracle11g库中（其他oracle11g库中也是每天生成一个分区表）就一个分区表保存数据，影响数据管理，暂时未找出原因；

3、对删除快照、AWR过期数据清理流程不太清楚；

4、为什么表中还保留一些过期数据，MMON后台进程怎么收集和清理AWR信息

由于对AWR不是很了解，有太多业余的地方，如哪位同志了解，请告知在下，不胜感激。

还在继续分析中……