ORACLE 数据库REDO日志暴涨的处理思路

数据库中可能我们经常会遇到因业务逻辑出现死循环或异常DML导致的日志暴涨问题，这时候如何快速排错，如何定位问题就是一件很棘手的事。下面是我日常工作中的处理套路，来供大家参考。

 

1、先确定日志增长的时间范围

 

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;

2、确定块对象的变化次数

SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME, 

       DHSO.OBJECT_NAME,DHSO.object_type,

       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('2020-05-20 11:00', 

                                       'YYYY-MM-DD HH24:MI') 

                                       AND 

           TO_DATE('2020-05-20 12:00', 'YYYY-MM-DD HH24:MI') 

GROUP  BY TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'), 

          DHSO.OBJECT_NAME,DHSO.object_type

HAVING SUM(DB_BLOCK_CHANGES_DELTA) > 0 

ORDER  BY SUM(DB_BLOCK_CHANGES_DELTA) DESC;

 

3、找到相应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 'MODEDATASHARE_1881%'  --此处用具体的段对象替换

  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('2020-05-20 11:00','YYYY-MM-DD HH24:MI')

  AND TO_DATE('2020-05-20 12:00','YYYY-MM-DD HH24:MI')

 

 

 

SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),

 

       dbms_lob.substr(sql_text, 4000, 1),

 

       dhss.instance_number,

 

       dhss.sql_id,

 

       executions_delta,

 

       rows_processed_delta

 

  FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst

 

 WHERE UPPER(dhst.sql_text) LIKE '%MODEDATASHARE_1881%'

 AND LTRIM(UPPER(DHST.SQL_TEXT)) NOT LIKE 'SELECT%'

 

   AND dhss.snap_id = dhs.snap_id