SYSAUX表空间维护之WRH$_SQLTEXT,WRH$_SQL_PLAN
来源:互联网 发布:视频矩阵切换控制器 编辑:程序博客网 时间:2024/05/01 13:10
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.
- SYSAUX表空间维护之WRH$_SQLTEXT,WRH$_SQL_PLAN
- ORACLE SYSAUX表空间维护之WRH$_SQLTEXT,WRH$_SQL_PLAN
- SYSAUX表空间中的WRH$_SQLTEXT,WRH$_SQL_PLAN 移动LOBSEGMENT类型
- 清理SYSAUX表空间的WRH$_LATCH_CHILDREN表
- 11gr2 rac WRH$_ACTIVE_SESSION_HISTORY未自动清理导致SYSAUX空间过度增长
- 11gr2 rac WRH$_ACTIVE_SESSION_HISTORY未自动清理导致SYSAUX空间过度增长
- Oracle10g以上sysaux表空间的维护和清理
- sys.wrh$_active_session_history v$active_session_history
- Oracle学习笔记之SYSAUX表空间
- SYSAUX 表空间介绍
- Oracle SYSAUX 表空间 说明
- Oracle SYSAUX 表空间 说明
- SYSTEM SYSAUX 表空间恢复
- Oracle SYSAUX 表空间说明
- Oracle-SYSAUX表空间解读
- oracle sysaux表空间管理
- 网页设计心得报告(2) 网站规划 by wrh
- 网页设计心得报告(4) by wrh
- 字符,字节和编码
- IP访问限制插件!!!
- error C2061: 语法错误 : 标识符“__RPC__in”
- 树形结构的cms
- 解决VML遭遇IE8和XHTML DOCTYPE时不能运行的问题
- SYSAUX表空间维护之WRH$_SQLTEXT,WRH$_SQL_PLAN
- ubuntu 不能使用sudo
- WindowsFrom中Mid窗体怎么去掉下拉列表
- php将数据写入xls或csv文件里
- Domenico将爱情藏到了鞋子里
- JavaScript location对象用法详解
- 内核编译配置选项简介 (2.4.20-8)
- JavaScript-Map
- LTE、Wi-Fi、3G、WiMAX等宽带无线技术的概述及分析