本文转载自微信公众号《DBA的闲思杂想》，作者潇湘隐士。转载本文请联系DBA杂念公众号。在管理和维护ORACLE数据库的过程中，偶尔会出现归档日志的激增，也就是说某些SQL语句产生了大量的重做日志，那么如何跟踪定位哪些SQL语句产生了大量重做日志日志？?下面文章结合实际案例和官方文档《HowtoidentifythecausesofHighRedoGeneration(DocumentID2265722.1)》来验证判断。首先，我们需要定位判断那个时间段的日志突然暴增。请注意，在某些时间段内产生大量重做日志是正常的业务行为。每天有可能在这个时间段产生大量的归档日志。比如这个时间段有大量的Jobs在密集运行。但是，要分析大量重做日志的突然异常产生，就需要进行数据分析和对比。找到大量重做日志产生的时间段，缩小分析范围是第一步。合理缩小范围可以方便、快速、准确定位到问题SQL。以下SQL语句分别统计redolog的切换次数的相关数据指标。由此可以间接判断出该时间段内产生了大量的归档日志。/******统计每日redolog切换次数，并与平均值对比*****/WITHTAS(SELECTTO_CHAR(FIRST_TIME,'YYYY-MM-DD')ASLOG_GEN_DAY,TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),1,0)),'999')AS"LOG_SWITCH_NUM"FROMV$LOG_HISTORYWHEREFIRST_TIMEsysdate-30ORDERBYsnaps.snap_id)),selectindost(resumid_number)redo_size_mb)over(partitionbytrunc(redo_dt))total_daily,trunc(sum(redo_size_mb)over(partitionbytrunc(redo_dt))/24,2)hourly_ratefromredo_Dataorderbyredo_dt,instance_number/分析的这个阶段，我们只得到了那个时间段的归档日志异常（归档日志激增），那么如何定位相关的SQL语句呢？我们可以用下面的SQL来定位：在这个时间段内，哪些对象有大量的数据块变化如下，这两个对象（当然对象可能是表也可能是索引。在这种情况下，这两个对象实际上是同一张表及其主键索引）有大量的数据块修改。基本上可以判断涉及该对象的DML语句产生了大量的redolog。当然，有些场景可能比较复杂，不是那么容易定位的。SELECTTO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DDHH24')SNAP_TIME,DHSO.OBJECT_NAME,SUM(DB_BLOCK_CHANGES_DELTA)BLOCK_CHANGEDFROMDBA_HIST_SEG_STATDHSS,DBA_HIST_SEG_STAT_OBJDHSO,DBA_HIST_SNAPSHOTDHSWHEREDHS.SNAP_ID=DHSS.SNAP_IDANDDHS.INSTANCE_NUMBER=DHSS.INSTANCE_NUMBERANDDHSS.OBJ#=DHSO.OBJ#ANDDHSS.DATAOBJ#=DHSO.DATAOBJ#ANDBEGIN_INTERVAL_TIMEBETWEENTO_DATE('2018-03-2617:00','YYYY-MM-DDHH24:MI')ANDTO_DATE('2018-03-2618:00','YYYY-MM-DDHH24:MI')GROUPBYTO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DDHH24'),DHSO.OBJECT_NAMEHAVINGSUM(DB_BLOCK_CHANGES_DELTA)>0ORDERBYSUM(DB_BLOCK_CHANGES_DELTA)DESC;这时候我们就可以生成这个时间段的AWR报告，而那些产生大量redolog的SQL一般都是从TOP中的某条DMLSQL语句或者获取到TOP中的一些DMLSQL语句，结合定位到的对象通过上面的SQL和下面的相关SQL语句，基本可以判断出下面两条SQL产生了大量的redolog。（第一个SQL是call包，里面包含了对这张表的大量DELETE和INSERT操作）重做日志的数量。在这种情况下，在上面的AWR报告中找到的SQL语句与下面的SQL抓取到的SQL基本一致。那么可以进一步佐证。注意这条SQL语句执行速度慢，执行过程中需要修改相关条件：时间和具体段对象。SELECTTO_CHAR(BEGIN_INTERVAL_TIME,'YYYY_MM_DDHH24')WHEN,DBMS_LOB.SUBSTR(SQL_TEXT,4000,1)SQL,DHSS.INSTANCE_NUMBERINST_ID,DHSS.SQL_ID,EXECUTIONS_DELTAEXEC_DELTA,ROWS_PROCESSED_DELTAROWS_PROC_DELTAFROMDBA_HIST_SQLSTATDHSS,DBA_HIST_SNAPSHOTDHS,DBA_HIST_SQLTEXTDHSTWHEREUPPER(DHST.SQL_TEXT)LIKE'%%'--替换ANDLTRIM(UPPER(DHST.SQL_TEXT))NOTLIKE'SELECT%'ANDDHSS.SNAP_ID=DHS.SNAP_IDANDDHSS.INSTANCE_NUMBER=DHS.INSTANCE_NUMBERANDDHSS.SQL_ID=DHST.SQL_IDANDBEGIN_INTERVAL_TIMEBETWEENTO_DATE('2018-7:00','YYYY-MM-DDHH24:MI')ANDTO_DATE('2018-03-2618:00','YYYY-MM-DDHH24:MI')其实上面的分析已经基本完整定位了SQL语句，剩下的就是和开发者交流了或者支持人员了解是正常的业务逻辑变化还是异常行为。如果需要深入挖掘，可以使用日志挖掘工具LogMiner进行深入分析。这里不做进一步分析。其实我在判断分析的时候生成了正常时间段和有问题时间段的AWR对比报告（WORKLOADREPOSITORYCOMPAREPERIODREPORT），如下图，其中一些信息也可以作为分析对比参考。复杂的场景可以做对比分析（因为场景复杂，只通过topAWR报表可能无法准确定位到SQL）。在这个截图中，没有拦截到相关的SQL。其实就是最上面分析的SQL语句。它在复杂的场景中非常有用。.参考：HowtoidentifythecausesofHighRedoGeneration(DocID2265722.1)
