原文出处:
潇湘隐者
在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" FRO