概述

Oracle数据库中如果redo日志暴增会间接导致归档日志数量增加，严重的会导致撑爆归档日志的存储空间，导致无法连接数据库。

前面已经介绍了怎么去查询最近几天，每小时归档日志产生数量的脚本，这里就不多做介绍了。下面介绍一下怎么去查找引起redo日志暴增的相关sql语句。

1、查看最近2小时"块改变"最多的segment

redo大量产生必然是由于大量产生"块改变"。从awr视图中找到"块改变"最多的segment。

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;

这是查询最近2小时(120分钟)的，begin_interval_time> sysdate - 120/1440，大家也可以自定义修改查询最近多少分钟的。

2、从awr视图中找出步骤1中排序靠前的对象涉及的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 '%MON_MODS$%'
 AND dhss.snap_id = dhs.snap_id
 AND dhss.instance_Number = dhs.instance_number
 AND dhss.sql_id = dhst.sql_id;

说明：LIKE '%MON_MODS$%'中MON_MODS是步骤1中查询出来的OBJECT_NAME

3、从ASH相关视图找到执行这些SQL的session、module和machine

select * from dba_hist_active_sess_history WHERE sql_id = '350f5yrnnmshs';
select * from v$active_session_history where sql_Id = '350f5yrnnmshs';

350f5yrnnmshs是SQL_ID,替换第二步查询的结果SQL_ID列

4、dba_source 看看是否有存储过程包含这个SQL

篇幅有限，这一块就介绍到这了，后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~