如何定位那些SQL产生了大量的redo日志

标签: 基础技术 redo日志 SQL 问题分析 | 发表时间:2018-04-04 21:30 | 作者:TiuVe2
出处:http://www.importnew.com

在ORACLE数据库的管理、维护过程中,偶尔会遇到归档日志暴增的情况,也就是说一些SQL语句产生了大量的redo log,那么如何跟踪、定位哪些SQL语句生成了大量的redo log日志呢? 下面这篇文章结合实际案例和官方文档“How to identify the causes of High Redo Generation (文档 ID 2265722.1)”来实验验证一下。

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

/******统计每天redo log的切换次数汇总,以及与平均次数的对比*****/
WITH T AS 
(
    SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD')    AS LOG_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" 
    FROM   V$LOG_HISTORY 
  WHERE FIRST_TIME < TRUNC(SYSDATE)  --排除当前这一天
    GROUP  BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD') 
)
SELECT  T.LOG_GEN_DAY
          , T.LOG_SWITCH_NUM
          , M.AVG_LOG_SWITCH_NUM
      , (T.LOG_SWITCH_NUM-M.AVG_LOG_SWITCH_NUM) AS DIFF_SWITCH_NUM
FROM  T CROSS JOIN 
(
    SELECT  TO_CHAR(AVG(T.LOG_SWITCH_NUM),'999') AS AVG_LOG_SWITCH_NUM
    FROM T
) M
ORDER BY T.LOG_GEN_DAY DESC;
SELECT    TO_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"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD') 
ORDER BY 1 DESC;

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


clip_image001 

clip_image002

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

-----------
REM Author: Riyaj Shamsudeen @OraInternals, LLC
REM         www.orainternals.com
REM
REM Functionality: This script is to print redo size rates in a RAC claster
REM **************
REM
REM Source  : AWR tables
REM
REM Exectution type: Execute from sqlplus or any other tool.
REM
REM Parameters: No parameters. Uses Last snapshot and the one prior snap
REM No implied or explicit warranty
REM
REM Please send me an email to [email protected], if you enhance this script :-)
REM  This is a open Source code and it is free to use and modify.
REM Version 1.20
REM
------------------------------------------------------------------------------------------------
 
set colsep '|'
set lines 220
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
set pagesize 10000
with redo_data as (
SELECT instance_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_mb
 FROM  (
  SELECT dbid, instance_number, redo_date, redo_size , startup_time  FROM  (
    SELECT  sysst.dbid,sysst.instance_number, begin_interval_time redo_date, startup_time,
  VALUE -
    lag (VALUE) OVER
    ( PARTITION BY  sysst.dbid, sysst.instance_number, startup_time
      ORDER BY begin_interval_time ,sysst.instance_number
     ) redo_size
  FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE sysst.stat_id =
       ( SELECT stat_id FROM sys.wrh$_stat_name WHERE  stat_name='redo size' )
  AND snaps.snap_id = sysst.snap_id
  AND snaps.dbid =sysst.dbid
  AND sysst.instance_number  = snaps.instance_number
  AND snaps.begin_interval_time> sysdate-30
   ORDER BY snaps.snap_id )
  )
)
select  instance_number,  redo_dt, redo_size_mb,
    sum (redo_size_mb) over (partition by  trunc(redo_dt)) total_daily,
    trunc(sum (redo_size_mb) over (partition by  trunc(redo_dt))/24,2) hourly_rate
   from redo_Data
order by redo_dt, instance_number
/

image

分析到这个阶段,我们还只获取了那个时间段归档日志异常(归档日志暴增),那么要如何定位到相关的SQL语句呢?我们可以用下面SQL来定位:在这个时间段,哪些对象有大量数据块变化情况。如下所示,这两个对象(当然,对象有可能是表或索引,这个案例中,这两个对象其实是同一个表和其主键索引)有大量的数据块修改情况。基本上我们可以判断是涉及这个对象的DML语句生成了大量的redo log, 当然有可能有些场景会比较复杂,不是那么容易定位。

SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME, 
       DHSO.OBJECT_NAME, 
       SUM(DB_BLOCK_CHANGES_DELTA)                     BLOCK_CHANGED 
FROM   DBA_HIST_SEG_STAT DHSS, 
       DBA_HIST_SEG_STAT_OBJ DHSO, 
       DBA_HIST_SNAPSHOT DHS 
WHERE  DHS.SNAP_ID = DHSS.SNAP_ID 
       AND DHS.INSTANCE_NUMBER = DHSS.INSTANCE_NUMBER 
       AND DHSS.OBJ# = DHSO.OBJ# 
       AND DHSS.DATAOBJ# = DHSO.DATAOBJ# 
       AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2018-03-26 17:00', 
                                       'YYYY-MM-DD HH24:MI') 
                                       AND 
           TO_DATE('2018-03-26 18:00', 'YYYY-MM-DD HH24:MI') 
GROUP  BY TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'), 
          DHSO.OBJECT_NAME 
HAVING SUM(DB_BLOCK_CHANGES_DELTA) > 0 
ORDER  BY SUM(DB_BLOCK_CHANGES_DELTA) DESC;

clip_image003

此时,我们可以生成这个时间段的AWR报告,那些产生大量redo log的SQL一般是来自TOP Gets、TOP Execution中某个DML SQL语句或一些DML SQL语句,结合上面SQL定位到的对象和下面相关SQL语句,基本上就可以判断就是下面这两个SQL产生了大量的redo log。(第一个SQL是调用包,包里面有对这个表做大量的DELETE、INSERT操作)

clip_image004

如果你此时还不能完全断定,也可以使用下面SQL来辅佐判断那些SQL生成了大量的redo log。 在这个案例中, 上面AWR报告中发现的SQL语句和下面SQL捕获的SQL基本一致。那么可以进一步佐证。 

注意,该SQL语句执行较慢,执行时需要修改相关条件:时间和具体段对象。

SELECT TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY_MM_DD HH24') WHEN,
             DBMS_LOB.SUBSTR(SQL_TEXT,4000,1) SQL,
             DHSS.INSTANCE_NUMBER INST_ID,
             DHSS.SQL_ID,
             EXECUTIONS_DELTA EXEC_DELTA,
             ROWS_PROCESSED_DELTA ROWS_PROC_DELTA
FROM DBA_HIST_SQLSTAT DHSS,
         DBA_HIST_SNAPSHOT DHS,
         DBA_HIST_SQLTEXT DHST
WHERE UPPER(DHST.SQL_TEXT) LIKE '%<segment_name>%'  --此处用具体的段对象替换
  AND LTRIM(UPPER(DHST.SQL_TEXT)) NOT LIKE 'SELECT%'
  AND DHSS.SNAP_ID=DHS.SNAP_ID
  AND DHSS.INSTANCE_NUMBER=DHS.INSTANCE_NUMBER
  AND DHSS.SQL_ID=DHST.SQL_ID
  AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2018-03-26 17:00','YYYY-MM-DD HH24:MI')
  AND TO_DATE('2018-03-26 18:00','YYYY-MM-DD HH24:MI')

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

clip_image005

clip_image006

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


clip_image007 

clip_image008

参考资料:

  • How to identify the causes of High Redo Generation (文档 ID 2265722.1)

相关文章

相关 [sql redo 日志] 推荐:

如何定位那些SQL产生了大量的redo日志

- - ImportNew
在ORACLE数据库的管理、维护过程中,偶尔会遇到归档日志暴增的情况,也就是说一些SQL语句产生了大量的redo log,那么如何跟踪、定位哪些SQL语句生成了大量的redo log日志呢. 下面这篇文章结合实际案例和官方文档“How to identify the causes of High Redo Generation (文档 ID 2265722.1)”来实验验证一下.

必须了解的MySQL三大日志:binlog、redo log和undo log

- - DockOne.io
日志是 MySQL数据库的重要组成部分,记录着数据库运行期间各种状态信息. MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类. 作为开发,我们重点需要关注的是二进制日志( binlog)和事务日志(包括 redo log和 undo log),本文接下来会详细介绍这三种日志.

REDO管理

- - CSDN博客数据库推荐文章
一、什么是REDO LOG.  REDOLOG文件是十分重要的文件,它记录了Oracle的所有变化,是数据库实例恢复机制中最为关键的组成部分.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME.

理解数据库中的undo日志、redo日志、检查点 | 乐天的个人网站

- -
数据库存放数据的文件,本文称其为data file. 数据库的内容在内存里是有缓存的,这里命名为db buffer. 某次操作,我们取了数据库某表格中的数据,这个数据会在内存中缓存一些时间. 对这个数据的修改在开始时候也只是修改在内存中的内容. 当db buffer已满或者遇到其他的情况,这些数据会写入data file.

Oracle online redo log 扫盲

- - CSDN博客数据库推荐文章
Oracle 的日志分为:ONLINE REDO LOG 和 archived log. 一个数据库至少要有2组 redo log,每组 redo log 至少要有一个 member(出于安全考虑,建议每组 redo log 至少有 2 个多元化的 redo log member). redo log 循环使用,当一组日志写满后,就会切换到下一组日志.

Oracle EBS SQL Trace日志收集的方法

- - CSDN博客推荐文章
Raw Trace的收集方法. 打开Trace,Help > Diagnostics > Trace > Trace > Trace with Binds and Waits. Trace项代表的意思. 3.关闭Trace,Help > Diagnostics > Trace > Trace > No Trace.

Redo write触发的四种情况

- - CSDN博客推荐文章
1、当LGWR空闲的时候,会每隔3秒检查一次是否有从redo buffer写入redelog中的数据,如果有,一个后台进程就会自动的执行将其写入. 2、当有进程要从redo buffer中分配空间时,会先计算redo buffer中已经占用的空间,如果该空间大于_log_io_size这个参数值,并且此时的LGWR处于空闲状态,便会被激活执行后台写.

日志系统:一条SQL更新语句是如何执行的?

- -
林晓斌 2018-11-16. 讲述:林晓斌 大小:8.66M. 前面我们系统了解了一个查询语句的执行流程,并介绍了执行过程中涉及的处理模块. 相信你还记得,一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎. 那么,一条更新语句的执行流程又是怎样的呢. 之前你可能经常听 DBA 同事说,MySQL 可以恢复到半个月内任意一秒的状态,惊叹的同时,你是不是心中也会不免会好奇,这是怎样做到的呢.

redo log大量生成的诊断处理流程

- - CSDN博客推荐文章
redo log大量生成的诊断处理流程. 本文是原创文章,转载请注明出处:. 1.获得归档日志暴增时段的一个归档日志:可以查询v$archived_log视图,结合completion_time列进行定位. 2.对该归档日志进行转储dump.    --请将路径修改成当时的redo归档的路径.   以上命令会在user_dump_dest中生成一个trace文件,请将该trace文件传到linux中(root用户or oracle用户均可).

PL/SQL动态SQL(原创)

- - ITeye博客
使用动态SQL是在编写PL/SQL过程时经常使用的方法之一. 很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行SQL查询语句,对于这种情况需要使用动态SQL来完成. 再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只能针对某几个特定的表来形成分页.