1. 问题现象与初步排查早上9点业务高峰时段CRM系统的节点1突然出现CPU使用率飙升至90%的异常情况。作为DBA我第一时间登录服务器进行检查。通过操作系统层面的top命令确认了CPU资源确实被数据库进程大量占用随即转向数据库内部寻找根源。在检查alert日志和日常巡检报告时发现了一个关键线索TL_B_NOTIFYTRADELOG表的分区索引状态异常。这个表是CRM系统交易流水记录的核心表每日数据增量在百万级别分区设计本应保证查询效率但此刻索引失效可能导致严重的性能问题。经验提示Oracle数据库CPU飙升时第一步永远是检查等待事件和最近统计信息收集情况。90%的情况下性能问题都与这两者相关。2. 深入诊断与分析过程2.1 等待事件分析通过查询gv$system_event视图发现了异常等待事件SELECT event, total_waits, time_waited FROM gv$system_event WHERE wait_class ! Idle ORDER BY time_waited DESC;结果显示gc buffer busy等待事件高居榜首累计等待时间超过1800秒涉及会话数达到200。这个等待事件在RAC环境中通常意味着多个节点频繁访问相同数据块全局缓存服务(Global Cache Service)出现争用可能由于低效SQL导致缓冲区被反复读取2.2 问题SQL定位进一步查询gv$session视图锁定最消耗资源的SQLSELECT sql_id, COUNT(*) as session_count FROM gv$session WHERE event gc buffer busy GROUP BY sql_id ORDER BY session_count DESC;发现sql_id为3gstz9qcvj064的SQL语句关联了85%的等待会话。获取该SQL文本后发现是一个针对TL_B_NOTIFYTRADELOG表的查询本应走分区索引的范围扫描实际却执行了全表扫描。2.3 执行计划分析通过DBMS_XPLAN查看异常SQL的执行计划EXPLAIN PLAN FOR SELECT /* 3gstz9qcvj064 */ trade_no, status FROM TL_B_NOTIFYTRADELOG WHERE create_time BETWEEN :1 AND :2; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);执行计划显示对TL_B_NOTIFYTRADELOG表进行了全分区扫描PARTITION RANGE ALL而没有使用create_time字段上的分区键。这种访问方式对于按月分区的表31个分区意味着需要扫描所有分区I/O消耗呈指数级增长。2.4 统计信息检查检查表的统计信息收集情况SELECT table_name, last_analyzed, stale_stats FROM dba_tab_statistics WHERE owner UCR_UIF1 AND table_name TL_B_NOTIFYTRADELOG;结果显示最后一次统计信息收集是在30天前且stale_stats标记为YES说明统计信息已经过期。更严重的是部分新增分区根本没有统计信息SELECT partition_name, last_analyzed, num_rows FROM dba_tab_partitions WHERE table_owner UCR_UIF1 AND table_name TL_B_NOTIFYTRADELOG ORDER BY partition_name;2.5 索引状态验证最终确认索引失效是根本原因SELECT ip.index_name, ip.partition_name, ip.status FROM dba_ind_partitions ip, dba_indexes i WHERE ip.index_name i.index_name AND i.table_name TL_B_NOTIFYTRADELOG AND i.owner UCR_UIF1;输出显示IDX_TL_B_NOTIFYTRADELOG_1和IDX_TL_B_NOTIFYTRADELOG_2两个索引的所有分区状态均为UNUSABLE。这种情况通常发生在分区维护操作TRUNCATE/EXCHANGE后未重建索引表空间传输后索引未同步直接路径加载数据后未更新索引3. 问题处理与恢复方案3.1 索引重建操作采用并行方式重建分区索引以加快速度ALTER INDEX UCR_UIF1.IDX_TL_B_NOTIFYTRADELOG_1 REBUILD PARTITION P_I_TL_B_NOTIFYTRADELOG_1 PARALLEL 8; -- 对所有31个分区执行类似操作 -- 也可以使用批量重建脚本 BEGIN FOR idx IN (SELECT index_name, partition_name FROM dba_ind_partitions WHERE status UNUSABLE AND index_owner UCR_UIF1) LOOP EXECUTE IMMEDIATE ALTER INDEX UCR_UIF1. || idx.index_name || REBUILD PARTITION || idx.partition_name || PARALLEL 8 NOLOGGING; END LOOP; END; /关键细节在生产环境重建大表索引时务必添加NOLOGGING和PARALLEL参数以减少redo生成和提高速度但要注意并行度不宜超过CPU核心数的50%。3.2 统计信息收集使用DBMS_STATS包进行统计信息收集BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname UCR_UIF1, tabname TL_B_NOTIFYTRADELOG, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree 8, cascade TRUE, granularity ALL, no_invalidate FALSE); END; /参数说明degree并行度设置为8granularity收集表级和分区级统计信息no_invalidate使依赖该表的SQL立即重新解析3.3 SQL执行计划验证重建索引后再次检查问题SQL的执行计划EXECUTE DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR( sql_id 3gstz9qcvj064, format ADVANCED));确认执行计划已从全表扫描变为分区范围扫描预计I/O消耗降低90%以上。4. 根因分析与预防措施4.1 问题根源追溯通过审计日志发现前一日夜间有数据清理作业SELECT sql_text, timestamp FROM dba_audit_trail WHERE obj_name TL_B_NOTIFYTRADELOG AND timestamp SYSDATE-1 ORDER BY timestamp DESC;结果显示执行了分区TRUNCATE操作ALTER TABLE TL_B_NOTIFYTRADELOG TRUNCATE PARTITION P_I_TL_B_NOTIFYTRADELOG_23;但后续未重建该分区的本地索引导致索引失效。这是Oracle的预期行为TRUNCATE分区会使该分区上的本地索引失效必须显式重建。4.2 长效预防机制为避免类似问题再次发生实施以下措施自动化监控脚本-- 每日检查失效索引 SELECT owner, index_name, partition_name, status FROM dba_ind_partitions WHERE status UNUSABLE UNION ALL SELECT owner, index_name, null, status FROM dba_indexes WHERE status UNUSABLE;维护作业标准化所有分区维护操作必须包含索引重建步骤使用事务包装相关操作BEGIN EXECUTE IMMEDIATE ALTER TABLE TL_B_NOTIFYTRADELOG TRUNCATE PARTITION P_I_TL_B_NOTIFYTRADELOG_23; EXECUTE IMMEDIATE ALTER INDEX IDX_TL_B_NOTIFYTRADELOG_1 REBUILD PARTITION P_I_TL_B_NOTIFYTRADELOG_23; EXECUTE IMMEDIATE ALTER INDEX IDX_TL_B_NOTIFYTRADELOG_2 REBUILD PARTITION P_I_TL_B_NOTIFYTRADELOG_23; COMMIT; END;统计信息收集策略优化-- 设置自动统计信息收集偏好 BEGIN DBMS_STATS.SET_TABLE_PREFS( UCR_UIF1, TL_B_NOTIFYTRADELOG, INCREMENTAL, TRUE); DBMS_STATS.SET_TABLE_PREFS( UCR_UIF1, TL_B_NOTIFYTRADELOG, INCREMENTAL_STALENESS, USE_STALE_PERCENT, 5); END;5. 性能对比与优化效果处理前后的关键指标对比指标处理前处理后改善幅度CPU使用率90%35%61%↓平均执行时间4.2秒0.3秒93%↓逻辑读/执行15,00080095%↓gc buffer busy等待1800秒/小时120秒/小时93%↓优化后的AWR报告显示数据库整体吞吐量提升40%平均响应时间从1.8秒降至0.4秒Buffer Cache命中率从85%提高到99%6. 深度优化建议6.1 分区策略优化当前按月分区的设计可能不适合查询模式-- 检查查询条件分布 SELECT EXTRACT(MONTH FROM create_time) as month, COUNT(*) as query_count FROM v$sql WHERE sql_text LIKE %TL_B_NOTIFYTRADELOG% GROUP BY EXTRACT(MONTH FROM create_time);若80%查询集中在最近1个月数据建议改为按范围分区RANGE 列表子分区LIST热数据分区更小如按周冷数据合并为大分区6.2 索引设计改进现有索引可能未覆盖高频查询-- 查询高频过滤条件 SELECT column_name, COUNT(*) as predicate_count FROM v$sql_plan WHERE object_name TL_B_NOTIFYTRADELOG AND operation TABLE ACCESS AND options BY INDEX ROWID GROUP BY column_name;建议考虑创建复合索引(create_time, status)对高频单值查询字段增加位图索引对分区键创建全局分区索引6.3 应用层优化与开发团队合作实现查询改写避免函数索引字段-- 不推荐 WHERE TO_CHAR(create_time,YYYYMM) 202401 -- 推荐 WHERE create_time TO_DATE(20240101,YYYYMMDD) AND create_time TO_DATE(20240201,YYYYMMDD)绑定变量使用检查SELECT force_matching_signature, COUNT(*) FROM v$sql WHERE sql_text LIKE %TL_B_NOTIFYTRADELOG% GROUP BY force_matching_signature HAVING COUNT(*) 10;结果集分页优化-- 传统分页效率低 SELECT * FROM ( SELECT a.*, ROWNUM rn FROM ( SELECT * FROM TL_B_NOTIFYTRADELOG WHERE create_time SYSDATE-30 ORDER BY trade_no ) a WHERE ROWNUM 100 ) WHERE rn 90; -- 推荐方式12c以上 SELECT * FROM TL_B_NOTIFYTRADELOG WHERE create_time SYSDATE-30 ORDER BY trade_no OFFSET 90 ROWS FETCH NEXT 10 ROWS ONLY;7. 应急处理手册当再次出现类似CPU飙升时可按此流程快速响应快速定位问题SQLSELECT sql_id, sql_text, executions, cpu_time/1000000 as cpu_sec FROM v$sqlarea ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;临时缓解措施-- 杀死问题会话 ALTER SYSTEM KILL SESSION sid,serial# IMMEDIATE; -- 限制资源使用 BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(EMERGENCY_GROUP); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan EMERGENCY_PLAN, group_or_subplan EMERGENCY_GROUP, comment Limit CPU for problem SQL, cpu_p1 10); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; / -- 应用限制 BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( attribute SQL_ID, value 3gstz9qcvj064, consumer_group EMERGENCY_GROUP); END; /长期解决方案建立索引状态监控告警定期审核分区维护作业实施SQL审核流程避免全表扫描
