How to Find Sessions Generating Lots of Redo or Archive logs
假如需要我们排查哪个会话产生大量redo的时候，通常有以下两种做法。由于一个事物产生undo的时候同时也产生redo因此该种做法也适合排查undo的使用情况。
The methods are:
1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session.
High values indicate a session generating lots of redo.
SQL> SELECT s.sid, s.serial#,
s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sidORDER BY 5 desc, 1, 2, 3, 4;
注：该种方法需要运行多次，通过对block_changes的值进行前后对比，来判断哪个会话产生大量的redo.
2)Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).
SQL> 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;
注：通过多次运行该语句，观察每次执行时的used_ublk,used_urec的详细情况，可以从中发现哪个session产生大量的redo和undo.
当你第一次分析一个程序（多个事物）占用多大的redo或undo时，这样的情况第二种方式较适合。
The methods are:
1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session.
High values indicate a session generating lots of redo.
SQL> SELECT s.sid, s.serial#,
s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sidORDER BY 5 desc, 1, 2, 3, 4;
注：该种方法需要运行多次，通过对block_changes的值进行前后对比，来判断哪个会话产生大量的redo.
2)Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).
SQL> 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;
注：通过多次运行该语句，观察每次执行时的used_ublk,used_urec的详细情况，可以从中发现哪个session产生大量的redo和undo.
当你第一次分析一个程序（多个事物）占用多大的redo或undo时，这样的情况第二种方式较适合。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29446986/viewspace-1329618/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
