How to calculate the optimal undo_retention and the initial experience of undo_retention In the Undo Series

A simple definition of undo_retention is the minimum retention time of the most data. In Aum mode, the undo_retention parameter is used for the time when undo data is retained after the transaction commit. The Unit is seconds. This is a limitation of no guarantee. That is, if the space is sufficient, it is just a 'vase '. When the available space is insufficient and a transaction needs to roll back the space, the data will still be overwritten. This behavior may cause a ORA-01555 error, and the time for the data to be remembered can be queried using the field tuned_undoretention in V $ undostat.

In many cases, we want undo data to be retained rather than overwritten. At 10 Gb, oracle adds guarantee control to undo, that is, you can specify that the Undo tablespace must meet the undo_retention limit.

alter tablespace undotbs1 retention guarantee|noguarantee；

By setting the expected retention time and modifying the Undo tablespace attribute, you can run the Undo tablespace in guarantee mode. Next we will use an experiment to test the differences between noguarantee and guarantee:

Sys @ orcl> select tablespace_name, contents, retention from limit where tablespace_name like '% limit %'; tablespace_name contents retention limit --------- ----------- undotbs1 undo limit @ orcl> alter system set limit = 800; system altered. sys @ orcl> alter tablespace undotbs1 retention guarantee; tablespace altered. sys @ orcl> select tablespace_name, contents, retention from dba_tablespaces where tablespace_name like '% undotbs % '; tablespace_name contents retention limit --------- ----------- undotbs1 undo guarantee cancels the automatic expansion attribute of the Undo tablespace by sys @ orcl> select file_name, tablespace_name, Bytes/1024/1024 m from limit where tablespace_name like '% limit % '; file_name tablespace_name M partition ---------------------------- ----------/u01/APP/Oracle/oradata/orcl/datafile/o1_mf_undotbs1_8050fkc6 _. DBF undotbs1 30sys @ orcl> alter database datafile '/u01/APP/Oracle/oradata/orcl/datafile/o1_mf_undotbs1_8050fkc6 _. DBF 'autoextend off; database altered. try to loop through small batches to delete data, and under guarantee settings, a ORA-30036 error will soon be prompted: HR @ orcl> select count (*) from t; count (*) ---------- 1462140hr @ orcl> begin 2 for I in 1 .. 1000 3 Loop 4 Delete from t where rownum <1001; 5 commit; 6 end loop; 7 end; 7/begin * error at line 1: ORA-30036: unable to extend segment by 8 in undo tablespace 'undotbs1 'ora-06512: At line 4hr @ orcl> select count (*) from t; count (*) ---------- 14620000 after modifying the retention attribute of the Undo tablespace, the deletion can be completed successfully by HR @ orcl> begin 2 for I in 1 .. 1000 3 Loop 4 Delete from t where rownum <1001; 5 commit; 6 end loop; 7 end; 8/PL/SQL procedure successfully completed.

The undo_retention settings are responsible for the flash back function. If the column type is lob, the memory of the automatic undo data does not support lobs. You must set the value of undo_retention to retain it as much as possible. Oracle creates a retention table on the Undo segment header to record the submission time of the related undo storage, so as to implement its retention policy.

How to calculate the optimal undo_retention?

Fuzzy computation can be used:

If your transaction isolation level is serializable or read only, you can make the undo_retention slightly longer than the longest running transaction;
If your transaction isolation level is read commited, you can make the undo_retention slightly longer than the longest running statement (DML ).

You can use the formula for exact calculation:

We need to select a representative period of time for testing, or remove multiple segments, and then weighted average

 SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]" 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'  ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]            OPTIMAL UNDO RETENTION [Sec]                      30    900                                                   40421