Oracle 11g Understanding UNDO behaviour

Couple of weeks ago i faced UNDO issue at my database. Now it is always full and system asks to add some space. I have added another 32GB autoextensible file now it is 32+32+32, but in a couple days, situation repeated. Previously i just ajusted RETENTION in order to get rid of "**ORA-01555: SNAPSHOT TOO OLD**" Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 50000 undo_tablespace string UNDO_NEW0 SQL> select RETENTION from dba_tablespaces where TABLESPACE_NAME='UNDO_NEW0'; RETENTION ----------- NOGUARANTEE SQL> select sum(bytes)/1024/1024/1024 "size_in_gb" from dba_data_files where tablespace_name='UNDO_NEW0'; size_in_gb ---------- 95.9999542 SQL> select u.maxquerysqlid, 2 round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) as gb_unexpire 3 from dba_hist_undostat u 4 , v$parameter p 5 where 1 = 1 6 and p.name = 'db_block_size' 7 and u.end_time >= sysdate - 7 8 group by u.maxquerysqlid, p.value 9 having round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) >= 95 10 order by round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) desc; MAXQUERYSQLID GB_UNEXPIRE ------------- ----------- cj2h00pd9h4uf 96 0rc4km05kgzb9 96 SELECT tablespace_name tablespace, 2 status, 3 ROUND (SUM (bytes) / 1024 / 1024 / 1024, 2) sum_in_gb, 4 COUNT (*) counts 5 FROM dba_undo_extents 6 GROUP BY tablespace_name, status 7 ORDER BY 1, 2; TABLESPACE STATUS SUM_IN_GB COUNTS ------------------------------ --------- ---------- ---------- UNDO_NEW0 ACTIVE 0 1 UNDO_NEW0 EXPIRED .01 8 UNDO_NEW0 UNEXPIRED 95.99 25312 SQL> SELECT ses.sid, 2 ses.username, 3 ses.status, 4 ses.program command, 5 round(tra.used_ublk / 8192 / 1024 ,1 ) as MB 6 FROM v$SESSION ses INNER JOIN v$TRANSACTION tra ON ses.saddr = tra.ses_addr; no rows selected So my questions are: 1. What may be the reason that despite I have add 32 GB for UNDO it still asking for more? For last couple of years 64GB was completely enough, now 96GB - almost everyday it asks to expand UNDO. Where i must to dig in order to get the solution? Of course data amount is growing, but linear and slowly. There were not any new features and calculations implemented. 2. How come the internal Oracle`s mechanism got 96GB of undo? I am talking about **"0rc4km05kgzb9" - "select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'"** ? I have seen that Oracle have a doc about it, but i do not have access there. 3. Is it OK that so many SQLs consumes almost full size of UNDO? I understand that decreasing of RETENTION may help here, but cannot figure out why i have such a problems now, after it worked well for a so long time?