Tuesday, July 5, 2011

How to find the sessions which generated maximum redo from the last oracle database startup on

How to find the sessions which generated maximum redo from the last oracle database startup on
=================================================================

----see the query

SELECT ss.SID, sq.sql_text, se.status, se.username, se.osuser, se.program,
se.machine, ss.VALUE
FROM v$sesstat ss, v$statname sn, v$session se, v$sqlarea sq
WHERE ss.statistic# = sn.statistic#
AND se.sql_hash_value = sq.hash_value(+)
AND se.sql_address = sq.address(+)
AND ss.SID = se.SID
AND sn.NAME = 'redo size'
ORDER BY ss.VALUE DESC



----for current session how much redo generation

SELECT value
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.name = 'redo size';

No comments: