怎么定位哪些SQL产生了大量的Redo日志

2025-03-19 技术教程

本篇内容介绍了“怎么定位哪些SQL产生了大量的Redo日志”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

首先，我们需要定位、判断那个时间段的日志突然暴增了，注意，有些时间段生成了大量的redo log是正常业务行为，有可能每天这个时间段都有大量归档日志生成，例如，有大量作业在这个时间段集中运行。而要分析突然、异常的大量redo log生成情况，就必须有数据分析对比，找到redo log大量产生的时间段，缩小分析的范围是第一步。合理的缩小范围能够方便快速准确定位问题SQL。下面SQL语句分别统计了redo log的切换次数的相关数据指标。这个可以间接判断那个时间段产生了大量归档日志。

/******统计每天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_TIME<TRUNC(SYSDATE)--排除当前这一天GROUPBYTO_CHAR(FIRST_TIME,'YYYY-MM-DD'))SELECTT.LOG_GEN_DAY,T.LOG_SWITCH_NUM,M.AVG_LOG_SWITCH_NUM,(T.LOG_SWITCH_NUM-M.AVG_LOG_SWITCH_NUM)ASDIFF_SWITCH_NUMFROMTCROSSJOIN(SELECTTO_CHAR(AVG(T.LOG_SWITCH_NUM),'999')ASAVG_LOG_SWITCH_NUMFROMT)MORDERBYT.LOG_GEN_DAYDESC;SELECTTO_CHAR(FIRST_TIME,'YYYY-MM-DD')DAY,TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999')"00",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999')"01",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999')"02",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999')"03",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999')"04",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999')"05",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999')"06",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999')"07",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999')"08",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999')"09",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999')"10",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999')"11",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999')"12",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999')"13",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999')"14",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999')"15",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999')"16",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999')"17",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999')"18",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999')"19",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999')"20",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999')"21",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999')"22",TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999')"23"FROMV$LOG_HISTORYGROUPBYTO_CHAR(FIRST_TIME,'YYYY-MM-DD')ORDERBY1DESC;

如下案例所示，2018-03-26日有一个归档日志暴增的情况，我们可以横向、纵向对比分析，然后判定在17点到18点这段时间出现异常，这个时间段与往常对比，生成了大量的redo log。

这里分享一个非常不错的分析redo log 历史信息的SQL

------------------------------------------------------------------------------------------------REMAuthor:RiyajShamsudeen@OraInternals,LLCREMwww.orainternals.comREMREMFunctionality:ThisscriptistoprintredosizeratesinaRACclasterREM**************REMREMSource:AWRtablesREMREMExectutiontype:Executefromsqlplusoranyothertool.REMREMParameters:Noparameters.UsesLastsnapshotandtheonepriorsnapREMNoimpliedorexplicitwarrantyREMREMPleasesendmeanemailtorshamsud@orainternals.com,ifyouenhancethisscript:-)REMThisisaopenSourcecodeanditisfreetouseandmodify.REMVersion1.20REM------------------------------------------------------------------------------------------------setcolsep'|'setlines220altersessionsetnls_date_format='YYYY-MM-DDHH24:MI';setpagesize10000withredo_dataas(SELECTinstance_number,to_date(to_char(redo_date,'DD-MON-YY-HH24:MI'),'DD-MON-YY-HH24:MI')redo_dt,trunc(redo_size/(1024*1024),2)redo_size_mbFROM(SELECTdbid,instance_number,redo_date,redo_size,startup_timeFROM(SELECTsysst.dbid,sysst.instance_number,begin_interval_timeredo_date,startup_time,VALUE-lag(VALUE)OVER(PARTITIONBYsysst.dbid,sysst.instance_number,startup_timeORDERBYbegin_interval_time,sysst.instance_number)redo_sizeFROMsys.wrh$_sysstatsysst,DBA_HIST_SNAPSHOTsnapsWHEREsysst.stat_id=(SELECTstat_idFROMsys.wrh$_stat_nameWHEREstat_name='redosize')ANDsnaps.snap_id=sysst.snap_idANDsnaps.dbid=sysst.dbidANDsysst.instance_number=snaps.instance_numberANDsnaps.begin_interval_time>sysdate-30ORDERBYsnaps.snap_id)))selectinstance_number,redo_dt,redo_size_mb,sum(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语句生成了大量的redo log，当然有可能有些场景会比较复杂，不是那么容易定位。

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报告，那些产生大量redo log的SQL一般是来自TOP Gets、TOP Execution中某个DML SQL语句或一些DML SQL语句，结合上面SQL定位到的对象和下面相关SQL语句，基本上就可以判断就是下面这两个SQL产生了大量的redo log。(第一个SQL是调用包，包里面有对这个表做大量的DELETE、INSERT操作)

如果你此时还不能完全断定，也可以使用下面SQL来辅佐判断那些SQL生成了大量的redo log。在这个案例中，上面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'%<segment_name>%'--此处用具体的段对象替换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-03-2617:00','YYYY-MM-DDHH24:MI')ANDTO_DATE('2018-03-2618:00','YYYY-MM-DDHH24:MI')

其实上面分析已经基本完全定位到SQL语句，剩下的就是和开发人员或Support人员沟通、了解是正常业务逻辑变更还是异常行为。如果需要进一步挖掘深入，我们可以使用日志挖掘工具Log Miner深入分析。在此不做展开分析。其实个人在判断分析时生成了正常时段和出现问题时段的AWR对比报告(WORKLOAD REPOSITORY COMPARE PERIOD REPORT)，如下所示，其中一些信息也可以供分析、对比参考。可以为复杂场景做对比分析(因为复杂场景，仅仅通过最上面的AWR报告可能无法准确定位SQL)

此次截图，没有截取相关SQL，其实就是最上面分析的SQL语句，如果复杂场景下，非常有用。

“怎么定位哪些SQL产生了大量的Redo日志”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！