Oracle hidden parameters: _ undo_autotune, a thankless Activity

Although talking about hidden parameters must be meaningless, but those hidden parameters are valuable, we have to "respect" those hidden parameters"


In Aum, why does my undo tablespace only grow and does not recycle it? Now it's more than 40 GB and it's still long. Why?
Why is undo insufficient when the system is not very busy? If it is not enough, the problem should be more serious during peak hours?
......
Sometimes the DBA will hear some sounds like above

Because there is a parameter named _ undo_autotune = true, undo_retention is no longer applicable

Oracle will decide on its own tuned_undo_retention, and then tries to increase the Undo tablespace

From Version 10.2, Oracle automatically adjusts undo retention by default.

The undo_retention parameter is automatically adjusted based on the size of your undo tablespace and the system's busy degree (Information in V $ undostat ).

Therefore, in a 10 Gb database, you will often find that the Undo tablespace is always full.

Because when undo tablespace has free space, the system automatically increases undo_retention to keep more undo Blocks

This method is helpful for long queries, but not suitable for typical OLTP systems.

Because OLTP is unlikely to run such a long query, and the OLTP is very busy, it will cause the above problems.

Oracle is really thankless

sys@ORCL> edWrote file afiedt.buf  1  select a.ksppinm name,b.ksppstvl value,a.ksppdesc description  2    from x$ksppi a,x$ksppcv b  3   where a.indx = b.indx  4*        and a.ksppinm like '%_undo_autotune%'  sys@ORCL> /NAME                 VALUE    DESCRIPTION-------------------- -------- ----------------------------------------_undo_autotune       TRUE     enable auto tuning of undo_retention

This parameter can be modified online:

  alter system set “_undo_autotune” = false;  

Note:
If Undo is not recycled, resize may report an error. I have encountered it many times. Generally, it is used to re-create an undo tablespace, switch to the past, and kill the previous tablespace. In this way, it is recycled. space