User Based Redo Generation Stats

Hi,

V$SESSTAT is a powerful view that has more than 600 counters for every sessions. Class 2 counters are about Redo in this view and you can query which session is writing huge Redo.

There is a query which shows the Number of times a redo entry is copied into the redo log buffer.

select sum(s.value) NumberOfTimesCoppiedLogBuffer,
sn.username,
sq.sql_id
from v$sesstat s,
v$session sn,
v$statname n,
v$sql sq
where     n.statistic# = s.statistic#
and sn.sid = s.sid
and n.name like '%redo entries%'
and sn.type = 'USER'
and sn.sql_id = sq.sql_id
and sq.parsing_schema_name = sn.username
group by sq.sql_id,sn.username
order by NumberOfTimesCoppiedLogBuffer desc;

If you want to use these counters in your performance analysis maybe you want to have a look

Statistics Descriptions.

Have a nice day.

 

Advertisements