WRH$_SQL_PLAN WRH$_SQLTEXT过大占用SYSAUX表空间

Maclean Liu(刘相兵

WRH$_SQL_PLAN WRH$_SQLTEXT过大占用SYSAUX表空间

数据库中由于AWR快照保留时间略长(31天)且应用所运行的SQL文本较长，导致AWR自动负载仓库的几张基表较大，占用了SYSAUX表空间的大量空间，同时也拖慢了AWR报告的查询。 通过分析发现是AWR的SQLPLAN、ASH和SQLTEXT 三个组件占用了大部分的空间，其中最大的2个数据段是WRH$_SQL_PLAN和WRH$_SQLTEXT的LOB SYS_LOB0000008984C00004$$。

由于WRH$_SQL_PLAN和WRH$_SQLTEXT不是系统关键的数据字典对象，故在没有保留性能快照数据历史的前提下可以对这2张表执行TRUNCATE清理操作，但是由于这2张表可能被AWR用户使用，所以要确保TRUNCATE之前没有锁存在。

步骤1：检查WRH$_SQL_PLAN和WRH$_SQLTEXT的大小：
select bytes / 1024 / 1024, segment_name
  from dba_segments
where segment_name in
       ('WRH$_SQLTEXT', 'SYS_LOB0000008984C00004$$', 'WRH$_SQL_PLAN');

步骤2：若觉得有清理以上2张表数据的必要，则先检查是否有锁：
select *
  from v$lock
where type = 'TM'
   and id1 in
       (select object_id
          from dba_objects
         where object_name in ('WRH$_SQL_PLAN', 'WRH$_SQLTEXT'));


步骤3：实际TRUNCATE截断表:
Set timing on;
truncate table sys.WRH$_SQLTEXT reuse storage;
truncate table sys.WRH$_SQL_PLAN reuse storage;      
truncate table sys.WRH$_SQLTEXT ;
truncate table sys.WRH$_SQL_PLAN ;

步骤4： 检查WRH$_SQL_PLAN和WRH$_SQLTEXT的大小变化，执行步骤1的操作。

liuwenqing

用自带的dbms包不是更安全！～

Maclean Liu(刘相兵

liuwenqing 发表于 2014-3-13 17:48
用自带的dbms包不是更安全！～

印象中 一开始用了标准方法，但耗时太久 根本出不来