查找引起redo日志暴增的SQL语句--脚本

此脚本转自梁敬彬老师的《收获，不止SQL优化》，感谢老师的分享

redo日志暴增会间接导致归档日志数量增加，严重的会导致撑爆归档日志的存储空间，导致无法连接数据库。
查询最近几天，每小时归档日志产生数量--脚本：
http://blog.csdn.net/u010692693/article/details/75309167

1. 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;SNAP_TIME        OBJECT_NAME                    SUM(DB_BLOCK_CHANGES_DELTA)---------------- ------------------------------ ---------------------------2017_07_19 01:00 TEST_REDO                                           2222082017_07_19 01:00 WRH$_SYSMETRIC_HISTORY_INDEX                           1122017_07_19 01:00 WRH$_PARAMETER_PK                                      1122017_07_19 01:00 WRM$_SNAPSHOT_DETAILS                                   802017_07_19 01:00 WRI$_ADV_PARAMETERS_PK                                  48

这是查询最近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 '%TEST_REDO%'   AND dhss.snap_id = dhs.snap_id   AND dhss.instance_Number = dhs.instance_number   AND dhss.sql_id = dhst.sql_id;TO_CHAR(BEGIN_INTERVAL_TIME,'Y DBMS_LOB.SUBSTR(SQL_TEXT,4000,                                                   INSTANCE_NUMBER SQL_ID        EXECUTIONS_DELTA ROWS_PROCESSED_DELTA------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ---------------- --------------------2017_07_19 01:00               insert into  test_redo select * from test_redo                                                 1 g893rmm0rmjra                5              26858402017_07_19 01:00               insert into  test_redo select * from test_redo where rownum<=10000                             1 b7z56jkxs5jam               14               1400002017_07_19 01:00               SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB               1 7q4g3xymr4yhz                3                    3

LIKE '%TEST_REDO%'中TEST_REDO是步骤1中查询出来的OBJECT_NAME，可以替换您查询的结果

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

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

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

4. dba_soure 看看是否有存储过程包含这个SQL。