ORACLE定位日志生成量sql,Oracle归档日志暴增查找定位

Oracle归档日志暴增查找定位

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,</