A Shortcoming Of Auto Tuned Undo Retention

Symptom:

    When Apps / DB team are doing data migration , there are big batches with insert sql and  the migration tool failed with error:

  ORA-01628: max # extents (32765) reached for rollback segment    XXXXXXXX

Diagnosis:

       The error informs that there were high volume of data undo segments and it reached the limit 32k.
     Use sql to check status of undo segments:

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM 
DBA_UNDO_EXTENTS GROUP BY STATUS; 

STATUS                      SUM(BYTES)/1024/1024   COUNT(*) 
--------------------------- -------------------- ---------- 
UNEXPIRED                             162676.563      55123 
EXPIRED                               12121.5625      12319 

So majority of the space is used by UNEXPIRED extents 

So something is wrong here, undo extents should not have majority as UNEXPIRED unless there are huge queries running right now.
   From AWR report, we can find the longest query is about 7654s. As long as undo tablespace has enough space for this longest query, the undo extents it uses should expire soon after the query is finished.

From 10g , oracle DB added auto tuned undo which is based on statistic data. Default it is TRUE
Use sql to check this hidden parameter

SELECT 
a.ksppinm Param , b.ksppstvl SessionVal ,c.ksppstvl InstanceVal,a.ksppdesc Descr 
FROM  x$ksppi a , x$ksppcv b , x$ksppsv c WHERE 
a.indx = b.indx AND 
a.indx = c.indx AND 
a.ksppinm LIKE '/_%undo_autotune%' escape '/'
ORDER BY 1;

The extents are not expired as we expect due to another hidden parameter _highthreshold_undoretention.  It is used to control auto tuned undo behaviour.
By default it is set 4294967294 (50 days) . It normally works if no big migration.
However giving big migration project,  it turns out to keep large volume of undo extents unexpired with this big value.  So the solution is to make it small 86400s (1 day) and they will expire in 1 day.

Solution:

 
ALTER SYSTEM SET "_highthreshold_undoretention"=86400 scope=both and sid='*';