ITPub博客

首页 > 数据库 > Oracle > 【AWR】该怎样清理SYSAUX表空间相关数据

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

原创 Oracle 作者:xysoul_云龙 时间:2014-02-17 19:04:59 0 删除 编辑

在检查数据库时发现表空间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不是很了解,有太多业余的地方,如哪位同志了解,请告知在下,不胜感激。

还在继续分析中……

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

请登录后发表评论 登录
全部评论
主要从事数据库相关工作,其他操作系统、中间件等也有涉及,热衷分享、开源,支持国产,期待中华民族全面的伟大复兴。近日骤然醒悟,欲在IT江湖中闯荡一番,如有幸在诸多侠客中留点踪迹,也算不虚此行。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博:文盲筱烨 微信公众号:筱烨视点

注册时间:2014-02-15

  • 博文量
    168
  • 访问量
    756830