ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【AWR】清理SYSAUX表空间数据（第三季非暴力不合作） xysoul

【AWR】清理SYSAUX表空间数据（第三季非暴力不合作） xysoul

Oracle 作者：lsh609 时间：2013-12-27 20:18:05 0 删除 编辑

前边两篇文章我们测试了修改保留快照的时间、收缩索引空间等方法，但是效果不是太明显，视图、索引还是占用着大部分空间，

SQL> col SEGMENT_NAME for a30

SQL> set lines 999

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              23150

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                INDEX                   10105.5625

WRI$_OPTSTAT_HISTGRM_HISTORY                                  TABLE                        10008

I_WRI$_OPTSTAT_H_ST                                           INDEX                      7424.75

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

 

查看了一下与统计信息相关的几个视图，发现记录还有一年之前的

SQL> select min(savtime),max(savtime) from WRI$_OPTSTAT_TAB_HISTORY;

 

MIN(SAVTIME)

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

MAX(SAVTIME)

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

26-MAY-13 09.01.04.793825 PM +08:00

25-DEC-13 10.42.55.676557 AM +08:00

 

 

SQL> select min(savtime),max(savtime) from WRI$_OPTSTAT_HISTGRM_HISTORY;

 

MIN(SAVTIME)

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

MAX(SAVTIME)

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

16-MAR-12 09.28.07.004180 PM +08:00

25-DEC-13 10.42.55.750147 AM +08:00

 

 

SQL> select min(savtime),max(savtime) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

 

MIN(SAVTIME)

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

MAX(SAVTIME)

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

16-NOV-12 12.00.27.899822 AM +08:00

25-DEC-13 10.42.55.750147 AM +08:00

执行清除统计信息命令

SQL> exec dbms_stats.purge_stats(systimestamp - 500);

 

 

 

PL/SQL procedure successfully completed.

 

SQL> SQL> SQL>

SQL>

SQL>

SQL> exec dbms_stats.purge_stats(systimestamp -300);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.purge_stats(systimestamp - 100);

。。。。。。。一直等啊等

 

通过上述方式呢，对于数据量较大的视图执行效果太缓慢，警告短暂挣扎，决定truncate

SQL> exec dbms_stats.alter_stats_history_retention(-1);

 

PL/SQL procedure successfully completed.

 

SQL> select dbms_stats.get_stats_history_retention from dual;

 

GET_STATS_HISTORY_RETENTION

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

                         -1

 

SQL> truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;

 

Table truncated.

 

SQL> truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;

 

Table truncated.

 

再次执行DBMS_STATS.PURGE_STATS清理就快多了

SQL> exec dbms_stats.purge_stats(sysdate-200);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.purge_stats(sysdate-100);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.purge_stats(sysdate-3);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.alter_stats_history_retention(3);

 

PL/SQL procedure successfully completed.

下面呢，就将跟统计相关的视图、索引进行整理，也就是move、rebuild，在分析一下

SQL> alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;

 

Table altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;

 

Index altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_HH_ST rebuild online;

 

Index altered.

 

SQL> alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux;

 

Table altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;

 

Index altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_H_ST rebuild online;

 

Index altered.

 

SQL> alter table sys.WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;

 

Table altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;

 

Index altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_IND_ST rebuild online;

 

Index altered.

 

SQL> alter table sys.WRI$_OPTSTAT_TAB_HISTORY  move tablespace sysaux;

 

Table altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online;

 

Index altered.

 

SQL> alter index sys.I_WRI$_OPTSTAT_TAB_ST rebuild online;

 

Index altered.

 

SQL> ALTER TABLE SYS.WRI$_OPTSTAT_OPR MOVE TABLESPACE SYSAUX;

 

Table altered.

 

SQL> ALTER TABLE SYS.WRI$_OPTSTAT_AUX_HISTORY MOVE TABLESPACE SYSAUX;

 

Table altered.

 

SQL> ALTER INDEX SYS.I_WRI$_OPTSTAT_AUX_ST REBUILD ONLINE;

 

Index altered.

 

SQL> ALTER INDEX SYS.I_WRI$_OPTSTAT_OPR_STIME REBUILD ONLINE;

 

Index altered.

 

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_IND_HISTORY',cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_TAB_HISTORY',cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_OPR',cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_AUX_HISTORY',cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

待命令执行完成后，再次查看SYSAUX表空间空间详细使用状况，OPTSTAT表基本“消失”了，

SQL> col SEGMENT_NAME for a30

SQL> set lines 999

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              23150

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2065804525_25627   INDEX PARTITION               3030

WRH$_SYSMETRIC_HISTORY                                        TABLE                         1732

WRH$_SYSMETRIC_HISTORY_INDEX                                  INDEX                         1671

WRH$_EVENT_HISTOGRAM_PK        WRH$_EVENT__2065804525_25594   INDEX PARTITION               1291

WRH$_EVENT_HISTOGRAM           WRH$_EVENT__2065804525_25594   TABLE PARTITION               1099

SYS_LOB0000006213C00038$$                                     LOBSEGMENT                     750

WRH$_LATCH_MISSES_SUMMARY_PK   WRH$_LATCH__2065804525_26275   INDEX PARTITION                544

WRH$_SQL_PLAN                                                 TABLE                          482

WRH$_LATCH_MISSES_SUMMARY      WRH$_LATCH__2065804525_26275   TABLE PARTITION                481

 

乘胜追击，查看ASH相关视图中无效的数据，并delete掉

SQL> select count(*)

  2    from sys.wrh$_active_session_history a

  3   where not exists (select 1

  4            from sys.wrm$_snapshot b

  5           where a.snap_id = b.snap_id

  6             and a.dbid = b.dbid

  7             and a.instance_number = b.instance_number);

 

  COUNT(*)

----------

    663769

 

SQL> delete

  2    from sys.wrh$_active_session_history a

  3   where not exists (select 1

  4            from sys.wrm$_snapshot b

  5           where a.snap_id = b.snap_id

  6             and a.dbid = b.dbid

  7             and a.instance_number = b.instance_number);

 

663769 rows deleted.

 

查看wrh$_active_session_history分区情况时发现，占用数据空间最大的那个分区的数据应该为空才对啊，现在的快照只保留三天

      DBID SNAP_INTERVAL

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

RETENTION                                                                   TOPNSQL

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

2065804525 +00000 01:00:00.0

+00003 00:00:00.0                                                           DEFAULT

好吧，直接将该分区truncate掉，其实该分区已没用，所有又drop了一下

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

 

Table truncated.

SQL> alter table sys.wrh$_active_session_history drop partition WRH$_ACTIVE_2065804525_25627 update global indexes;

 

Table altered.

 

再次查看SYSAUX表空间中数据分布情况

SQL> col SEGMENT_NAME for a30

SQL> set lines 999

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$_SYSMETRIC_HISTORY                                        TABLE                         1732

WRH$_SYSMETRIC_HISTORY_INDEX                                  INDEX                         1671

WRH$_EVENT_HISTOGRAM_PK        WRH$_EVENT__2065804525_25594   INDEX PARTITION               1291

WRH$_EVENT_HISTOGRAM           WRH$_EVENT__2065804525_25594   TABLE PARTITION               1099

SYS_LOB0000006213C00038$$                                     LOBSEGMENT                     750

WRH$_LATCH_MISSES_SUMMARY_PK   WRH$_LATCH__2065804525_26275   INDEX PARTITION                544

WRH$_SQL_PLAN                                                 TABLE                          482

WRH$_LATCH_MISSES_SUMMARY      WRH$_LATCH__2065804525_26275   TABLE PARTITION                481

WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_2065804525_31206   TABLE PARTITION                299

WRH$_SYSSTAT_PK                WRH$_SYSSTA_2065804525_26275   INDEX PARTITION                251

 

查看表空间信息，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      69898         85

………

 

再查看一下有没有无效对象什么的：

SQL> select * from dba_indexes where status<>'VALID' AND STATUS<>'N/A';

 

no rows selected

 

SQL> SELECT * FROM DBA_IND_PARTITIONS WHERE STATUS<>'USABLE' AND STATUS<>'N/A';

 

no rows selected

 

SQL> SELECT * FROM DBA_IND_SUBPARTITIONS WHERE STATUS<>'USABLE';

 

no rows selected

 

SQL> select * from dba_indexes where degree not in ('1','0','DEFAULT');

 

no rows selected

 

后记：由于SYSAUX表空间下几个视图、索引数据量有点大，对于常规清除方法过于缓慢、效果更不明显，至于ASH中表WRH$_ACTIVE_SESSION_HISTORY怎么分区的，为什么过期数据没有自动清除，还需要观察一下，再跟踪几天看看。

我的更多文章：
<!-- 正文结束 -->

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

0
0
上一篇： 没有了~
下一篇： 没有了~
请登录后发表评论 登录
全部评论
lsh609

注册时间：2010-03-15

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593