ORACLE SYSAUX表空间维护之WRH$_SQLTEXT，WRH$_SQL_PLAN

SYSAUX表空间使用率不断的增加，上个月刚扩了2G，现在又快满了。总去扩表空间也不是长久之计。

决定彻底搞定这个问题。

 

1.查询出那些对象占用了SYSAUX表空间

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024   2    FROM DBA_SEGMENTS   3   WHERE TABLESPACE_NAME = 'SYSAUX';   OWNER  SEGMENT_NAME        SEGMENT_TYPE       BYTES/1024/1024 ------------ ------------------------     ------------------------    ------------------------ SYS          WRH$_SQLTEXT         TABLE                         1443 SYS          WRH$_SQL_PLAN      TABLE                         1982

 

结果发现WRH$_SQLTEXT，WRH$_SQL_PLAN分别占用了1443MB，1982MB的表空间。

 

WRH$_SQLTEXT：保存的是快照期间的SQL。 SQL>SELECT * FROM WRH$_SQLTEXT;      WRH$_SQL_PLAN：保留了快照期间SQL的执行计划。 SQL>SELECT * FROM WRH$_SQL_PLAN

 

由于SYSAUX表空间中存储了大量AWR快照的信息，想通过删除快照的方式来减小

SYSAUX表空间的占用。

 

2.删除指定范围内的快照

-- a)查询快照的SNAP_ID SQL> SELECT T.SNAP_ID   2    FROM SYS.WRH$_ACTIVE_SESSION_HISTORY T   3   GROUP BY T.SNAP_ID   4   ORDER BY T.SNAP_ID; -- b) 传入起始SNAP_ID和结束SNAP_ID及数据库的dbid   begin dbms_workload_repository.drop_snapshot_range(low_snap_id => 15261, high_snap_id => 15302, dbid => 3181236543); end;

 

删除快照后发现，WRH$_SQLTEXT，WRH$_SQL_PLAN变化不大。查阅了meatlink后证实这是10.2.0.4的一个Bug。详见 [ID 1243058.1] [ID 6394861.8]

想想WRH$_SQLTEXT，WRH$_SQL_PLAN这两个表中的记录不过是保存了一些SQL的历史记录，现在即使清理掉也没什么影响。决定truncate 掉这两张表。先在本机，测试环境都跑了一遍，观察了几天没发现什么问题。最后终于找了个空闲时间把这两张表清理了。(可能会对查看历史的AWR报告有一定影响）

 

3.truncate掉WRH$_SQLTEXT，WRH$_SQL_PLAN

SQL> conn / as sysdba Connected.   SQL> TRUNCATE TABLE WRH$_SQLTEXT; Table truncated.   SQL> TRUNCATE TABLE WRH$_SQL_PLAN; Table truncated.   SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024   2    FROM DBA_SEGMENTS   3   WHERE TABLESPACE_NAME = 'SYSAUX'   4     AND SEGMENT_NAME in ( 'WRH$_SQLTEXT','WRH$_SQL_PLAN');   OWNER        SEGMENT_NAME              SEGMENT_TYPE       BYTES/1024/1024 ------------       ------------------------- ------------------ --------------- SYS          WRH$_SQLTEXT              TABLE                        0.0625 SYS          WRH$_SQL_PLAN             TABLE                        0.0625

 

Truncate掉这两张表后，SYSAUX表空间的使用率立即下降到了10%。

 

以下是meatlink上关于SYSAUX表空间的两篇文章的部分片段：

Wrh$_sql_plan table growth causes Sysaux Tablespace size increase continuously [ID 1243058.1]

Symptoms - SYSAUX tablespace keep increasing - Biggest size segments are WRH$_SQL_PLAN and WRH$_SQL_PLAN_PK. - There are more than one baseline exist in workload repository: SQL> select dbid , BASELINE_NAME from wrm$_baseline order by baseline_id;   Cause This problem had been investigated in the following bug: Bug 6394861 - WRH$_SQL_PLAN GROW IN SIZE AND SNAPSHOTS STILL EXISTS AFTER THE RETENTION PERIOD   Solution Apply patch 6394861 for your platform The Fix not enabled by default after patch applied; To enable the fix set event 10455 or 10445 (depending on your installed patchset) to 1 10.2.0.4 patch: SQL> alter system set event= '10455 trace name context forever, level 1';     11.1.0.7 patch: SQL> alter system set event= '10445 trace name context forever, level 1';

 

 

Bug 6394861 - WRH$_SQLTEXT and WRH$_SQL_PLAN are not purged when baselines are present [ID 6394861.8]

Description When a database contained baselines, rows in WRH$_SQLTEXT and WRH$_SQL_PLAN may not get purged, causing excessive space usage.   The purge has different behaviors depending the version: - in 10.2.0.4 is disabled by default because of the lack of   a timeout in 10gR2 that 11gR2 has.   To enable set event 10455 level 1 and event 10456 level 7   - In 10.2.0.5 is disabled by default because of the lack of   a timeout in 10gR2 that 11gR2 has.   To enable set _AWR_MMON_DEEP_PURGE_ENABLED=TRUE   - In 11.1.0.7 is disabled by default because of the lack of   a timeout in 10gR2 that 11gR2 has.   To enable set event 10455 and event 10456 level 7   - In 11.2 it is enabled by default.   This fix introduces a new procedure to help manually purge the tables.   - In 10.2.0.4   alter session set events 'immediate trace name awr_test level 16';   - In 11.1.0.7   alter session set events 'immediate trace name awr_test level 18';   - In 10.2.0.5 & 11gR2   DBMS_WORKLOAD_REPOSITORY.PURGE_SQL_DETAILS;     The PL/SQL call has an optional "maximum number of rows to purge" parameter.   For systems with a lot of data to purge, setting that to some reasonable   value (maybe 5000 or 10000) might be useful.