在Oracle运维一线我接到过不少这种求助应用侧说高峰期数据库扛不住开发查不到调用链监控上只能看到CPU和IO告警AWR快照又因为各种原因没留存。这时候你手头还剩什么东西最不惹眼却最靠谱的就是那堆每天自动生成的归档日志。今天要分享的就是如何利用Oracle的归档日志加上LogMiner把业务高峰时段真正跑过的SQL一张张“捞”上来。这个方法我用了不止一次不依赖AWR不需要提前埋点只要归档日志还在哪怕是前几天的高峰SQL也能翻出来。适合DBA、性能优化同学也适合那些被“高峰期慢SQL”反复折磨的运维朋友。1. 为什么日志能当“第三只眼”先搞清楚归档日志里到底有什么1.1 归档日志不是备份文件而是数据库的“行车记录仪”很多人把归档日志当成单纯的备份数据平时只知道它占了存储空间、满了要给业务腾磁盘。实际上归档日志是Oracle在归档模式下把联机重做日志redo log切换之后保存下来的一份完整副本它记录的是数据库每一次物理变更的元数据和数据碎片包括事务的时间戳、操作类型、对象名、SCN、SQL_ID甚至LogMiner还能从中还原出SQL_TEXT。我用一个比较直白的方式理解它redo log是短期行车记录仪只有几路内存缓冲区保存最近几十秒的内容归档日志就是把记录仪的视频定期拷到硬盘。你拷贝下来的每一段都保留了数据库在那一刻“做了什么”的原始痕迹。平时没有性能采集、没有监控埋点的时候这份痕迹就是最硬核的破案依据。对于查找业务高峰SQL关键在于一个事实业务高峰期如果数据库大量执行INSERT、UPDATE、DELETE必然产生对应的redo记录这些记录会带着SQL_ID和操作对象一起落到归档日志里。我们只要把归档日志里各个时间段的redo量拉出来谁在哪个小时“写得最狠”基本就能锁定业务高峰窗口再把窗口内的SQL_ID聚合排序谁在高峰贡献了最大量的变更就一目了然了。1.2 什么场景下必须走“翻归档”这条路我总结下来以下三类场景特别适合用这个方法第一AWR快照没有留存或者已经被自动清理你只有告警时间点和一堆归档日志。这个时候去翻AWR是查不到历史高峰的但是归档日志通常还在尤其是按天备份归档日志、定期拷贝到备份服务器的环境翻个三五天前的日志都没有问题。第二ASH或者活动会话历史覆盖窗口太短。很多环境ASH默认只保留最近1小时你接到工单的时候高峰期已经过去两三个小时了在线动态视图里全是新会话根本看不到当时的会话状态。第三没有开启细粒度审计也不方便用DBMS_MONITOR给业务模块单独打标。这种环境想回答“昨晚8点到底是谁在跑高并发SQL”最直接的道路就是LogMiner挖掘归档日志。当然这个方法也有边界就是它主要能覆盖DML类高峰。纯只读的SELECT并发高峰不产生redo这种情况归档日志的利用价值就会下降。不过实战里真正把数据库压垮的绝大多数还是DML配合大量redo生成、锁竞争、索引更新这些“有重做痕迹”的操作所以归档日志这个第三只眼的覆盖面已经够用了。2. 方案选型为什么LogMiner是性价比最高的路径2.1 三条路对比AWR、审计、LogMiner在确定用归档日志分析之前我把可能的方案都梳理过一遍这里直接给结论方案数据来源能回答高峰SQL问题吗成本坑点AWR报告内存快照取决于快照是否留存低生成报告快高峰期往往没有可用快照统一审计审计日志表/文件能拿到SQL文本需要提前开启记录量大默认没开开了之后磁盘压力不小LogMiner归档日志/联机日志能还原SQL_ID、SQL_TEXT、操作对象需要补充日志配合扫描时间较长归档日志被清理就无能为力从灵活性来说LogMiner最大的优势是“事后追溯”。它不依赖你提前做了什么准备只要数据库本来就是归档模式归档日志还在就能启动挖掘。这个特性在生产应急场景里太重要了因为你没法要求业务先开启所有监控再去出事。从信息量来看LogMiner给出的V$LOGMNR_CONTENTS视图里有我们需要的核心字段OPERATION、TIMESTAMP、SQL_ID、SQL_TEXT、SEG_OWNER、SEG_NAME、USERNAME、CON_ID等。其中OPERATION区分了INSERT、UPDATE、DELETE、DDL等动作TIMESTAMP是操作发生时间SQL_ID和SQL_TEXT用来聚合和识别具体语句SEG_NAME直接给出操作的哪张表。这些字段组合起来足够支撑“按时间窗口按SQL_ID按对象”三个维度的分析。2.2 前置准备补充日志、权限、字典模式写SQL之前先确认几个硬性条件否则后面分析会白忙活。第一确认数据库已经开启最小补充日志。LogMiner解析的就是redo里那套变化数据如果没有补充日志很多关键列的变化不会记录例如UPDATE语句因为无法确认旧值/新值解析出来的SQL_TEXT可能不完整SQL_ID也可能为空。检查方法很简单SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;如果SUPPLEMENTAL_LOG_DATA_MIN为NO需要开启ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER SYSTEM SWITCH LOGFILE;注意开启补充日志之后新产生的redo才会带完整信息存量归档日志是补不回来的。所以这个动作应该作为数据库上线时的标准配置来做而不是等出事了才想起来。第二确认当前用户有权限读LogMiner相关内容。由于LogMiner涉及大量内部字典对象通常需要给用户授予EXECUTE_CATALOG_ROLE或者直接使用具有DBA角色的账号。如果用户没有权限执行DBMS_LOGMNR.START_LOGMNR时大概率会报权限不足。生产环境建议单独建一个用于日志分析的低权限账号只授分析需要的最小权限避免DBA密码到处发。第三选择数据字典模式。最简单的方式是用当前在线数据字典也就是在START_LOGMNR时指定DICT_FROM_ONLINE_CATALOG。这种模式不用额外维护字典文件适合挖掘最近的、表结构变动不大的归档日志。如果归档日志跨度很长期间表结构发生了多次变更更稳妥的方式是用DICT_FROM_REDO_LOGS把字典快照也加入挖掘列表但操作会复杂一些。实战里我大部分场景都是用在线字典离问题时间近结构差异小足够用了。3. 实操把高峰SQL从归档日志里一步步“挖”出来3.1 第一步用V$ARCHIVED_LOG先圈出疑似高峰窗口整个分析过程最忌讳一上来就把一大片归档日志全加载给LogMiner。归档日志动辄几十GB全部加载进去轻则等几十分钟重则把共享池撑爆。正确做法先用V$ARCHIVED_LOG从宏观上定位可疑时间段。思路是按小时统计归档日志的生成量哪个小时生成的日志文件数量多、字节数大哪个小时大概率就是业务变更高峰。SELECT TO_CHAR(completion_time, YYYY-MM-DD HH24) AS hour_bucket, COUNT(*) AS arch_log_count, ROUND(SUM(blocks * block_size) / 1024 / 1024, 2) AS size_mb FROM v$archived_log WHERE completion_time TRUNC(SYSDATE - 2) AND completion_time TRUNC(SYSDATE) AND archived YES AND deleted NO GROUP BY TO_CHAR(completion_time, YYYY-MM-DD HH24) ORDER BY size_mb DESC;注意v$archived_log里DEST_ID可能不同同序列号的日志可能同时归档到多个目的地做统计时最好确认一下自己关注的目标目录。我一般只关心备份目录所以会加DEST_ID1或者用DEST_NAME过滤。拿到结果后找size_mb最大的那个小时窗口再缩小到具体的10分钟或5分钟区间。有一个细节很容易忽略归档日志的completion_time是“日志文件切换完成”的时间不是redo里面业务操作的时间。如果某个业务高峰发生在1点整对应的redo切换归档可能是1点02分左右所以宏观定位之后之后在LogMiner图层里查具体时间点还是以V$LOGMNR_CONTENTS里的TIMESTAMP为准不能直接拿归档日志完成时间去卡业务操作。3.2 第二步把目标归档日志加入LogMiner挖掘圈定了具体的时间区间之后找到落在区间内的归档日志文件。可以通过V$ARCHIVED_LOG查对应路径SELECT sequence#, name, completion_time FROM v$archived_log WHERE completion_time TO_DATE(2024-05-18 00:30:00, YYYY-MM-DD HH24:MI:SS) AND completion_time TO_DATE(2024-05-18 02:00:00, YYYY-MM-DD HH24:MI:SS) AND deleted NO ORDER BY sequence#;然后逐个加入LogMinerBEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME /archivelog/2_45123_1109876543.arc, OPTIONS DBMS_LOGMNR.NEW ); END; /加入第二个、第三个日志文件时把OPTIONS改为DBMS_LOGMNR.ADDFILEBEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME /archivelog/2_45124_1109876543.arc, OPTIONS DBMS_LOGMNR.ADDFILE ); END; /加入完成后启动LogMiner。我的常用选项是BEGIN DBMS_LOGMNR.START_LOGMNR( OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG DBMS_LOGMNR.COMMITTED_DATA_ONLY ); END; /COMMITTED_DATA_ONLY表示只显示已提交事务的数据这样能过滤掉大量回滚和中间状态的记录统计结果更接近真实业务操作。如果你的环境有补日志但有些老日志SQL_ID缺失可以先用这种模式跑一遍后面有需要在去掉这个选项看全量数据。3.3 第三步按小时/分钟聚合LogMiner结果定位业务热度启动之后直接查V$LOGMNR_CONTENTS。先按时间片做一次粗聚合确认高峰窗口SELECT TO_CHAR(timestamp, YYYY-MM-DD HH24:MI) AS minute_bucket, COUNT(*) AS redo_records FROM v$logmnr_contents WHERE timestamp TO_DATE(2024-05-18 00:30:00, YYYY-MM-DD HH24:MI:SS) AND timestamp TO_DATE(2024-05-18 02:00:00, YYYY-MM-DD HH24:MI:SS) GROUP BY TO_CHAR(timestamp, YYYY-MM-DD HH24:MI) ORDER BY redo_records DESC;这里要解释一个容易被误读的点V$LOGMNR_CONTENTS中的一行往往代表一条redo变化记录不是一次SQL执行。一条UPDATE如果修改了1万行很可能在V$LOGMNR_CONTENTS里对应1万条甚至更多的变化行。所以COUNT(*)实际上代表的是“变更量”而不是“执行次数”。用变更量来评估业务热度反而更合理数据库CPU、redo写、索引维护这些开销就是跟着变更行数走的谁变更行数最多谁对系统压力贡献就最大。根据这个聚合结果把窗口缩小到比如01:00到01:05这样的5分钟区间。接下来就是最关键的一步按SQL_ID统计高峰SQL。3.4 第四步按SQL_ID聚合找出高峰“肇事SQL”进入明确的高峰时间窗之后直接对SQL_ID做聚合SELECT sql_id, COUNT(*) AS redo_row_cnt, COUNT(DISTINCT seg_owner || . || seg_name) AS obj_cnt, SUBSTR(MAX(sql_text), 1, 300) AS sql_text_sample FROM v$logmnr_contents WHERE timestamp TO_DATE(2024-05-18 01:00:00, YYYY-MM-DD HH24:MI:SS) AND timestamp TO_DATE(2024-05-18 01:05:00, YYYY-MM-DD HH24:MI:SS) AND sql_id IS NOT NULL GROUP BY sql_id ORDER BY redo_row_cnt DESC;跑完之后排在前面的SQL_ID基本就是高峰期的核心变更源。如果SQL_TEXT字段有内容直接能看到语句大致样子如果SQL_TEXT显示为空再用SQL_ID去关联在线共享池或历史SQL视图WITH peak_sql AS ( SELECT sql_id, COUNT(*) AS cnt FROM v$logmnr_contents WHERE timestamp TO_DATE(2024-05-18 01:00:00, YYYY-MM-DD HH24:MI:SS) AND timestamp TO_DATE(2024-05-18 01:05:00, YYYY-MM-DD HH24:MI:SS) AND sql_id IS NOT NULL GROUP BY sql_id ) SELECT p.sql_id, p.cnt, SUBSTR(COALESCE(sa.sql_text, hs.sql_text), 1, 300) AS sql_text FROM peak_sql p LEFT JOIN v$sqlarea sa ON sa.sql_id p.sql_id LEFT JOIN dba_hist_sqltext hs ON hs.sql_id p.sql_id AND hs.dbid (SELECT dbid FROM v$database) ORDER BY p.cnt DESC;如果两个关联都取不到SQL文本还有一个土办法直接从V$LOGMNR_CONTENTS中取该SQL_ID任一条记录的SQL_TEXT字段。LogMiner的SQL_TEXT虽然可能只有片段但通常已经能看出语句类型、目标表和关键条件了判断大方向足够。这里有个排序上的坑你不能只按redo_row_cnt排序就下结论。比如一个是1万行INSERT但语句简单另一个是5000行UPDATE但每次都要更新索引、触发大量redo两个对系统的影响维度不同。所以统计时最好把“变更行数”“涉及对象数”一起看现在这个查询里已经加上了obj_cnt实战中很有参考意义。4. 实战案例凌晨1点的批量UPDATE到底来自哪4.1 现象CPU告警没有对应应用记录有一次我们接手一个核心系统的排查现象是每天早上01:00到01:10数据库服务器CPU使用率冲到95%以上持续大约7分钟。应用团队查了自己的调度平台发现00:50和01:20都有定时任务偏偏01:00这个时间点是空档没有任务配置。监控平台只能看到CPU高、IO写高看不出具体语句。由于当天没有保留AWR快照快照策略是每2小时一次但凌晨1点的快照因为系统繁忙没能正常生成最终我们只能走归档日志分析这条路。4.2 分析过程归档日志给出的关键线索先跑V$ARCHIVED_LOG统计结果很直观01:00到01:10这10分钟里产生的归档日志量占当天全天归档日志量的近四分之一比平时整点的批量任务还要高一个量级。然后把这10分钟的归档日志加入LogMiner按SQL_ID聚合结果如下排名SQL_ID变更行数涉及对象18x3j2m9yq2v1c约86万行T_ORDER_FLOW26z4v8h7q2a3b约5万行T_SMS_SEND_LOG32y9u3k4m8s0x约2万行T_ORDER_FLOW拿到SQL_ID之后通过DBA_HIST_SQLTEXT关联出了完整的SQL文本是一条UPDATE语句大概是这个形态UPDATE T_ORDER_FLOW SET SETTLE_STATUS DONE, SETTLE_TIME SYSDATE WHERE SETTLE_DATE :B1 AND SETTLE_STATUS WAIT看到SETTLE_DATE、SETTLE_STATUS之后基本锁定是结算模块的“日终结算状态批量置完成”逻辑。再查DBMS_SCHEDULER的JOB运行历史发现确实有一个名为“SP_SETTLE_DAILY”的DBMS_JOB但调度平台没有展示因为它是数据库内部的JOB很多应用团队根本看不到。之前运维团队调整过这个JOB的调度时间却忽略了它依赖的数据量因为业务增长已经翻了几十倍导致原本2分钟跑完的批量UPDATE现在要7分钟而且高峰期把CPU打满。4.3 结果定位与优化落点定位并不算结束真正的优化动作还要回到三条线上一是调整调度时间把这个JOB从01:00挪到流量更低的02:30错峰执行这个改动最快当晚就能见效二是优化SQL本身SETTLE_DATE条件其实可以改成按主键分段批量提交每次只更新1万行减少单条事务对UNDO和redo的累积影响三是建议业务侧回顾日报的生成链路看看能否增量更新而不是每天全量扫T_ORDER_FLOW。这个案例里LogMiner的分析只花了一个多小时核心查询也就是本章前两节写的那些SQL。真正花时间的是把SQL_ID对应到业务模块这一步。我的做法是用SQL文本和对象名去代码仓库里搜关键字一下就能定位到对应代码位置再配合DBMS_SCHEDULER的JOB日志确认调用来源。5. 常见问题与排查技巧速查表5.1 SQL_ID为空或者SQL_TEXT为空怎么办这个问题出现频率最高。原因大概率是开启补充日志之前的归档日志或者挖掘的日志没有包含足够信息。遇到这种情况先不要急着下结论回退一步用SEG_NAME和OPERATION来做聚合看看高峰期间DML集中发生在哪些表上。虽然拿不到精确SQL_ID但对象维度的分析同样能定位到业务模块。还有一种情况是LogMiner对部分操作类型的支持不完整例如某些LOB操作、NOLOGGING表操作在V$LOGMNR_CONTENTS里可能看不到完整记录。这类操作本来就不产生或很少产生redo所以它们在业务高峰里不一定是主要矛盾可以先放到一边。5.2 归档日志太多LogMiner慢到怀疑人生几个方向可以提速第一用V$ARCHIVED_LOG先缩小时间范围别把一天日志全部导入实际分析往往只需要高峰期前后半小时到一小时第二在START_LOGMNR时启用COMMITTED_DATA_ONLY减少中间状态记录第三尽量不要在业务高峰时段对生产库执行LogMiner操作LogMiner本身会消耗大量内存和I/O最好在备库或者恢复出来的克隆库上处理这个是生产环境安全底线。如果一定要在生产库上跑建议把查询结果用CREATE TABLE AS SELECT先落盘避免反复扫描V$LOGMNR_CONTENTS导致共享池对象失效。实际操作中我先聚合出明细表再在这个临时表上做SQL_ID统计效率和稳定性都好一截。5.3 12c/19c多租户环境下的挖掘坑如果你用的是CDB架构需要注意LogMiner在CDB根容器中运行时V$LOGMNR_CONTENTS会包含所有PDB的数据要注意用CON_ID字段区分业务库。如果直接在某一个PDB中启动LogMiner很可能会因为字典不匹配报错或者查不到数据。另外补充日志如果在CDB根开启通常对下面所有PDB生效如果只在某个PDB开启该PDB的重做数据在根容器里解析时信息就可能不完整。所以多租户环境建议统一在CDB根开启最小补充日志分析时在根容器操作最后用CON_ID过滤到具体PDB。5.4 归档日志不连续报ORA-01291或者ORA-01289ORA-01291通常是缺少某个日志文件导致无法连续挖掘。解决思路是顺着V$ARCHIVED_LOG找到目标区间前后的日志序列号把缺失的日志从备份恢复出来再加载。如果没有备份那只能缩小时间范围跳过不连续段再用其他方法如审计、应用日志补充分析。ORA-01289一般是重复加载了同一个日志。ADD_LOGFILE时不小心重复添加了相同文件可以用DBMS_LOGMNR.REMOVE_LOGFILE清理或者直接END_LOGMNR后重新ADD。5.5 从分析结果到“定案”还差最后一环即使LogMiner已经把SQL_ID、SQL_TEXT都捞出来了我还是建议再搭一层关联把分析结果和应用的发布记录、调度平台记录、业务报表时间点对一下。SQL只是告诉你“数据库层面发生了什么”而业务为什么会在这个时间点发起这个SQL往往需要结合代码提交记录和任务调度信息共同确认。不要只看SQL就去找开发扯皮带上时间线、对象、调用来源一起说沟通效率高很多。最后说点我自己的体会这套“归档日志分析”方法我实践了很多次最大的感触是数据库管理员不能等到系统出问题了才想起日志的重要性。归档日志平时看着碍眼关键时刻是真能救命。随手把下面的SQL整理成脚本每天自动跑一遍归档日志量统计一旦发现某个时间点归档日志异常增长再触发一次LogMiner分析高峰SQL定位的效率会高很多。等到你要临时翻日志的时候补充日志没开、归档日志被清理、权限不足各种坑都会冒出来那就只剩下被动了。这个方法还可以继续扩展比如结合ASM里的别名路径直接定位日志文件或者在备库上做定期采样把历史高峰SQL自动存档下来后续做容量评估和上线压测的时候都能用得上。