我一般这么弄：
1、查看产生归档的时间
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select lpad(to_char(first_time,'yyyymmdd hh24'),11) "TIME",trunc(sum(blocks*block_size)/1024/1024) "size(M)",count(*) from v$archived_log where first_time>trunc(sysdate-7) group by lpad(to_char(first_time,'yyyymmdd hh24'),11) order by
2、根据以上时间，查看块变更量最大的对象。根据对象和时间去awr报告里找到对应的sql。
alter session set nls_date_format='yyyymmdd hh24:mi';
SELECT
TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME,
DHSO.OBJECT_NAME,
SUM(DB_BLOCK_CHANGES_DELTA) BLOCK_CHANGED
FROM
DBA_HIST_SEG_STAT DHSS,
DBA_HIST_SEG_STAT_OBJ DHSO,
DBA_HIST_SNAPSHOT DHS
WHERE
DHS.SNAP_ID = DHSS.SNAP_ID
AND DHS.INSTANCE_NUMBER = DHSS.INSTANCE_NUMBER
AND DHSS.OBJ# = DHSO.OBJ#
AND DHSS.DATAOBJ# = DHSO.DATAOBJ#
AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2019-10-11 21:00', 'YYYY-MM-DD HH24:MI')
AND TO_DATE('2019-10-12 08:00', 'YYYY-MM-DD HH24:MI')
GROUP BY
TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'),
DHSO.OBJECT_NAME
HAVING
SUM(DB_BLOCK_CHANGES_DELTA) > 0
ORDER BY
SUM(DB_BLOCK_CHANGES_DELTA) DESC;
评论
有用 2
在线查的话，归档-----redo----undo的思路
找生成大量undo的进程
1) 查 v$sess_io.block_changes --发生更改的块select s.sid, s.serial#, s.username, s.program,i.block_changes from v$session s, v$sess_io i where s.sid = i.sid order by 5 desc, 1, 2, 3, 4;
查多次比较差异，较大的增量表示会话产生的redo次数较多。
2) 查v$transaction.used_ublk v$transaction.used_urec ---undo 块和undo记录数select s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec from v$session s, v$transaction t where s.taddr = t.addr order by 5 desc, 6 desc, 1, 2, 3, 4;
查多次比较增量
评论 有用 1
