Sunday, September 12, 2010

How to find out list of top 10 redo generators? (redo usage)

set lines 132 pages 2000

col sid form 9999
col username form a10
col value Head "Redo | Generated in | MB" form 9999999999.99
col program form a20
col logtime head "Logon Time" form a15
col sql_text format a45 wrap

select a.sid, a.username, a.logtime, a.program, a.value, sq.sql_text from (SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi') logtime, nvl(se.program,se.module) program, round((value/1048576),2) VALUE, se.sql_address FROM v$sesstat st, v$statname sn, v$session se WHERE = 'redo size' AND value > 104857600 AND sn.statistic# = st.statistic# AND st.sid = se.sid ORDER BY 5 desc ) a, v$sql sq where a.sql_address=sq.address and rownum < 11 order by 5 desc;

No comments:

Post a Comment