Undo Related Queries

Undo Extents  ==> ACTIVE    UNEXPIRED   EXPIRED 

show parameter undo_rentention

optimal_undo_rentention = undo_size/(db_block_size*undo_blocks_per_sec)

You can calculate the space allocated for undo in your database by issuing the following query

select sum(d.bytes) "undo"
from v$datafile d,
v$tablespace t,
dba_tablespaces s
where s.contents = 'UNDO'
and s.status = 'ONLINE'
and t.name = s.tablespace_name
and d.ts# = t.ts#;

You can calculate the value of UNDO_BLOCKS_PER_SEC with the following query

Select max(undoblks/((end_time-begin_time)*3600*24))
    "undo_block_per_sec"
    from v$undostat;

You can use the following query to do all the calculations for you

select d.undo_size/(1024*1024) "Current UNDO SIZE",
   SUBSTR(e.value,1,25) "UNDO RETENTION",
   (to_number(e.value) * to_number(f.value) *
   g.undo_block_per_sec) / (1024*1024)
  "Necessary UNDO SIZE"
 from (
   select sum(a.bytes) undo_size
     from v$datafile a,
          v$tablespace b,
         dba_tablespaces c
   where c.contents = 'UNDO'
     and c.status = 'ONLINE'
     and b.name = c.tablespace_name
     and a.ts# = b.ts#
  ) d,
 v$parameter e,
  v$parameter f,
  (
  Select max(undoblks/((end_time-begin_time)*3600*24))
    undo_block_per_sec
    from v$undostat
  ) g
where e.name = 'undo_retention'
and f.name = 'db_block_size';

The following query based on the v$undostat view shows how oracle automatically tunes undo retention based on the length of the longest running query (maxquery column) in the current instance workload

select to_char(begin_time,'hh24:mi:ss') BEGIN_TIME,
to_char(end_time,'hh24:mi:ss') END_TIME,
maxquerylen,nospaceerrcnt,tuned_undoretention
from v$undostat;

Use the following query to find out which SQL statement has run for the longest time in your database 

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

Use the following query to find out the most undo used by a session for a currently executing transaction 


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;

You can also issue the following query to find out which session is currently using the most undo in an instance

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

Shows you the user and the SQL statement together with the amount of undo space that's consumed by the SQL Statement

select sql.sql_text sql_text, t.USED_UREC Records,
       t.USED_UBLK Blocks,    
       (t.USED_UBLK*8192/1024) KBytes
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';

If you experiencing excessive undo , then he following query can help

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;