在ORACLE資料庫的管理、維護過程中，偶爾會遇到歸檔日誌暴增的情況,也就是說一些SQL語句產生了大量的redo log，那麼如何跟蹤、定位哪些SQL語句生成了大量的redo log日誌呢？ 下麵這篇文章結合實際案例和官方文檔“How to identify the causes of High R... ...
在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_NUMFROM 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_HISTORYGROUP 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, LLCREM 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 220alter session set nls_date_format='YYYY-MM-DD HH24:MI';
set pagesize 10000with 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_timeORDER BY begin_interval_time ,sysst.instance_number
) redo_size
FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snapsWHERE sysst.stat_id =( SELECT stat_id FROM sys.wrh$_stat_name
