WRH$_LATCH, WRH$_SYSSTAT, WRH$_PARAMETER对象较大
惜分飞发表于 2023-07-31 12:24:28
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：WRH$_LATCH, WRH$_SYSSTAT, WRH$_PARAMETER对象较大
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
通过awrinfo查看发现sysaux中以下对象大小属于top N
********************************** (3b) Space usage within AWR Components (> 500K) ********************************** COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE --------- --------- --------------------------------------------------------------------- --------------- FIXED 136.0 WRH$_PARAMETER_PK.WRH$_PARAME_1600597976_0 - 68% INDEX PARTITION FIXED 128.0 WRH$_LATCH.WRH$_LATCH_1600597976_0 - 98% TABLE PARTITION FIXED 104.0 WRH$_PARAMETER.WRH$_PARAME_1600597976_0 - 97% TABLE PARTITION FIXED 88.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_1600597976_0 - 99% INDEX PARTITION FIXED 88.0 WRH$_SYSSTAT.WRH$_SYSSTA_1600597976_0 - 90% TABLE PARTITION FIXED 80.0 WRH$_LATCH_PK.WRH$_LATCH_1600597976_0 - 99% INDEX PARTITION
查新mos发现类似文档:WRH$_LATCH, WRH$_SYSSTAT, and WRH$_PARAMETER Consume the Majority of Space within SYSAUX (Doc ID 2099998.1)
对应的bug为：Bug 14084247 – ORA-1555 or ORA-12571 Failed AWR purge can lead to continued SYSAUX space use (Doc ID 14084247.8)
处理操作
SQL> SELECT COUNT(1) HOW_MANY 2 FROM sys.WRH$_PARAMETER a 3 WHERE NOT EXISTS 4 (SELECT 1 5 FROM sys.wrm$_snapshot 6 WHERE snap_id = a.snap_id 7 AND dbid = a.dbid 8 AND instance_number = a.instance_number 9 ); HOW_MANY ---------- 2406788 SQL> DELETE FROM sys.WRH$_LATCH a 2 WHERE NOT EXISTS 3 (SELECT 1 4 FROM sys.wrm$_snapshot b 5 WHERE b.snap_id = a.snap_id 6 AND dbid=(SELECT dbid FROM v$database) 7 AND b.dbid = a.dbid 8 AND b.instance_number = a.instance_number); 已删除2411808行。 SQL> SQL> DELETE FROM sys.WRH$_SYSSTAT a 2 WHERE NOT EXISTS 3 (SELECT 1 4 FROM sys.wrm$_snapshot b 5 WHERE b.snap_id = a.snap_id 6 AND dbid=(SELECT dbid FROM v$database) 7 AND b.dbid = a.dbid 8 AND b.instance_number = a.instance_number); 已删除2747472行。 SQL> SQL> DELETE FROM sys.WRH$_PARAMETER a 2 WHERE NOT EXISTS 3 (SELECT 1 4 FROM sys.wrm$_snapshot b 5 WHERE b.snap_id = a.snap_id 6 AND dbid=(SELECT dbid FROM v$database) 7 AND b.dbid = a.dbid 8 AND b.instance_number = a.instance_number); 已删除2406788行。 SQL> SQL> COMMIT; 提交完成。 SQL> ALTER TABLE WRH$_LATCH ENABLE ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_LATCH SHRINK SPACE COMPACT; 表已更改。 SQL> ALTER TABLE WRH$_LATCH SHRINK SPACE; 表已更改。 SQL> ALTER TABLE WRH$_LATCH SHRINK SPACE CASCADE; 表已更改。 SQL> SQL> ALTER TABLE WRH$_PARAMETER ENABLE ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER SHRINK SPACE COMPACT; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER SHRINK SPACE; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER SHRINK SPACE CASCADE; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT ENABLE ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT SHRINK SPACE COMPACT; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT SHRINK SPACE; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT SHRINK SPACE CASCADE; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT disable ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER disable ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_LATCH disable ROW MOVEMENT; 表已更改。
再次查看这些TOP对象消失
********************************** (3b) Space usage within AWR Components (> 500K) ********************************** COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE --------- --------- --------------------------------------------------------------------- --------------- FIXED 56.0 WRH$_SERVICE_STAT_PK.WRH$_SERVIC_1600597976_0 - 64% INDEX PARTITION FIXED 29.0 WRH$_SERVICE_STAT.WRH$_SERVIC_1600597976_0 - 95% TABLE PARTITION FIXED 26.0 WRH$_ROWCACHE_SUMMARY.WRH$_ROWCAC_1600597976_0 - 96% TABLE PARTITION FIXED 21.0 WRH$_MVPARAMETER.WRH$_MVPARA_1600597976_0 - 95% TABLE PARTITION FIXED 17.0 WRH$_ROWCACHE_SUMMARY_PK.WRH$_ROWCAC_1600597976_0 - 98% INDEX PARTITION FIXED 17.0 WRH$_MVPARAMETER_PK.WRH$_MVPARA_1600597976_0 - 97% INDEX PARTITION FIXED 12.0 WRH$_SYSMETRIC_HISTORY - 45% TABLE