在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,'Hp4'),'00',1,0)),'999') "00",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'01',1,0)),'999') "01",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'02',1,0)),'999') "02",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'03',1,0)),'999') "03",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'04',1,0)),'999') "04",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'05',1,0)),'999') "05",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'06',1,0)),'999') "06",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'07',1,0)),'999') "07",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'08',1,0)),'999') "08",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'09',1,0)),'999') "09",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'10',1,0)),'999') "10",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'11',1,0)),'999') "11",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'12',1,0)),'999') "12",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'13',1,0)),'999') "13",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'14',1,0)),'999') "14",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'15',1,0)),'999') "15",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'16',1,0)),'999') "16",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'17',1,0)),'999') "17",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'18',1,0)),'999') "18",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'19',1,0)),'999') "19",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'20',1,0)),'999') "20",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'21',1,0)),'999') "21",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'22',1,0)),'999') "22",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'Hp4'),'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。

这里分享一个很是不错的阐明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 rshamsud@orainternals.com, 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 Hp4:MI';
set pagesize 10000
with redo_data as (
SELECT instance_number,
       to_date(to_char(redo_date,'DD-MON-YY-Hp4:MI'), 'DD-MON-YY-Hp4: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
/