通过awr里面的segments by db dblocks changge里面定位的objects对象去找到的sql都是selete语句，一般产生redo应该都是dml语句才对吧。。
--1、redo、archivelog大量产生必然是由于大量产生"块改变"。从awr视图中找出"块改变"最多的segments
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 - 60/1440
GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dhsso.object_name
order by 3 desc)
where rownum<=5;
--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 '%这里写对象名大写%'
AND dhss.snap_id = dhs.snap_id
AND dhss.instance_Number = dhs.instance_number
AND dhss.sql_id = dhst.sql_id;
评论
有用 0如下SQL也可以参考：
select *
from (
select timestamp, table_name,inserts,updates,deletes
from dba_tab_modifications
where timestamp>sysdate-1 and (inserts+updates+deletes)>0
order by (inserts+updates+deletes) desc
)
where rownum<=10
/
评论 有用 0
