============= oracle資料庫archivelog暴增分析 ====================
前言
歸檔量突然增長到981G/天，導致歸檔目錄使用率告警
歸檔日誌量異常暴增會導致磁碟空間爆滿，資料庫異常
1、歸檔日誌量統計
SELECT TRUNC(FIRST_TIME) "TIME",
SUM(BLOCK_SIZE * BLOCKS) / 1024 / 1024 / 1024 "SIZE(GB)"
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(FIRST_TIME) order by TRUNC(FIRST_TIME);
部分輸出：
TIME SIZE(GB)
--------- ----------
09-JUN-21 63.345335
10-JUN-21 50.041029
11-JUN-21 43.3773184
12-JUN-21 41.8273354
13-JUN-21 36.834712
14-JUN-21 38.4342957
15-JUN-21 42.0742655
16-JUN-21 40.6740589
17-JUN-21 44.1439724
18-JUN-21 53.9158897
19-JUN-21 981.466756
2、日誌生成量最大的物件 (按小時統計)
select * from (
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time> sysdate - 120/1440
GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dhsso.object_name
order by 3 desc)
where rownum<=5;
SNAP_TIME OBJECT_NAME SUM(DB_BLOCK_CHANGES_DELTA)
---------------- ------------------------------ ---------------------------
2021_06_19 09:00 OMS_GOODSQTY_UPLOAD_GDST_IDX 1506104016
2021_06_19 09:00 OMS_GOODSQTY_UPLOAD_IDX 175114000
2021_06_19 09:00 OMS_GOODSQTY_UPLOAD 148411280
2021_06_19 09:00 BMS_ST_IO_DTLTMP_INOUTID_IDX 201616
2021_06_19 09:00 GRESA_SA_DTL_RSAID_IDX 138160
tips：也可以查awr的Segments by Physical Writes的資料，資料相對應
3、相關sql查詢
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 '%OMS_GOODSQTY_UPLOAD%'
AND dhss.snap_id = dhs.snap_id
AND dhss.instance_Number = dhs.instance_number
AND dhss.sql_id = dhst.sql_id;
當前sql查詢：
select a.username,a.sid,b.sql_text from v$session a,v$sql b
where a.sql_id=b.sql_id and b.sql_text like '%OMS_GOODSQTY_UPLOAD%';
4、查詢關聯SQL的session和module和machine
select username,program,module,machine from dba_hist_active_sess_history WHERE sql_id = 'cs3758yhdjxav';
處理方式及總結
排查出歸檔生成量最大的物件相關資訊(使用者名稱、物件名、程式名、模組、機器）反饋應用測排查相關業務，
緊急處理方式先停止相關異常業務，再進行業務側優化。
新的業務上線前需要經過嚴格的測試，新業務上線前需要提前溝通，實時觀察資料庫情況，以便出現問題及時發現和處理；
資料庫維護期間要保持高度警惕，認真觀察，冷靜分析。
<end>