前言
前一段時間巡檢發(fā)現一套數據庫歸檔量近幾日發(fā)生了爆增，雖然最終定位并非數據庫自身原因，這里也總結下分析步驟和思路。
分析步驟
1、確認日志切換頻率，明顯30號和1號日志切換比之前增長一倍。
select to_char(first_time,'YYYY-MON-DD') "Date", to_char(first_time,'DY') 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" , count(*) Total from v$log_history group by to_char(first_time,'YYYY-MON-DD'), to_char(first_time,'DY') order by to_date(to_char(first_time,'YYYY-MON-DD'),'YYYY-MON-DD');
Date DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 TOTAL
-------------- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
2022-NOV-27 SUN 71 33 52 36 53 51 68 45 56 58 51 56 74 56 49 63 51 50 78 54 54 47 33 59 1298
2022-NOV-28 MON 59 32 54 44 52 39 65 113 117 96 65 148 110 79 62 67 97 56 84 47 55 58 76 56 1731
2022-NOV-29 TUE 60 32 54 36 60 42 70 47 60 59 44 56 63 51 49 51 48 51 83 112 122 138 136 164 1688
2022-NOV-30 WED 124 83 97 102 121 116 142 84 119 126 100 110 142 87 116 124 97 137 165 102 117 126 68 137 2742
2022-DEC-01 THU 133 82 90 106 114 124 150 123 95 131 102 114 130 104 118 181 69 0 0 0 0 0 0 0 1966
2、查看每日的日志量，日志切換頻繁不見得日志量就大，還是要確認下日志量的情況。從查詢結果看，最近兩天單日歸檔量快到4T了。日常也就2T左右。
select dest_id,to_char(first_time,'yyyy-mm-dd') TIME,count(*),sum(blocks*block_size)/1024/1024/1024 GB
from v$archived_log =================》即使是rac也無需使用gv$視圖。
where dest_id=1
and
first_time>to_date('20221126 00:00','yyyymmdd hh24:mi')
and
first_time<to_date('20221201 18:59','yyyymmdd hh24:mi')
group by dest_id,to_char(first_time,'yyyy-mm-dd')
order by 1,2; DEST_ID TIME COUNT(*) GB
---------- ---------- ---------- ----------
1 2022-11-26 1308 1949.53655
1 2022-11-27 1298 1890.3195
1 2022-11-28 1731 2526.82131
1 2022-11-29 1688 2589.85447
1 2022-11-30 2742 3995.80157
1 2022-12-01 2405 3509.18593、確認下活動會話是否有增長，如果活動會話有增長的話，可能是由于交易量上升導致的歸檔量變大。根據查詢結果，除去兩個異常時段有會話數激增，其他時段并沒有發(fā)現活躍會話有明顯的增長。
11-29號：
select to_char(sample_time,'yyyy/mm/dd hh24'),count(*) from dba_hist_active_sess_history where
sample_time>to_date('20221129 06:00','yyyymmdd hh24:mi')
and sample_time<to_date('20221129 12:00','yyyymmdd hh24:mi')
group by to_char(sample_time,'yyyy/mm/dd hh24') order by 1;
TO_CHAR(SAMPL COUNT(*)
------------- ----------
2022/11/29 06 4869
2022/11/29 07 4518
2022/11/29 08 8142
2022/11/29 09 108017 ============》當天出現數據庫異常，導致會話堵塞。
2022/11/29 10 85392
2022/11/29 11 3078
11-30號：
select to_char(sample_time,'yyyy/mm/dd hh24'),count(*) from dba_hist_active_sess_history where
sample_time>to_date('20221130 06:00','yyyymmdd hh24:mi')
and sample_time<to_date('20221130 12:00','yyyymmdd hh24:mi')
group by to_char(sample_time,'yyyy/mm/dd hh24') order by 1;
TO_CHAR(SAMPL COUNT(*)
------------- ----------
2022/11/30 06 4392
2022/11/30 07 10890 ==================》當天由于歸檔目錄滿了，導致會話堵塞。
2022/11/30 08 4963
2022/11/30 09 4722
2022/11/30 10 3549
2022/11/30 11 4070
12-1號
select to_char(sample_time,'yyyy/mm/dd hh24'),count(*) from dba_hist_active_sess_history where
sample_time>to_date('20221201 06:00','yyyymmdd hh24:mi')
and sample_time<to_date('20221201 12:00','yyyymmdd hh24:mi')
group by to_char(sample_time,'yyyy/mm/dd hh24') order by 1;
TO_CHAR(SAMPL COUNT(*)
------------- ----------
2022/12/01 06 4481
2022/12/01 07 4146
2022/12/01 08 5200
2022/12/01 09 4671
2022/12/01 10 4283
2022/12/01 11 3394 4、參考mos文檔How to identify the causes of High Redo Generation (Doc ID 2265722.1)，選取日志生成量比較大的06-07點，查看block changed的對象情況。
對比了三天的block changed，發(fā)現12月1號和11月30號的TOP4都是一樣的，而在11月29號中并沒有這幾個對象，這幾個對象從名字看應該是一個表和三個索引。12月1號早晨6點-7點：SELECT to_char(begin_interval_time,'YY-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('22-12-01 06:00','YY-MM-DD HH24:MI')
AND to_date('22-12-01 07:00','YY-MM-DD HH24:MI') GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'), dhso.object_name HAVING sum(db_block_changes_delta) > 0 ORDER BY sum(db_block_changes_delta) desc ;
SNAP_TIME OBJECT_NAME BLOCK_CHANGED
----------- ------------------------------ -------------
22-12-01 06 IX_GTMP_WIND_RECORD_LOG 106597968
22-12-01 06 IX2_GTMP_WIND_RECORD_LOG 68668736
22-12-01 06 IX1_GTMP_WIND_RECORD_LOG 48441232
22-12-01 06 GTMP_WIND_RECORD_LOG 46247936
22-12-01 06 GZ45_VN_BM_BAL 21052960
22-12-01 06 ** TRANSIENT: 23179648 18150128
22-12-01 06 PK_O32_TTRADESTOCK 16278256
22-12-01 06 PK_GZ45_VN_DAYF_CONFIRM 7566992
11月30號早晨6點到7點：SELECT to_char(begin_interval_time,'YY-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('22-11-30 06:00','YY-MM-DD HH24:MI')
AND to_date('22-11-30 07:00','YY-MM-DD HH24:MI') GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'), dhso.object_name HAVING sum(db_block_changes_delta) > 0 ORDER BY sum(db_block_changes_delta) desc ;
SNAP_TIME OBJECT_NAME BLOCK_CHANGED
----------- ------------------------------ -------------
22-11-30 06 IX_GTMP_WIND_RECORD_LOG 59098880
22-11-30 06 IX2_GTMP_WIND_RECORD_LOG 44366832
22-11-30 06 IX1_GTMP_WIND_RECORD_LOG 39393808
22-11-30 06 GTMP_WIND_RECORD_LOG 24130704
22-11-30 06 GZ45_VN_BM_BAL 21051680
22-11-30 06 PK_O32_TTRADESTOCK 16285792
22-11-30 06 GZ45_T_R_FR_ASTSTAT 6459584
22-11-30 06 IX_GZ45_VN_BM_BAL 5837776
11月29號早晨6點-7點：SELECT to_char(begin_interval_time,'YY-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('22-11-29 06:00','YY-MM-DD HH24:MI')
AND to_date('22-11-29 07:00','YY-MM-DD HH24:MI') GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'), dhso.object_name HAVING sum(db_block_changes_delta) > 0 ORDER BY sum(db_block_changes_delta) desc ;
SNAP_TIME OBJECT_NAME BLOCK_CHANGED
----------- ------------------------------ -------------
22-11-29 06 GZ45_VN_BM_BAL 21660112
22-11-29 06 PK_O32_TTRADESTOCK 16292176
22-11-29 06 IDX_ODS_WIND_CBONDAGENCY 11994048
22-11-29 06 PK_ODS_WIND_CBONDAGENCY 9990224
22-11-29 06 O32_TTRADESTOCK 9007728
22-11-29 06 ODS_WIND_CBONDAGENCY 7276320
22-11-29 06 GZ45_T_R_FR_ASTSTAT 6671232
22-11-29 06 IX_GZ45_VN_BM_BAL 5930496
5、根據以上對象可以找到執(zhí)行的相應的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 '%GTMP_WIND_RECORD_LOG%'
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('22-12-01 06:00','YY-MM-DD HH24:MI')
AND to_date('22-12-01 07:00','YY-MM-DD HH24:MI')；
INST_ID SQL_ID EXEC_DELTA ROWS_PROC_DELTA
---------- ------------- ---------- ---------------
2022_12_01 06
INSERT INTO ETL.GTMP_WIND_RECORD_LOG (ID, REC_ID, OPMODE, OPDATE, TABLE_NAME) SE
LECT ID, REC_ID, OPMODE, OPDATE, TABLENAME FROM WINDDF.RECORD_LOG@WINDNEWDB A WH
ERE A.TABLENAME=:B1
2 61z1sgnsccx6m 184 96347952
INST_ID SQL_ID EXEC_DELTA ROWS_PROC_DELTA
---------- ------------- ---------- ---------------
DELETE FROM ETL.GTMP_WIND_RECORD_LOG A WHERE A.ID>:B2 OR A.ID<:B1
2 d7k797d3827cg 221 966106166、其實上面的結論從awr報告中也能的出來，但是awr報告只能顯示top5，沒辦法看到后面的。當我想從awr報告里證明上述結論的時候，我發(fā)現這個處于TOP的對象竟然是TEMP表。按我以前的理解，對臨時表的操作是不會產生redo的。從度娘上查了下，臨時表本身的操作雖然不會產生redo，但是對臨時表操作需要產生undo，產生undo自然會產生redo，只是比普通表產生的redo要少很多。
總結
日常碰到的日志量突增大部分都是交易量上漲、業(yè)務邏輯調整等導致的，但是如果你直接去問開發(fā)人員，他可能會說啥也沒變，這個時候你就會挺懵逼（這是本人親身經歷哈）。如果你拿著你查到的東西去找他，他可能就不會這么硬氣了。所以啊，雖然可能不是數據庫的問題，但作為萬能的DBA，你也的會查啊。
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創(chuàng)內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發(fā)現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發(fā)送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
