Oracle归档日志暴增查找定位

1.查找引起日志暴增对象

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.查找引起日志暴增对象的关联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 '%FULLSEARCH_ANALYSISLOG%'
   AND dhss.snap_id = dhs.snap_id
   AND dhss.instance_Number = dhs.instance_number
   AND dhss.sql_id = dhst.sql_id;

3.查找关联SQL的session和module和machine

select * from dba_hist_active_sess_history WHERE sql_id = 'cs3758yhdjxav';