Finding what's consuming the most UNDO

posted Apr 24, 2012, 8:54 AM by Sachchida Ojha   [ updated Apr 24, 2012, 8:55 AM ]
Very often DBA's see that one or more session seem to be hogging the UNDO tablespace.  You need to find out what user and which SQL statement is eating up all the UNDO space.

SQL> select s.sql_text from v$sql s, v$undostat u
where u.maxqueryid=s.sql_id;

You can also use following SQL to find out most undo used by a session for a currently executing transaction.

SQL> select s.sid,s.username,t.used_urec,t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
order by t.used_ublk desc;

To find out which session is currently using the most UNDO,

SQL>select s.sid,, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and'undo change vector size'
order by s.value desc;

SQL>select sql.sql_text, t.used_urec records, t.used_ublk blocks,
(t.used_ublk*8192/1024) kb from v$transaction t,
v$session s, v$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';