Who is using your UNDO space?

Sure you've faced this situation: a growing undo tablespace, that seems it could engulf your entire disk space... until finally stops demanding additional space, and within some minutes (or hours, depends on your UNDO_RETENTION setting) you start to see more and more free space in your tablespace. If you scratched your head wondering 'what happened?' or 'who the User did this?', this post may be helpful.

There are some views that show information related to undo activity:

* V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
* V$TRANSACTION: present time view providing information on current transactions.
* V$SESSTAT: individual session statistics, which includes one for undo usage.

V$UNDOSTAT will provide a who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

V$SESSTAT provides another view, a who uses the undo kind of view, but we must avoid to get lost in the maze of Oracle statistics and focus on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176    <-- Which stands for undo change vector size
ORDER BY a.value DESC 

Good luck with your UNDO-eating monsters...


Who is using your UNDO space? Improved Script"

I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks

and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.

Then the script for Oracle 11g is as follows:

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value > 0
ORDER BY a.value DESC

If you want to run this script on versions 10g1 and 10g2, just replace the statistic# with 176; 216 if your database is 11gR1... or use the following version independent script!!! (Hope we don't change the statistic name).

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND b.name = 'undo change vector size'
AND a.value > 0
ORDER BY a.value DESC

http://oracledisect.blogspot.com/2008/05/who-is-using-your-undo-space.html