oracle删除唯一索引sql语句_Oracle数据库redo日志暴增，怎么查出是哪些sql语句影响导致？...

概述

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方面的内容，感兴趣的朋友可以关注一下~