Oracle SYSAUX表空间爆满？3步快速定位AWR数据膨胀元凶（附诊断脚本）

Oracle SYSAUX表空间爆满的精准诊断与高效修复指南1. 危机现场当SYSAUX表空间告警灯亮起凌晨3点值班手机刺耳的警报声划破夜空——核心业务数据库的SYSAUX表空间使用率突破95%警戒线。DBA最不愿看到的场景正在发生统计信息收集作业频繁失败AWR快照生成中断应用界面操作响应时间从毫秒级骤增至分钟级。这不是普通的性能波动而是即将引发系统性崩溃的前兆。SYSAUX作为Oracle的系统辅助表空间承载着AWR性能数据、优化器统计信息、审计记录等关键元数据。其空间耗尽会导致统计信息真空DBMS_STATS作业无法更新对象统计信息SQL执行计划劣化性能诊断失明AWR快照中断历史性能数据缺失管理功能瘫痪ADDM、SQL调优顾问等依赖SYSAUX的组件失效紧急处理原则先恢复业务再根除隐患。在空间释放前任何扩容操作都只是延缓死亡时间的无效抢救。2. 三级诊断快速定位空间吞噬者2.1 第一级表空间健康度速查执行以下脚本获取空间消耗全景视图SET LINES 200 PAGES 999 COL TABLESPACE_NAME FOR A15 COL USED_PCT FOR 999.99 HEAD USED(%) SELECT TABLESPACE_NAME, USED_SPACE*8/1024 USED_MB, TABLESPACE_SIZE*8/1024 TOTAL_MB, USED_PERCENT USED_PCT FROM DBA_TABLESPACE_USAGE_METRICS WHERE TABLESPACE_NAME SYSAUX;典型异常输出TABLESPACE_NAME USED_MB TOTAL_MB USED(%) -------------- ---------- ---------- -------- SYSAUX 102400 102400 100.002.2 第二级空间占用TOP对象分析锁定具体肇事对象SELECT SEGMENT_NAME, SEGMENT_TYPE, ROUND(BYTES/1024/1024,2) SIZE_MB, OWNER FROM ( SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAMESYSAUX ORDER BY BYTES DESC ) WHERE ROWNUM 10;AWR数据膨胀的典型特征SEGMENT_NAME SEGMENT_TYPE SIZE_MB OWNER ---------------------------- ----------------- --------- ----- WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 42000 SYS WRH$_SYSTEM_EVENT TABLE PARTITION 38000 SYS SYS_LOB0000189232C00003$$ LOB PARTITION 12500 SYS2.3 第三级AWR分区机制健康检查验证分区自动维护状态SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, CREATED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME LIKE WRH$% AND CREATED SYSDATE-7 ORDER BY CREATED;异常现象表现为存在超过保留策略期限默认8天的老旧分区分区HIGH_VALUE与DBA_HIST_SNAPSHOT中的SNAP_ID不匹配单个分区尺寸异常巨大10GB3. 手术级修复分区级精准清理3.1 紧急空间释放方案方案一触发隐藏分区修复机制-- 启用AWR内部调试模式 ALTER SESSION SET _swrf_test_action72; -- 强制创建快照触发分区拆分 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); -- 清理过期分区需根据实际输出调整日期条件 SELECT ALTER TABLE ||SEGMENT_NAME|| TRUNCATE PARTITION || REGEXP_SUBSTR(PARTITION_NAME,[^_],1,4)|| _||REGEXP_SUBSTR(PARTITION_NAME,[^_],5)|| UPDATE GLOBAL INDEXES; FROM DBA_SEGMENTS WHERE TABLESPACE_NAMESYSAUX AND SEGMENT_NAME LIKE WRH$% AND SEGMENT_TYPETABLE PARTITION AND PARTITION_NAME LIKE %||TO_CHAR(SYSDATE-8,YYYYMMDD)||%;方案二定向清理最大分区-- 查找空间占用最大的单个分区 SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, ROUND(BYTES/1024/1024) SIZE_MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAMESYSAUX AND SEGMENT_TYPETABLE PARTITION ORDER BY BYTES DESC FETCH FIRST 1 ROW ONLY; -- 针对性清理示例 ALTER TABLE SYS.WRH$_ACTIVE_SESSION_HISTORY TRUNCATE PARTITION WRH$_ACTIVE_1475634_592 UPDATE GLOBAL INDEXES;3.2 修复后验证指标空间释放量SELECT ROUND((BEFORE_MB-AFTER_MB)/1024,2) SPACE_GB_FREED, ROUND(AFTER_MB/BEFORE_MB*100,2) CURRENT_USAGE_PCT FROM ( SELECT (SELECT USED_SPACE*8/1024 FROM DBA_TABLESPACE_USAGE_METRICS WHERE TABLESPACE_NAMESYSAUX AND ROWNUM1) AFTER_MB, original_used_mb BEFORE_MB FROM DUAL );AWR功能恢复-- 验证快照生成能力 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); -- 检查最新快照时间 SELECT MAX(END_INTERVAL_TIME) LAST_SNAP_TIME FROM DBA_HIST_SNAPSHOT;4. 防御体系构建空间治理长效机制4.1 监控预警配置每日检查脚本-- SYSAUX空间趋势监控 SELECT TO_CHAR(TRUNC(BEGIN_TIME),YYYY-MM-DD) DAY, ROUND(MAX(SPACE_USED)/1024) MAX_MB, ROUND(MAX(SPACE_USED)/MAX(SPACE_ALLOCATED)*100) USED_PCT FROM DBA_HIST_TBSPC_SPACE_USAGE WHERE TABLESPACE_NAMESYSAUX GROUP BY TRUNC(BEGIN_TIME) ORDER BY DAY DESC; -- AWR分区健康检查 SELECT COUNT(DISTINCT PARTITION_NAME) OLD_PARTITIONS FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME LIKE WRH$% AND CREATED SYSDATE-7;预警阈值建议监控指标警告阈值严重阈值检查频率SYSAUX使用率80%90%每小时AWR分区过期天数7天10天每天最大分区尺寸5GB10GB每周4.2 保留策略优化方案根据业务特点调整保留策略-- 标准OLTP环境7天足够 BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention 10080, -- 分钟数(7天) interval 30); -- 快照间隔(分钟) END; / -- 需长期分析场景基线滚动保留 BEGIN -- 创建永久基线 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( start_snap_id start_id, end_snap_id end_id, baseline_name Quarterly_Perf_Base); -- 设置滚动保留策略 DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE( window_size 30); -- 30天滚动窗口 END; /4.3 自动化维护脚本创建定期清理作业BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name SYSAUX_AUTO_CLEANUP, job_type PLSQL_BLOCK, job_action BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( low_snap_id (SELECT MIN(snap_id) FROM dba_hist_snapshot WHERE begin_interval_timeSYSDATE-7), high_snap_id (SELECT MAX(snap_id) FROM dba_hist_snapshot WHERE begin_interval_timeSYSDATE-7)); END;, start_date SYSTIMESTAMP, repeat_interval FREQDAILY;BYHOUR2, enabled TRUE, comments Daily purge expired AWR data); END; /5. 深度解析AWR存储架构与优化实践5.1 AWR分区机制原理Oracle采用时间范围分区快照ID子分区的混合策略一级分区按周划分WRH$_ACTIVE_YYYYMMDD二级子分区按快照ID哈希分布典型问题场景分区分裂失败当SYSAUX空间不足时新快照数据仍写入旧分区高水位线膨胀频繁DML导致分区物理空间无法回收LOB段溢出ASH采样数据中的SQL文本占用大量LOB空间5.2 高级调优参数参数名推荐值作用说明_swrf_max_flush_wait300控制AWR刷盘等待时间(秒)_ash_sampling_interval1000ASH采样频率(毫秒)_awr_snapshot_time_offset随机30-60秒避免RAC节点同时产生快照_awr_restrict_modeFALSE禁用AWR数据访问限制5.3 AWR数据精简策略对于超大型数据库可采用数据采样-- 启用智能采样Oracle 19c ALTER SYSTEM SET _awr_smart_flushTRUE; -- 设置TOP SQL保留阈值 EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( topnsql DEFAULT, retention 10080);在经历多次SYSAUX空间危机后我总结出一个黄金法则空间治理重在预防。建议将AWR健康检查纳入DBA日常巡检清单特别是当数据库经历以下变化时版本升级后AWR格式可能变更业务量陡增ASH数据量爆炸统计信息收集策略调整可能增加OPTSTAT数据