Sunday, 30 December 2012

A query to show you current sessions generating redo


A query to show you current sessions generating redo:-

select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s
  join v$statname n on n.statistic# = s.statistic#
  join v$session sn on sn.sid = s.sid
where name like '%redo entries%'
order by value desc;


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.sid
     ORDER BY 5 desc, 1, 2, 3, 4;


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;

1 comment:

  1. Hi Shiven,

    Do you have any query which can give which were the sessions which generated more redo yesterday.

    Regards,
    Visith Kumar

    ReplyDelete