"_undo_autotune" parameter with undo_management and undo_retention

Oracle does have multiple parameters which govern how undo management happens in database. Each parameter applies in different stages, below we will discuss on each of these.

What is undo_management parameter?

Undo_managments is the parameter which defines how the undo extents are managed in the database. This can be either AUTO (which is the default setting 11g onwards) or manual. When set to auto Oracle does allocate, retain and free up undo extents, which is the recommended method.

Undo extents that are managed by Oracle can be in one of below states,

• ACTIVE – Undo extents of uncommitted active queries.
• UNEXPIRED - Undo extents of completed queries, but still within the undo_retention value defined.
• EXPIERED - Undo extents of completed queries, but still within the undo_retention time period.

What is _undo_autotune parameter?

Simply put _undo_autotune , is  a parameter that will try to override the undo_retention parameter. When _undo_autotune is set to True (default value), based on the size of the undo tablespace Oracle will try to keep the undo segments to higher time than defined in undo_retention parameter. Time Oracle keeps each of the undo extent can be queried through tuned_undoretention column of v$undostat view. Example below,

 select begin_time, end_time, maxqueryid, tuned_undoretention from v$undostat order by begin_time desc;  

Changing _undo_autotune should be only done under the advice of Oracle support.